User-Defined Functions in Excel VBA

On This Page

Creating User-Defined Functions

The syntax for creating a user-defined function in Excel VBA is as follows:

code.vba 
[Public|Private] Function funcName([arg1 As type1, arg2 As type2,...]) [As type]
    [statements]
    [funcName = expression]
    [Exit Function]
    [statements]
    [funcName = expression]
End Function

Public: Optional. Indicates the function is public and can be called by procedures in any module. If omitted, the default is Public.

Private: Optional. Indicates the function is private and can only be called by procedures in the current module.

Function: Required. Marks the start of the function declaration.

funcName: Required. Name of the function.

arg1, arg2,...: Optional. Argument list of the function. Each argument consists of a name and a data type (e.g., num As Integer). Arguments can be passed by value (ByVal, default) or by reference (ByRef).

type1, type2,...: Data types of the arguments.

type: Optional. Return value type of the function. If omitted, the default is Variant.

statements: Statements to be executed in the function body.

funcName = expression: Assigns the result of the expression to the function name (i.e., the return value). This can appear multiple times in the function body.

Exit Function: Optional. Exits the function prematurely.

End Function: Required. Marks the end of the function declaration.

Example 1: Create a function named AddOne that takes an integer as input and returns the integer plus 1. Sample file path: Samples\ch08\Excel VBA\UDF.xlsm.

code.vba 
Function AddOne(num As Integer) As Integer
    AddOne = num + 1
End Function

Example 2: Create a function named GetArea that calculates the area of a rectangle (length × width). Sample file path: Samples\ch08\Excel VBA\UDF.xlsm.

code.vba 
Function GetArea(length As Double, width As Double) As Double
    GetArea = length * width
End Function

Example 3: Create a function named JudgeSign that determines whether an integer is positive, negative, or zero. Sample file path: Samples\ch08\Excel VBA\UDF.xlsm.

code.vba 
Function JudgeSign(num As Integer) As String
    If num > 0 Then
        JudgeSign = "Positive"
    ElseIf num < 0 Then
        JudgeSign = "Negative"
    Else
        JudgeSign = "Zero"
    End If
End Function

Calling User-Defined Functions

User-defined functions created in Excel VBA can be called in worksheet cells just like built-in functions.

For example, in the UDF.xlsm file:

Enter =AddOne(5) in cell A1 → returns 6.

Enter =GetArea(3, 4) in cell A2 → returns 12.

Enter =JudgeSign(-2) in cell A3 → returns "Negative".

Notes on Using User-Defined Functions

Scope: Public functions can be called by any procedure in the workbook; private functions can only be called by procedures in the same module.

Return Value: A function must assign a value to the function name (via funcName = expression) before exiting; otherwise, the return value is Empty (for Variant type) or 0 (for numeric types).

Arguments: Arguments passed by value (ByVal) do not affect the original variable; arguments passed by reference (ByRef) can modify the original variable.

Volatile Functions: If a function needs to recalculate whenever the worksheet changes (even if its arguments are not changed), add Application.Volatile at the start of the function. Example:

code.vba 
Function NowTime() As String
    Application.Volatile
    NowTime = Format(Now, "hh:mm:ss")
End Function

This function returns the current time and updates automatically when the worksheet changes.