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.
Creating a UDF (User Defined Function) in Excel
Michael Pierce
One thing I would suggest is using nested IF (If Then ElseIf) or Select Case statements. That way, once you match something, you avoid evaluating further IF statements that you know will be false. With only 3 options, either method is comparable. If you have more than 3 conditions, though, I’d suggest using the Select Case.
Philosophically, though, I would say that merging code into one function is better if you can maximize reuse. In your example, the only thing that really changes across the various For loops is the string pattern you’re comparing against. Otherwise, the code is identical. If you can merge all of those, it will make your code smaller, more efficient and you’re less likely to introduce errors. Here is my variation of the function:
Function MyCleanString(CharString As Variant, Mode As Integer) As Variant
Dim result As String
Dim CurrentChar As String
Dim i As Long
Dim CleanPattern As String
If Mode 3 Then Exit Function
Select Case Mode
Case 1
CleanPattern = “[A-Z]”
Case 2
CleanPattern = “[!A-Z]”
Case Else
CleanPattern = “[0-9]”
End Select
For i = 1 To Len(CharString)
CurrentChar = Mid(CharString, i, 1)
If UCase(CurrentChar) Like CleanPattern Then
result = result & CurrentChar
End If
Next i
MyCleanString = result
End Function
Philip Treacy
Hi Michael,
I wanted this to be a simple example for people so didn’t elaborate on the use of things like SELECT CASE but I do agree with you and would use that in a lot of situations.
For similar reasons I didn’t reduce the code as well as you did.
Thanks for your more efficient code.
Cheers
Phil
Craig Hatmaker
VBA can access some extremely powerful Windows routines. One that addresses this problem without loops is RegEx(). RegEx() can do FAR more than just clean strings. But to not overwhelm readers, here is a stripped down “multi-function” UDF leveraging RegEx().
Public Function Remove(ByVal sString As String, _
ByVal sPattern As String, _
Optional ByVal bIgnoreCase As Boolean = True, _
Optional ByVal bGlobal As Boolean = True) As String
‘ Description:Remove patterns from a string
‘ Inputs: sString String to search
‘ sPattern Regular Expression to apply
‘ bIgnoreCase Case sensitivity
‘ bGlobal Apply to all instances within string
‘ Outputs: Me String with sPattern removed from sString
‘ Requisites: *None
‘ Notes: RegEx Tester: http://regexr.com/
‘ RegEx Object Model: https://msdn.microsoft.com/en-us/library/30wbz966(v=vs.110).aspx
‘ VBScript: https://msdn.microsoft.com/en-us/library/ms974570.aspx
‘ Example: Remove all numbers =Remove(“This is a test 999”, “\d”)
‘ Remove all numbers =Remove(“This is a test 999”, “[0-9]”)
‘ Remove all characters =Remove(“This is a test 999”, “[A-Z]”)
‘ Remove all lower case =Remove(“This is a test 999”, “[a-z]”, FALSE)
‘ Remove all “is” =Remove(“This is a test 999”, “is”)
‘ Remove first “is” =Remove(“This is a test 999”, “is”, TRUE, FALSE)
‘ Date Ini Modification
‘ 12/01/15 CWH Initial Programming
‘ Declarations
Const cRoutine As String = “Remove”
Static oRegEx As Object ‘Regular Expression Object
‘ Error Handling Initialization
On Error GoTo ErrHandler
Remove = vbNullString
‘ Initialize Variables
If oRegEx Is Nothing Then Set oRegEx = CreateObject(“vbscript.regexp”)
oRegEx.Pattern = sPattern
oRegEx.IgnoreCase = bIgnoreCase
oRegEx.Global = bGlobal
‘ Procedure
Remove = oRegEx.Replace(sString, “”)
ErrHandler:
Select Case Err.Number
Case Is = 0: ‘No error. Do nothing
Case Is = 5018, 5020
MsgBox Prompt:=”Invalid pattern:” & sPattern, _
Buttons:=vbOKOnly + vbMsgBoxHelpButton, _
Title:=cRoutine, _
HelpFile:=Err.HelpFile, _
Context:=Err.HelpContext
Case Else:
MsgBox Prompt:=Err.Number & “:” & Err.Description, _
Buttons:=vbOKOnly + vbMsgBoxHelpButton, _
Title:=cRoutine, _
HelpFile:=Err.HelpFile, _
Context:=Err.HelpContext
End Select
End Function
Philip Treacy
Thanks Craig. RegEx is great for this kind of thing.
Regards,
Phil