A General Overview of VBA Functions
VBA function modules can be used to segment your VBA project into more manageable and maintainable units. These modules can easily be utilized on future projects and are capable of perform a wide range of tasks and is pretty much limited to your imagination. So let’s look at a few basic examples. Although this might not represent an exhaustive list, it should enable you to get started on your project.
Example 1
A function module can be developed to execute without receiving or returning any variables. Such modules can just be executed using the Call Function. For example:
Function WhatAGreatSite()
On Error Resume Next
Msgbox “ExcelHelp is a great site!”
End Function
You execute this module using the following command Call WhatAGreatSite
Example 2
Function modules can also be designed to receive and return values. For example, let’s assume that we want to develop a module that converts distances from miles into kilometers.
Function ConvertMilesToKilometers(Distance As Long) As Long
On Error Resume Next
Dim ConvertedValue As Long
ConvertedValue = Distance * 1.609344
ConvertMilesToKilometers = ConvertedValue
End Function
Should you require to utilize the function module you can do so by using one of the following commands, namely:
Dim Trip as Long
Trip =ConvertMilesToKilometers(1000)
Note: Note the declaration of the module. We use the As Long at the end to inform the compiler that this function module will be returning a value.
Example 3
Sometimes you might want to transfer a variable that you have already been working with to a function so that you can change it. You would achieve this by declaring the function module using the ByRef keyword. Using our previous example it would look something like this.
Function ConvertMilesToKilometers(ByRef Distance As Long)
On Error Resume Next
Distance = Distance * 1.609344
End Function
Variables
So, when we pass the variable to the function above we can change the variable and the new value will be reflected as we have passed the variable to the module to use.
Also, another important aspect to Function Module are variables.
To declare variables for use we use the Dim keyword
To initialize a variable we can just assign a value to the variable. For example:
Dim TestValue as Long‘Declaring a variable with a long Data Type
Long = 10‘Now we have assigned the value 10 to the variable
When we initialize an object variable we use the command Set. Feel free to refer to the WorkBook and WorkSheet examples as a good example of how to use it.
Note: Be sure to use the ‘Option Explicit’ Declaration at the beginning of your VBA module. That will force to compiler to determine whether or not variables have correctly been declared before they can be used. It is great for avoiding any significant problems later on in your project.