Cat On A Spreadsheet

Building a Custom Excel Function Library with VBA (UDFs)

If you're a power user of Excel, you've probably hit the limits of built-in functions like VLOOKUP, IF, or LET. That’s where User Defined Functions (UDFs) come in—custom VBA functions that behave like native Excel formulas.

But instead of just creating ad hoc functions, what if you could build an entire reusable function library? Think: your own personal XL.XLOOKUPWITHDEFAULT() or MYTOOLS.ISISOFORMAT()—functions that work across any workbook you use.

In this post, you’ll learn:

How UDFs work in Excel VBA
How to build a modular library of custom functions
Best practices for creating efficient, safe UDFs
How to distribute and use your function library across multiple workbooks

🧠 What Are UDFs (User Defined Functions)?

UDFs are custom VBA functions that can be used directly in Excel cells like native functions.

Example:

  Function Square(n As Double) As Double
 
      Square = n * n
 
 End Function

In Excel, you'd use: =Square(5) → Returns 25

🛠️ Step-by-Step: Creating a Function Library

Instead of building random UDFs in random workbooks, we’ll create a modular codebase that you can load into any project or distribute as an add-in.

📁 Step 1: Create a Module Called MyFunctions

Open the VBA editor (Alt + F11), then:

Insert > Module
Rename the module to MyFunctions
Start adding your custom UDFs there

Example functions:

  ' Check if a value is a prime number Function IsPrime(n As Long) As Boolean
 
      Dim i As Long
 
      If n < 2 Then IsPrime = False: Exit Function
 
      For i = 2 To Sqr(n)
 
           If n Mod i = 0 Then IsPrime = False: Exit Function
 
      Next i
 
      IsPrime = True
 
 End Function
 
 ' Convert a string to proper case (Title Case)
 
 Function ToTitleCase(text As String) As String
 
      Dim words() As String, i As Long
 
      words = Split(LCase(text))
 
      For i = 0 To UBound(words)
 
           words(i) = UCase(Left(words(i), 1)) & Mid(words(i), 2)
 
      Next i
 
      ToTitleCase = Join(words, " ")
 
 End Function

Now in Excel, use:

=IsPrime(17) → TRUE

=ToTitleCase("hello world") → Hello World

📚 Function Categories to Include

Here are ideas for categories in your UDF library:

Text tools: ToTitleCase, StripHTML, CountWords

Math/Statistics: MedianIgnoreZeros, ModeMulti, PercentRank

Date/time: IsISODate, WorkDaysBetween, IsLastDayOfMonth

Logic Helpers: IfErrorLike, HasValue, InList

Regex (via reference): RegexMatch, RegexExtract, RegexReplace

Data validation: IsEmail, IsValidDate, IsIBAN

🧼 Best Practices for Writing Clean UDFs

1. Make it safe

Always trap errors, especially with text, dates, or empty inputs.

  Function SafeDivide(a As Double, b As Double) As Variant
 
      If b = 0 Then
 
           SafeDivide = CVErr(xlErrDiv0)
 
      Else
 
           SafeDivide = a / b
 
      End If
 
 End Function

2. Keep it fast

Avoid interacting with the worksheet unless absolutely needed. Do not use .Select, .Activate, etc.

3. Avoid volatile functions unless necessary

If your UDF doesn't depend on the current date/time or cell position, don't make it Application.Volatile.

4. Handle arrays (for power users)

Make your UDFs return arrays for use in dynamic spills:

  Function MultiplyRange(arr As Range, factor As Double) As Variant
 
      Dim r As Variant, i As Long, j As Long
 
      r = arr.Value
 
      For i = 1 To UBound(r, 1)
 
           For j = 1 To UBound(r, 2)
 
                r(i, j) = r(i, j) * factor
 
           Next j
 
      Next i
 
      MultiplyRange = r
 
 End Function

Used like:

=MultiplyRange(A1:C3, 2) → returns array

🔁 Reusing Your Library Across Workbooks

You can reuse your UDFs without copy-pasting every time.

Option 1: Save as a Personal Macro Workbook (for yourself)

Save your MyFunctions module in PERSONAL.XLSB
It loads every time Excel starts
Works only on your machine

Option 2: Create an Excel Add-in (.xlam)

In the VBA editor, File > Save As > Excel Add-in (*.xlam)
Then:
- Open Excel > File > Options > Add-ins
- Manage: Excel Add-ins > Go... > Browse to your .xlam

Now your UDFs are available in every workbook.

Option 3: Export as .bas module for sharing

Right-click module > Export File (.bas)
Import into other VBA projects

🧪 Example: Custom Lookup with Default

Let’s build a VLOOKUP-style function that returns a default value instead of #N/A.

  Function VLookupWithDefault(lookup_value As Variant, _
 
      table_array As Range, col_index As Long, _
 
      Optional default_value As Variant = "Not found") As Variant
 
      On Error GoTo ErrHandler
 
      VLookupWithDefault = Application.WorksheetFunction.VLookup(lookup_value, table_array, col_index, False)
 
      Exit Function
 
 ErrHandler:
 
      VLookupWithDefault = default_value
 
 End Function

Usage:

=VLookupWithDefault("XYZ", A2:B100, 2, "Missing")

🛡️ Error Handling Tips

Return CVErr(xlErrValue) when the user inputs something invalid
Use On Error Resume Next only when carefully scoped
Avoid breaking loops or functions with unhandled worksheet errors

📈 Bonus: Document Your Functions with Intellisense

(Kind of)

VBA doesn't support true IntelliSense for UDFs in the formula bar, but you can still:

Add comments before each function to explain usage
Use descriptive names and parameters with default values
Build a cheat sheet worksheet that lists all UDFs and examples

🎯 Final Thoughts

Creating a personal function library in VBA makes Excel work your way. Whether you're streamlining reports, validating data, or creating smarter formulas, UDFs put you in control. And once you start organizing your UDFs into a library, you’ll never go back to a one-off macro again.