My last post looked at creating a simple user defined function (UDF). This post looks at a little more advanced subject of combining multiple functions into one multi function UDF.
Sometimes you’ll write code to do similar jobs. Maybe one function to remove alpha characters (letters) from a string, and another to remove non-numeric characters.
Function RemoveAlphas(CharString As Variant) As Variant Dim result As String Dim CurrentChar As String Dim i As Long result = "" For i = 1 To Len(CharString) CurrentChar = Mid(CharString, i, 1) If Not (UCase(CurrentChar) Like "[A-Z]") Then result = result & CurrentChar End If Next i RemoveAlphas = result End Function Function RemoveNonNumerics(CharString As Variant) As Variant Dim result As String Dim CurrentChar As String Dim i As Long result = "" For i = 1 To Len(CharString) CurrentChar = Mid(CharString, i, 1) If CurrentChar Like "[0-9]" Then result = result & CurrentChar End If Next i RemoveNonNumerics = result End Function
As you can see you end up with code that is very similar and could be rationalized by combining the separate functions into one multi-function UDF.
In addition to passing the data argument (the string), you will need to pass in a control argument. The control argument indicates what you want the function to do, e.g. 1 tells it to remove alpha characters from the string, a 2 tells it to remove non-numeric characters.
If the function only did two different things then you could use TRUE and FALSE as the control to switch between what it did.
If the function could perform more than 2 different calculations or processes then you can use numbers to choose between them
How To Structure The Function Code
If we are writing our function to do more than one thing, at some point we have to check what it is we want it to do.
Once we have done this check, we run the code to loop through the string and remove the non-desirable characters. We’ll end up with an algorithm like this
If we want to remove Alphabetic characters Loop through the string and remove alphabetic characters End if If we want to remove Non-Numeric characters Loop through the string and remove non-numeric characters End if
So for each type of character(s) we want to remove, we end up with a separate chunk of code for looping through our string.
The other approach is to have a single loop, with the tests inside the loop, like so:
Loop through the string one character at a time If we want to remove Alphabetic characters Remove the Alphabetic character End if If we want to remove Non-Numeric characters Remove the Non-Numeric character End if End Loop
So we only have one loop, but we are making multiple tests on each loop and we know only one of those will evaluate to true.
I’m not sure which approach would be fastest (maybe someone would like to benchmark this?) but I prefer not to test things that I know will be false, so I’m going with the first approach.
I've also added code for a 3rd function which removes non-alpha characters.
Download the Workbook
Enter your email address below to download the sample workbook.
The sample workbook contains the separate macros and functions to remove the various types of characters from a string.
It also contains the ‘super’ function CLEANSTRING which does everything in a single function.
If you liked this or know someone who could use it please click the buttons below to share it.