Creating Multi-Function UDF’s

Philip Treacy

September 21, 2016

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.

By submitting your email address you agree that we can email you our Excel newsletter.

It also contains the ‘super’ function CLEANSTRING which does everything in a single function.

AUTHOR Philip Treacy Co-Founder / Owner at My Online Training Hub

Systems Engineer with 30+ years working for companies like Credit Suisse and E.D.S. in roles as varied as Network & Server Support, Team Leader and Consultant Project Manager.

These days Philip does a lot of programming in VBA for Excel, as well as PHP, JavaScript and HTML/CSS for web development.

He's particularly keen on Power Query where he writes a lot of M code.

When not writing blog posts or programming for My Online Training Hub, Philip can be found answering questions on the Microsoft Power BI Community forums where he is a Super User.

4 thoughts on “Creating Multi-Function UDF’s”

  1. 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

    Reply
    • 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

      Reply
  2. 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: https://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

    Reply

Leave a Comment

Current ye@r *