UDF - Custom Split and Remove

Marmit424

Board Regular
Joined
Jul 12, 2016
Messages
58
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 -
Code:
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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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.
 
Last edited:
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top