VBA Functions

A function in VBA is a block of statements that perform a specific task and returns a result, which can be used multiple times within a program. As with a lot of languages, VBA has a number of functions built in, however, it is also possible to create user defined functions. The statements within a function are only executed once the function is called, so, if the function is never called then they won’t run. In VBA, a function can be defined and called as follows.

' Function definition.
Function function_name(parameter_name As parameter_type) As return_type

   function_name = calculation_using_parameters

End Function

' Variable declaration.
Dim variable_name As variable_type

' Function call.
variable_name = function_name(parameter_value)

The function name is not case sensitive, so ‘function_name’ and ‘Function_Name’ are both valid calls to the above function. Although numbers can be used within a function name, it must always start with a letter. It is also possible to include more than one parameter in a function call and definition by separating them with a comma. Finally, the data type of the return value must be specified. The result returned from a function can be assigned to a variable as shown.

The example function below takes the age of a person in years as an integer and returns the age in months, again as an integer. The result is then displayed in cell ‘A1’ of a spreadsheet.

' Function definition.
Function AgeInMonths(age As Integer) As Integer

   AgeInMonths = age * 12
    
End Function

' Variable declaration.
Dim ageMonths As Integer

' Function call.
ageMonths = AgeInMonths(20)

' Populate cell A1 with the result.
Range("A1").Value = "Age in months: " & ageMonths

It is possible to make a parameter for a function optional by using the ‘Optional’ key word and specifying a default value. Here the ‘age’ parameter has been made optional and given a default value of ‘1’, meaning that the function can be called with no parameters.

' Function definition.
Function AgeInMonths(Optional age As Integer = 1) As Integer

   AgeInMonths = age * 12
    
End Function

' Variable declaration.
Dim ageMonths As Integer

' Function call.
ageMonths = AgeInMonths()

' Populate cell A1 with the result.
Range("A1").Value = "Age in months: " & ageMonths