• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • SALE 20% Off All Courses
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Logout
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Creating Multi-Function UDF’s

You are here: Home / Excel VBA / Creating Multi-Function UDF’s
Creating Multi-Function UDF's
September 21, 2016 by Philip Treacy

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.
Please enter a valid email address.

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 Multi-Function UDF's

More User Defined Function Posts

writing udfs in excel with python

Writing UDFs in Excel With Python

Write Excel UDFs in Python. Use the power of the vast Python libraries for data science, statistics, web scraping or database connectivity.
Return an array from a udf

Return An Array From A UDF

If you can return an array from a udf you are able to return multiple pieces of data and place them into multiple cells
Return a range from a UDF

Return a Range from a UDF

If you return a range from a UDF this allows you to use that range in other functions and calculations.
create an excel add-in for user defined functions udfs

Create an Excel Add-In for User Defined Functions (UDF’s)

How to create an Excel add-in for user defined functions (UDF's) to make them easier to use.
creating a reference to personal.xlsb for user defined function udf

Creating a Reference to PERSONAL.XLSB for User Defined Functions (UDF’s)

How to create a reference to PERSONAL.XLSB so that using User Define Functions (UDF's) is easier

Creating a UDF (User Defined Function) in Excel

Create your own functions in Excel

More Excel VBA Posts

Display All Matches from Search in Userform ListBox

Display All Matches from Search in Userform ListBox

Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.
animating excel charts

Animating Excel Charts

Use animation correctly to enhance the story your data is telling. Don't animate your chart just for some eye candy. Sample code and workbook to download.
dynamic data validation lists in userforms

Dynamic Data Validation Lists in Userforms

Data validation lists using the same source that are dynamically modified to prevent the same choice being made in each list.
show report filter pages for power pivot pivottables

Show Report Filter Pages for Power Pivot PivotTables

PivotTables created from Power Pivot can't use the 'Show Report Filter Pages' option. But this piece of VBA allows you to do just that.
charting real time data in excel

Charting Real Time Data in Excel

Receive data in real time and chart the data as it arrives. Can be used to chart things like stock prices or sensor readings. Sample code and workbook
select multiple items from drop down data validation list

Select Multiple Items from Drop Down (Data Validation) List

Choose multiple items from a data validation (drop down) list and store them all in the same cell. Sample workbook with working VBA.
Excel Calendar (Date Picker) to Use in Worksheets and Userforms

Multi-Language Excel Calendar (Date Picker) for Worksheets and Userforms

Easy to use, highly customizable and multi-language. This date picker is implemented as a userform that is simple to integrate into your workbook.
automating and emailing pivot table reports

Automating and Emailing Pivot Table Reports

Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
search for data with userform

Searching for Data With a User Form

Search a list of records (like a table) using a user form, and then populate the fields of the search form when the record is found.
Checking values in range objects with vba

Checking Values in Range Objects With VBA

Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.




Category: Excel VBATag: user defined function
Previous Post:Creating a UDF (User Defined Function) in Excel
Next Post:Excel Styles

Reader Interactions

Comments

  1. Michael Pierce

    September 28, 2016 at 6:33 am

    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
    • Philip Treacy

      October 5, 2016 at 2:30 pm

      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. Craig Hatmaker

    September 23, 2016 at 12:44 am

    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

    Reply
    • Philip Treacy

      September 23, 2016 at 4:43 pm

      Thanks Craig. RegEx is great for this kind of thing.

      Regards,

      Phil

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Course Sale

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
trustpilot excellent rating
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.