VBA Function producing #Value! return in Excel for Mac

Question

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 String | Duplicate Characters Removed
route   | route
trout   | trou
eater   | eatr
brass   | bras
seige   | seig
smelt   | smelt

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.

MAC does not have `Scripting.Dictionary`. There is a substitute on GitHub (Tim Hall, I think is the author). See also [VBA (Excel) Dictionary on Mac?](https://stackoverflow.com/questions/19869266/vba-excel-dictionary-on-mac) — Ron Rosenfeld, Jan 08 '23 at 12:00
Does your version of Excel have Power Query? If so, you can add a custom column with a single line of code, such as: `=Text.Combine(List.Distinct(Text.ToList([Original])),"")`. — Ron Rosenfeld, Jan 08 '23 at 13:54

FunThomas · Accepted Answer · 2023-01-08T13:33:52.853

As Ron wrote in the comments, there is no Scripting.Dictionary on MAC - the Dictionary is part of the Microsoft Scripting Library (scrrun.dll) and dll's (dynamic link library) doesn't exist on a Mac.

Now using a Dictionary for duplicate checking is a good idea in general because it's very fast and easy to use, however, when it comes to simple checking if a string contains a character, it's a little overkill.

The following function will copy every character of the source string into the destination string if it is not already in - the check is done by using InStr.

I have changed the parameter type to Variant and convert the content into a string using the function CStr - with that you can call the function with nearly every data type (Range, String, even Numbers or Dates).

Function RemoveDuplicateChars(param As Variant) As String
    Dim sourceString As String, i As Long
    sourceString = CStr(param)
    For i = 1 To Len(sourceString)
        Dim xChar As String
        xChar = Mid(sourceString, i, 1)
        If InStr(RemoveDuplicateChars, xChar) = 0 Then
            RemoveDuplicateChars = RemoveDuplicateChars & xChar
        End If
    Next
End Function

Update: Just for completeness, I added an optional 2nd parameter so that the function can be used case insensitiv (If the string contains an uppercase and a lowercase character, only one of them is copied)

Function RemoveDuplicateChars(param As Variant, Optional IgnoreCase As Boolean = False) As String
    Dim sourceString As String, i As Long
    sourceString = CStr(param)
    For i = 1 To Len(sourceString)
        Dim xChar As String
        xChar = Mid(sourceString, i, 1)
        Dim compareMethod As Long
        compareMethod = IIf(IgnoreCase, vbTextCompare, vbBinaryCompare)
        If InStr(1, RemoveDuplicateChars, xChar, compareMethod) = 0 Then
            RemoveDuplicateChars = RemoveDuplicateChars & xChar
        End If
    Next
End Function

I had suspicion that it might be a Mac issue but didn't know specifically where to look. Thx for the help. This works perfectly. — cmc, Jan 08 '23 at 20:56

VBA Function producing #Value! return in Excel for Mac

1 Answers1