VBA Function producing #Value! return in Excel for Mac-CodePudding

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.

CodePudding user response：

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