User-Defined Functions in VBA
Learn how to create custom VBA functions, return values, pass parameters, use optional arguments, and build reusable calculations for Excel worksheets and automation projects.
1. What Is a User-Defined Function?
A User-Defined Function (UDF) is a function
created by a programmer to perform a specific calculation
or operation. VBA provides built-in functions such as
Len, UCase, and Round.
When these built-in functions do not meet a requirement,
you can create your own function.
A function can accept inputs, process those inputs, and return a result. A UDF can also be called from another VBA procedure or, when appropriately defined, used as a custom formula in an Excel worksheet.
2. Difference Between Sub and Function
| Sub Procedure | Function Procedure |
|---|---|
| Used mainly to perform actions. | Used to calculate or return a value. |
Declared using Sub. |
Declared using Function. |
| Does not return a function result directly. | Returns a result by assigning it to the function name. |
| Can display messages and format cells. | Can calculate totals, percentages, grades, and other results. |
| Called as a procedure. | Can be used in an expression or assigned to a variable. |
Example: Sub procedure
Sub DisplayTotal()
MsgBox "Total amount is 1000."
End Sub
Example: Function procedure
Function GetTotal() As Long
GetTotal = 1000
End Function
Sub TestGetTotal()
Dim result As Long
result = GetTotal()
MsgBox result
End Sub
3. Syntax of a User-Defined Function
Function FunctionName(parameters) As DataType
' Statements
FunctionName = result
End Function
Explanation
Functiondeclares a function procedure.FunctionNameis the name used to call the function.parametersrepresent the inputs accepted by the function.As DataTypedeclares the return data type.FunctionName = resultassigns the return value.End Functionmarks the end of the function.
4. Create a Simple Function
The following function returns a fixed message. It demonstrates how a function can return a String value.
Function WelcomeMessage() As String
WelcomeMessage = "Welcome to VBA Functions!"
End Function
Sub TestWelcomeMessage()
MsgBox WelcomeMessage()
End Sub
The function assigns text to its own name, and the calling procedure displays the returned value.
5. Function with One Parameter
A function can accept an input parameter and use it to calculate a result.
Example: Square of a number
Function SquareNumber(ByVal number As Double) As Double
SquareNumber = number * number
End Function
Sub TestSquareNumber()
MsgBox SquareNumber(5)
MsgBox SquareNumber(8)
End Sub
| Input | Calculation | Returned Value |
|---|---|---|
| 5 | 5 × 5 | 25 |
| 8 | 8 × 8 | 64 |
6. Function with Multiple Parameters
You can pass multiple inputs to a function. Each parameter should have an appropriate data type.
Example: Calculate total price
Function CalculateTotal( _
ByVal quantity As Long, _
ByVal unitPrice As Currency) As Currency
CalculateTotal = quantity * unitPrice
End Function
Sub TestCalculateTotal()
Dim totalAmount As Currency
totalAmount = CalculateTotal(5, 250)
MsgBox "Total Amount: " & Format(totalAmount, "0.00")
End Sub
7. Function Return Data Types
The return type determines the kind of value the function returns. Choose a data type that suits the result.
| Data Type | Typical Use |
|---|---|
Long |
Whole-number counts and identifiers. |
Double |
Decimal calculations. |
Currency |
Financial amounts requiring fixed decimal precision. |
String |
Text values. |
Boolean |
True or False results. |
Date |
Date and time values. |
Variant |
Values that may require different types or error values. |
Example: Check whether a number is even
Function IsEven(ByVal number As Long) As Boolean
IsEven = (number Mod 2 = 0)
End Function
Sub TestIsEven()
MsgBox IsEven(10)
MsgBox IsEven(7)
End Sub
8. Use a UDF as an Excel Worksheet Formula
A suitable Public Function in a standard VBA module can be called from an Excel cell, just like a custom formula. The function should calculate a result rather than modify unrelated worksheet content.
Example: Calculate simple interest
Public Function SimpleInterest( _
ByVal principal As Double, _
ByVal annualRate As Double, _
ByVal years As Double) As Double
SimpleInterest = principal * annualRate * years / 100
End Function
How to use it in Excel
After adding the function to a standard module, enter this formula into a worksheet cell:
=SimpleInterest(10000, 5, 2)
Simple interest = Principal × Rate × Time ÷ 100.
9. Practical UDF: Calculate Employee Bonus
Suppose an employee receives a bonus based on a percentage of annual salary. You can create a function and use it in VBA or as a worksheet formula.
Example: Bonus calculation
Public Function CalculateBonus( _
ByVal annualSalary As Currency, _
ByVal bonusPercentage As Double) As Currency
If annualSalary < 0 Then
CalculateBonus = 0
Exit Function
End If
If bonusPercentage < 0 Or bonusPercentage > 100 Then
CalculateBonus = 0
Exit Function
End If
CalculateBonus = annualSalary * bonusPercentage / 100
End Function
Example inputs and results
| Annual Salary | Bonus % | Bonus Amount |
|---|---|---|
| 300,000 | 10% | 30,000 |
| 500,000 | 15% | 75,000 |
| 600,000 | 20% | 120,000 |
In an Excel cell, use a formula such as:
=CalculateBonus(A2,B2)
10. Optional Parameters in Functions
Optional parameters allow callers to omit an input when a default value is suitable. Optional parameters must follow required parameters.
Example: Calculate discounted price
Function FinalPrice( _
ByVal originalPrice As Currency, _
Optional ByVal discountRate As Double = 0.1) As Currency
FinalPrice = originalPrice * (1 - discountRate)
End Function
Sub TestFinalPrice()
MsgBox FinalPrice(1000)
MsgBox FinalPrice(1000, 0.2)
End Sub
| Call | Discount | Final Price |
|---|---|---|
FinalPrice(1000) |
10% | 900 |
FinalPrice(1000, 0.2) |
20% | 800 |
11. ByVal and ByRef in Functions
Function parameters can also use ByVal or
ByRef. These keywords determine how a parameter
interacts with a variable passed by the caller.
Example: ByVal
Function DoubleByVal(ByVal number As Long) As Long
number = number * 2
DoubleByVal = number
End Function
Sub TestDoubleByVal()
Dim x As Long
x = 10
MsgBox DoubleByVal(x)
MsgBox x
End Sub
The function returns 20, while the caller's variable remains 10.
Example: ByRef
Function DoubleByRef(ByRef number As Long) As Long
number = number * 2
DoubleByRef = number
End Function
Sub TestDoubleByRef()
Dim x As Long
x = 10
MsgBox DoubleByRef(x)
MsgBox x
End Sub
Here the function returns 20 and the caller's variable becomes 20 because it was passed ByRef. For predictable UDF behavior, functions are often best designed to calculate a result without unexpectedly modifying caller variables.
12. Handle Invalid Inputs
A function should account for invalid input and division by zero. The following example returns a Boolean result and writes the calculated quotient through a ByRef argument.
Example: Safe division procedure design
Function TryDivide( _
ByVal numerator As Double, _
ByVal denominator As Double, _
ByRef result As Double) As Boolean
If denominator = 0 Then
TryDivide = False
Exit Function
End If
result = numerator / denominator
TryDivide = True
End Function
Sub TestTryDivide()
Dim answer As Double
If TryDivide(20, 4, answer) Then
MsgBox "Result: " & answer
Else
MsgBox "Cannot divide by zero."
End If
If TryDivide(20, 0, answer) Then
MsgBox "Result: " & answer
Else
MsgBox "Cannot divide by zero."
End If
End Sub
13. Important Rules for Worksheet UDFs
- Place ordinary worksheet UDFs in a standard VBA module.
- Use a Public Function when it should be available as a worksheet formula.
- Return a value compatible with the function's declared return type.
- Prefer calculations that depend on supplied arguments.
- Do not use a worksheet UDF as a replacement for a macro that formats cells or changes unrelated worksheet contents.
- Some Excel actions and application features are restricted when a function is called from a worksheet cell.
- VBA functions require a compatible desktop Excel environment; VBA UDFs do not run in Excel for the web.
- Save the workbook in a format that preserves VBA code.
14. Common Mistakes to Avoid
- Forgetting to assign a result to the function name.
- Using an unsuitable return data type.
- Passing the wrong number of arguments.
- Confusing a Sub procedure with a Function procedure.
- Ignoring invalid input and division-by-zero cases.
- Using worksheet UDFs to modify unrelated worksheet cells.
- Forgetting to save the workbook in a macro-compatible format.
- Assuming a VBA UDF works in Excel for the web.
15. Practice Exercises
Write separate functions for these tasks. Test each function with normal values, boundary values, and invalid inputs.
- Create a function that returns the square of a number.
- Create a function that returns the cube of a number.
- Create a function that accepts two numbers and returns their sum.
- Create a function that calculates the area of a rectangle.
- Create a function that calculates simple interest.
- Create a function that returns True if a number is even.
- Create a function that calculates an employee bonus.
- Create a function that calculates a discount with an optional rate.
- Create a function that returns a student's grade based on marks.
- Create a worksheet UDF that calculates a product's total price.
- Create a function that validates a percentage from 0 to 100.
- Create a safe division function that handles a zero denominator.
- Demonstrate ByVal and ByRef using a numeric variable.
- Call a custom function from both a VBA Sub and an Excel cell.
Important Points to Remember
- A UDF is a custom function created by the programmer.
- Declare functions using
FunctionandEnd Function. - Assign the returned result to the function name.
- Use parameters to accept input values.
- Choose a suitable return data type.
- Use
Optionalparameters when a default value is appropriate. - Use
ByValorByRefdeliberately. - Suitable Public functions in standard modules can be used as worksheet formulas.
- Validate input and handle exceptional cases such as division by zero.
Summary
User-Defined Functions extend VBA by allowing programmers to create reusable calculations. Functions can accept one or more arguments, return values of appropriate data types, and use optional parameters when flexibility is needed.
A well-designed function validates its inputs, handles exceptional cases, and returns a predictable result. Suitable UDFs can be called from other VBA procedures or used as custom Excel formulas.