How to Return a Value from VBA Function (With Examples)

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: