How to return a result from VBA function?

As You may know, in general in programming to return a result from function there is Return statement. Even Visual Basic has it. Unfortunately there is no such thing in VBA. In this article I’m going to show You how to return a result from VBA function.

Let’s start with function

For the purpose of this article I’ve created functionModulo, which is very similar to the Mod function.

Function functionModulo(x As Integer, y As Integer) As Boolean

Dim divisionRest As Double, divisionRestStatus As Integer
Let divisionRest = x / y - Fix(x / y)

If divisionRest > 0 Then
    divisionRestStatus = 0
Else
    divisionRestStatus = 1
End If

If divisionRestStatus = 1 Then
    functionModulo = True
Else
    functionModulo = False
End If

End Function

This function allows You to check if there is a divisibility between two numbers. If there is a rest from division the function returns False, if there is no any – function returns True.

So to describe the work of this function, I was using return word. But not as a statement in code. To return the result in VBA function You need to use the function name itself and put it a value.

functionModulo = True

Unfortunately the function not stops there and exits. It just go further.

As close as possible

I decided to modify this function to make it as similar to Return statement schema as possible.

So first of all I cut it a little bit.

Function functionModulo(x As Integer, y As Integer) As Boolean

Dim divisionRest As Double
Let divisionRest = x / y - Fix(x / y)

If divisionRest > 0 Then
    functionModulo = False
Else
    functionModulo = True
End If

End Function

Then I wanted to have the same effect after returning the result from function as Return statement brings.

Function functionModulo(x As Integer, y As Integer) As Boolean

Dim divisionRest As Double
Let divisionRest = x / y - Fix(x / y)

If divisionRest > 0 Then functionModulo = False: Exit Function
If divisionRest = 0 Then functionModulo = True

End Function

That’s why after returning the value I added Exit Function.

If divisionRest > 0 Then functionModulo = False: Exit Function

I was almost done, but also figured out that we can only check for valid values. This means that I could set False value at the beginning as default. One conditional less in code. Cleaner the code.

Function functionModulo(x As Integer, y As Integer) As Boolean

functionModulo = False

Dim divisionRest As Double: Let divisionRest = x / y - Fix(x / y)

If divisionRest = 0 Then functionModulo = True

End Function

Having nothing after the conditional for True value I decided to erase Exit Function statement. In this case it is not needed, but in general it is really good approach to simulate Return statement as from other programming languages.

To sum up

To avoid unnecessary conditionals put the default value at the beginning of the function. You can also do that in the end of the code, only if You simulate the Return statement in VBA – pass the result in the function name and Exit Function just right after that line.