Forum Discussion

cmc13's avatar
cmc13
Copper Contributor
Jan 08, 2023
Solved

VBA problem - Excel for Mac

I am having problems running a VBA function on my Excel for Mac.

 

I want to process a series of strings to remove any duplicate characters in the strings.  For example: column 1 shows the original strings while column 2 has removed any duplicate characters.

Original StringDuplicate Characters Removed
routeroute
trouttrou
eatereatr
brassbras
seigeseig
smeltsmelt

 

i found some VBA code which purports to do this however it returns #VALUE! when I run it.  Code is shown below:

 

 

 

Function RemoveDupes1(pWorkRng As Range) As String
'Updateby Extendoffice
Dim xValue As String
Dim xChar As String
Dim xOutValue As String
Set xDic = CreateObject("Scripting.Dictionary")
xValue = pWorkRng.Value
For i = 1 To VBA.Len(xValue)
    xChar = VBA.Mid(xValue, i, 1)
    If xDic.Exists(xChar) Then
    Else
        xDic(xChar) = ""
        xOutValue = xOutValue & xChar
    End If
Next
RemoveDupes1 = xOutValue
End Function

 

 

 

I call this function by entering =Removedupes1(A2) in cell B2 (where A2 holds the first string in my list), however I receive #VALUE! error.

 

I dont know if the problem is in the VBA code (others seem to have succesfully used it, but perhaps not on a Mac) or the way I am applying it (I dont really know VBA but have succesfully applied other snippets in the past).  Any advice gratefully received.  TIA.

Excel on Mac
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Jan 08, 2023

    cmc13 

    Scripting.Dictionary is for Windows only, it doesn't exist on a Mac. Here is a version that should work on a Mac:

    Function RemoveDupes1(pWorkRng As Range) As String
    'Update by Microsoft Tech Community
    Dim xValue As String
    Dim xChar As String
    Dim xOutValue As String
    Dim xCol As New Collection
    Dim i As Long
    xValue = pWorkRng.Value
    For i = 1 To Len(xValue)
        xChar = Mid(xValue, i, 1)
        On Error Resume Next
        xCol.Add Item:=xChar, Key:=xChar
    Next i
    For i = 1 To xCol.Count
        xOutValue = xOutValue & xCol(i)
    Next i
    RemoveDupes1 = xOutValue
End Function

2 Replies

Sort By
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jan 08, 2023

    cmc13 

    Scripting.Dictionary is for Windows only, it doesn't exist on a Mac. Here is a version that should work on a Mac:

    Function RemoveDupes1(pWorkRng As Range) As String
    'Update by Microsoft Tech Community
    Dim xValue As String
    Dim xChar As String
    Dim xOutValue As String
    Dim xCol As New Collection
    Dim i As Long
    xValue = pWorkRng.Value
    For i = 1 To Len(xValue)
        xChar = Mid(xValue, i, 1)
        On Error Resume Next
        xCol.Add Item:=xChar, Key:=xChar
    Next i
    For i = 1 To xCol.Count
        xOutValue = xOutValue & xCol(i)
    Next i
    RemoveDupes1 = xOutValue
End Function
    • cmc13's avatar
      cmc13
      Copper Contributor
      Jan 08, 2023

      HansVogelaar   Yep. This has solved.  I had suspicion that the issue was Mac specific but didnt know where to search.  Thx for this.

Resources