UDF - Custom Split and Remove

[Marmit424]

Hey there,

I've created a custom function which compares two text strings, and removes any duplicates. For instance
String 1 = "Bob; Sally; Tom; Beth;"
String 2 = "Sally; Beth;"
Resulting String = "Bob; Tom;"

I have two issues. One is that my function is being recalculated needlessly. For instance, if I create a new worksheet and enter information into a cell it hangs as this code is run.
The second issue is that as I reference this formula over 6,000 times, doing something simple like removing a column can take quite some time. Coupled with the above problem that doesn't bode well.

Could you please help me troubleshoot the auto-recalculation and/or increase the codes efficiency? This is my first UDF. Thanks!

Code below -

Function Custom_SplitandRemove(InitialRange, RemoveMatchesFromThisRange)


n = 0
Dim NewArray() As Variant
ReDim NewArray(0 To 5000) ;The 5000 is arbitrary, could be lowered. Most strings have <10 items.

'Splits the values by delimiter
SplitValuesInitialRange = Split(InitialRange, ";")
SplitValuesSecondaryRange = Split(RemoveMatchesFromThisRange, ";")

'For each initial value, checks for a match from secondary range. If there is no match, adds it to an array.
For Each InitialValue In SplitValuesInitialRange
    If WorksheetFunction.Trim(InitialValue) <> "" Then
        For i = 0 To UBound(SplitValuesSecondaryRange)
            If WorksheetFunction.Trim(InitialValue) = WorksheetFunction.Trim(SplitValuesSecondaryRange(i)) Then
                TheresAMatch = True
            End If
        Next i
        If TheresAMatch = False Then
            NewArray(n) = WorksheetFunction.Trim(InitialValue) & "; "
            n = n + 1
        End If
        TheresAMatch = Empty
    End If
Next InitialValue
ReDim Preserve NewArray(0 To n - 1)


'Puts array into string so I can put the result in a cell.
For b = 0 To n - 1
NewString = NewArray(b) & NewString
Next b


Custom_SplitandRemove = NewString


End Function

 

[Marmit424]

It appears that the code running when it isn't supposed to is a bug. If I reduce the number of instances of the equation it stops malfunctioning in that manner.

 

[Marmit424]

From what I can tell there are a few areas where my code fails.

First, it lacks an error handler. Apparently it's good practice for custom functions to have error handlers. If they don't have a handler, Excel responds by re-running the code again at a later time.

Secondly, on some cells where I'm running this code the first string is blank. In this instance, the code is returning 0. I don't think this would be a problem, except I have nested this code with itself in a few areas, and upon taking 0 into a string it throws an error. I'm working on a fix for that.