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.
Additional Resources
The following tutorials explain how to perform other common tasks in VBA:
How to Comment a Block of Code in VBA
How to Check if File Exists Using VBA
How to Create Folders Using VBA