Returning Values from VBA Functions

In Visual Basic for Applications (VBA), functions are used to perform specific tasks and return values. However, unlike some other programming languages, VBA does not use a traditional return statement to exit the function and pass back a value. Instead, you assign the desired return value to the name of the function itself.

Assigning Return Values

To return a value from a VBA function, you simply assign the value to the function name. For example:

Public Function Add(x As Integer, y As Integer) As Integer
    Add = x + y
End Function

In this example, the Add function takes two integer arguments and returns their sum by assigning it to the Add variable.

Using the Return Value

To use the return value of a VBA function, you can assign it to a variable or pass it directly to another function. For instance:

Dim result As Integer
result = Add(2, 3)

Alternatively, you can pass the return value directly to another function:

Debug.Print Add(2, 3) ' prints 5

Returning Object Types

When returning object types, such as ranges or worksheets, you must use the Set keyword. For example:

Public Function GetRange() As Range
    Set GetRange = Range("A1")
End Function

To use the return value of a function that returns an object type, you also need to use the Set keyword:

Dim targetRange As Range
Set targetRange = GetRange()

Exiting the Function

Assigning a return value to the function name does not automatically exit the function. If you want to terminate the execution of your function immediately after assigning a return value, you need to use the Exit Function statement. For example:

Public Function Test(justReturnOne As Boolean) As Integer
    If justReturnOne Then
        Test = 1
        Exit Function
    End If
    ' more code...
    Test = 2
End Function

In this example, if justReturnOne is True, the function will return 1 and exit immediately. Otherwise, it will continue executing and eventually return 2.

Using the Return Variable

The return variable can be used like any other variable within the function. You can assign a value to it, modify it, or even pass it as an argument to another function. For instance:

Public Function Test(x As Integer) As Integer
    Test = x ' set the return value
    If Test <> 1 Then ' test the currently set return value
        Test = 0 ' reset the return value to a new value
    End If
End Function

By understanding how to return values from VBA functions and using the return variable effectively, you can write more efficient and flexible code.