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