How to use Function statement in VBA?

The Function statement in VBA (Visual Basic for Applications) is used to define a user-defined function. A function is a block of code that performs a specific task and returns a value. Functions can be used to simplify your code and can be called from anywhere in your VBA project.

Syntax:

VBA

Function FunctionName([arglist]) As ReturnType
    [statements]
    FunctionName = [returnValue]
    [Exit Function]
    [statements]
End Function

FunctionName: The name of the function.
arglist: Optional. A list of arguments (parameters) that you pass to the function, separated by commas.
ReturnType: The type of value the function returns (e.g., Integer, String, Boolean).
statements: The lines of code that define the function’s operations.
FunctionName = [returnValue]: Assigns a value to the function name, which will be the result returned by the function.
Exit Function: Optional. Exits the function prematurely.

Example:

Let’s create a simple function to add two numbers:

VBA

Function AddNumbers(Number1 As Integer, Number2 As Integer) As Integer
    AddNumbers = Number1 + Number2
End Function

You can call this function from anywhere in your VBA project:

VBA

Sub TestFunction()
    Dim result As Integer
    result = AddNumbers(5, 10)
    MsgBox "The result is " & result
End Sub

Key Points:

Reusable Code: Functions allow you to write reusable code. Instead of writing the same code multiple times, you can write a function and call it as needed.
Return Value: Unlike a Sub procedure, a function returns a value. You assign the return value to the function name itself.
Parameters: Functions can take parameters, allowing you to pass different values each time you call the function.
Data Types: Always specify the data type for parameters and the return type of the function to ensure type safety.
Scope: By default, functions in VBA are Public, meaning they can be accessed from any module within the project. You can also define them as Private if they should only be accessed within the same module.

Using functions is a great way to organize your code, make it more readable, and avoid redundancy. They are an essential part of VBA programming.

Switch the language

facebooklinkedin

Unlock Your Potential

Excel

Access

Power BI

Help us grow the project

English

by Jonathan Mojica
on December 23, 2023