Creating Custom VBA Functions (UDFs)
Build your own Excel functions with User-Defined Functions (UDFs) in VBA
Quick Answer
Create a UDF: Use Function instead of Sub, add parameters, and return a value.
Function MyFunction(param1 As Double) As Double
MyFunction = param1 * 2
End Function
Use in Excel: Type =MyFunction(A1) in any cell
What are User-Defined Functions?
User-Defined Functions (UDFs) are custom Excel functions you create with VBA. They work just like built-in functions (SUM, VLOOKUP, IF) but perform calculations you design.
Built-in Functions:
=SUM(A1:A10)=VLOOKUP(A1, B:C, 2)=IF(A1>100, "High", "Low")Your Custom Functions:
=CalculateTax(A1)=GetFullName(B1, C1)=IsWeekend(D1)Benefits of UDFs:
- ✓ Reusable across worksheets and workbooks
- ✓ Simplify complex formulas
- ✓ Encapsulate business logic
- ✓ Auto-recalculate when inputs change
- ✓ Available in Formula Builder
Creating Your First UDF
Step 1: Open VBA Editor
Press Alt + F11 to open the VBA Editor
Step 2: Insert a Module
Insert → Module (functions must be in modules, not sheet code)
Step 3: Write the Function
Function DoubleIt(num As Double) As Double
DoubleIt = num * 2
End Function
Function name = return value assigns the result
Step 4: Use in Excel
In any cell, type: =DoubleIt(5)
Result: 10
Function Syntax
Function FunctionName(param1 As Type, param2 As Type) As ReturnType
' Code here
FunctionName = result ' Return value
End Function
Key Differences from Subs:
Subroutines (Sub)
- • Perform actions
- • Don't return values
- • Can't use in formulas
- • Run with F5 or button
Functions
- • Calculate and return value
- • Return exactly one value
- • Use in worksheet formulas
- • Auto-recalculate
Working with Parameters
Single Parameter:
Function AddTax(price As Double) As Double
AddTax = price * 1.08 ' 8% tax
End Function
Excel: =AddTax(100) → 108
Multiple Parameters:
Function GetFullName(firstName As String, lastName As String) As String
GetFullName = firstName & " " & lastName
End Function
Excel: =GetFullName(A1, B1)
Optional Parameters:
Function Greet(name As String, Optional title As String = "Mr.") As String
Greet = title & " " & name
End Function
=Greet("Smith") → "Mr. Smith"=Greet("Smith", "Dr.") → "Dr. Smith"
ParamArray for Variable Arguments:
Function SumAll(ParamArray values() As Variant) As Double
Dim total As Double, val As Variant
For Each val In values
total = total + val
Next val
SumAll = total
End Function
Excel: =SumAll(1, 2, 3, 4, 5) → 15
Return Types
| Return Type | Use For | Example |
|---|---|---|
| Double | Numbers with decimals | 123.45 |
| Long | Whole numbers | 12345 |
| String | Text | "Hello" |
| Boolean | True/False | TRUE |
| Date | Dates and times | 12/3/2025 |
| Variant | Any type (flexible) | Anything |
Boolean Function Example:
Function IsWeekend(dateValue As Date) As Boolean
Dim dayNum As Integer
dayNum = Weekday(dateValue)
IsWeekend = (dayNum = 1 Or dayNum = 7) ' Sun or Sat
End Function
Variant Return (Handles Errors):
Function SafeDivide(num As Double, divisor As Double) As Variant
If divisor = 0 Then
SafeDivide = CVErr(xlErrDiv0) ' #DIV/0! error
Else
SafeDivide = num / divisor
End If
End Function
Practical UDF Examples
1. Calculate Age from Birthdate:
Function CalculateAge(birthDate As Date) As Long
CalculateAge = DateDiff("yyyy", birthDate, Date)
' Adjust if birthday hasn't occurred this year
If Month(birthDate) > Month(Date) Or _
(Month(birthDate) = Month(Date) And Day(birthDate) > Day(Date)) Then
CalculateAge = CalculateAge - 1
End If
End Function
2. Remove Extra Spaces:
Function CleanText(text As String) As String
' Remove leading/trailing spaces and double spaces
CleanText = Application.Trim(text)
End Function
3. Count Words in Text:
Function WordCount(text As String) As Long
Dim words() As String
If Len(Trim(text)) = 0 Then
WordCount = 0
Else
words = Split(Trim(text), " ")
WordCount = UBound(words) + 1
End If
End Function
4. Reverse Text:
Function ReverseText(text As String) As String
ReverseText = StrReverse(text)
End Function
5. Extract Initials:
Function GetInitials(fullName As String) As String
Dim words() As String, i As Integer
Dim initials As String
words = Split(Trim(fullName), " ")
For i = 0 To UBound(words)
If Len(words(i)) > 0 Then
initials = initials & UCase(Left(words(i), 1))
End If
Next i
GetInitials = initials
End Function
UDFs with Range Parameters
Functions can accept Excel ranges as parameters and process multiple cells.
Count Cells Above Threshold:
Function CountAbove(rng As Range, threshold As Double) As Long
Dim cell As Range
Dim count As Long
For Each cell In rng
If IsNumeric(cell.Value) Then
If cell.Value > threshold Then
count = count + 1
End If
End If
Next cell
CountAbove = count
End Function
Excel: =CountAbove(A1:A10, 100)
Sum Positive Values Only:
Function SumPositive(rng As Range) As Double
Dim cell As Range
Dim total As Double
For Each cell In rng
If IsNumeric(cell.Value) Then
If cell.Value > 0 Then
total = total + cell.Value
End If
End If
Next cell
SumPositive = total
End Function
Volatile Functions
By default, UDFs only recalculate when their input cells change. Make a function volatile to recalculate whenever Excel recalculates.
Function CurrentTime() As String
Application.Volatile ' Force recalc every time
CurrentTime = Format(Now, "hh:mm:ss AM/PM")
End Function
Warning: Volatile functions slow down Excel. Use sparingly!
UDF Best Practices
✓
Use descriptive names:
CalculateTax not CT✓
Specify data types: Always declare parameter and return types
✓
Add error handling: Return Variant with CVErr for error values
✓
Keep functions simple: One task per function
✓
Add comments: Explain what function does and parameters
✓
Avoid Select/Activate: Work directly with ranges
✓
Test thoroughly: Try edge cases (empty cells, errors, large ranges)
✓
Save as .xlsm: Macro-enabled workbook to preserve UDFs
UDF Limitations
UDFs Cannot:
- ❌ Modify other cells (read-only from worksheet)
- ❌ Change formatting or cell properties
- ❌ Insert/delete rows or columns
- ❌ Open/close workbooks
- ❌ Change Excel settings
- ❌ Show message boxes (use in Sub instead)
Solution: Use Sub routines for actions, Functions only for calculations
Sharing UDFs
Method 1: Personal Macro Workbook
Store UDFs in PERSONAL.XLSB - available in all workbooks
- Developer → Record Macro → Store in Personal Macro Workbook
- Stop recording immediately
- Alt+F11 → Find VBAProject (PERSONAL.XLSB)
- Add your functions to a module there
Method 2: Add-in (.xlam)
Create Excel Add-in for professional distribution
- Save workbook as Excel Add-in (.xlam)
- File → Options → Add-ins → Go (Excel Add-ins)
- Browse and select your .xlam file
- Functions available in all workbooks