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...

Excel on Mac

Macros and VBA

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

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

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.

Forum Discussion

VBA problem - Excel for Mac

Resources