Contents
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.