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.