Table of Contents
To return a value from a VBA Function, you must use the ‘Return’ statement. This statement can be used to return a single value from the function, or a range of values. To illustrate this, an example of how to return a single value from a function is: Function AddNums(num1 As Integer, num2 As Integer) as Integer
Dim sum As Integer
sum = num1 + num2
Return sum
End Function
In this example, the function ‘AddNums’ returns the sum of the two numbers passed to it. Similarly, a range of values can be returned from a function using the ‘Return’ statement. For example: Function AddNumsRange(num1 As Integer, num2 As Integer) as Range
Dim range As Range
Set range = Range(“A1:A5”)
Range(“A1”).Value = num1 + num2
Range(“A2”).Value = num1 – num2
Range(“A3”).Value = num1 * num2
Range(“A4”).Value = num1 / num2
Range(“A5”).Value = num1 ^ num2
Return range
End Function
In this example, the function ‘AddNumsRange’ returns a range of values containing the results of the mathematical operations performed on the two numbers.
To return a value from a function in VBA, you must assign the value to the function name.
For example, we can create the following function to divide two values and then return the result of the division:
Function DivideValues(x, y)
DivideValues = x / y
End Function
The name of this function is DivideValues, so to return a value from this function we must assign the result of x / y to a variable with the same name of DivideValues.
If your function involves If Else logic, you can assign the value to the function name multiple times.
For example, you can create the following function that returns “Cannot divide by zero” if you attempt to divide by zero or else simply return the result of the division:
Function DivideValues(x, y)
If y = 0 Then
DivideValues = "Cannot divide by zero"
Else
DivideValues = x / y
End If
End Function
The following example shows how to use this syntax in practice.
Example: How to Return Value from VBA Function
Suppose we would like to create a function in VBA to divide the value in cell A2 by the value in cell B2:
We can create the following function to do so:
Function DivideValues(x, y)
DivideValues = x / y
End Function
When we run this macro, we receive the following output:
The function returns a value of 5, which is the result of 50 divided by 10.
We could also create a function that uses If Else logic to first check if the value that we’re dividing by is not equal to zero:
Function DivideValues(x, y)
If y = 0 Then
DivideValues = "Cannot divide by zero"
Else
DivideValues = x / y
End If
End Function
If we change the value in cell B2 and then use this function to perform division, we’ll receive the following output:
Since we attempted to divide by zero, “Cannot divide by zero” is returned by the function.