How to Return Values from a Sub in VBA

Mike Wolfe -

This article is one in a series on Subs vs. Functions in VBA.

One of the "additional facts" from my Subs vs. Functions article was this:

You can "return" values from a Sub via a parameter passed by reference

Passing Parameters "By Reference"

In a traditional programming sense, Subs are procedures that do not return values, while Functions return values.

A parameter passed by reference allows you to modify its value within a Sub, and the changes made to the parameter are reflected outside the Sub. Essentially, you are "returning" values from the Sub through the parameter.

For example:

Sub MultiplyByTwo(ByRef Num As Integer) 
    Num = Num * 2 
End Sub

Sub Main() 
    Dim x As Integer 
    x = 5 
    MultiplyByTwo x 

    Debug.Print x 'outputs 10 
End Sub

In this example, the Main Sub calls the MultiplyByTwo Sub, passing the variable x as an argument. The MultiplyByTwo Sub takes this argument and multiplies its value by 2, which modifies the value of x. The changes made to x within the MultiplyByTwo Sub are reflected in the Main Sub, as shown by the output of the Debug.Print statement.

The "ByRef" Keyword is Optional

By default, all parameters are passed by reference in VBA, so the ByRef keyword is not necessary in the MultiplyByTwo declaration.  

Some developers prefer to explicitly use ByRef or ByVal with every parameter.  For example, the Rubberduck VBA add-in has a dedicated code analysis check that will flag parameters that are missing those keywords.

Another common convention is to prefix or suffix parameters with Out or InOut based on how the Sub will treat the parameter.  For example, the above procedure declaration might be rewritten as Sub MultipyByTwo(InOut_Num As Integer).

My personal preference is to include the ByRef keyword if and only if I plan on changing the value of the passed argument.  I rarely change the value of a parameter passed by reference, so there are very few instances of the ByRef keyword in my code.

(Side rant: This is an actual example of "the exception that proves the rule."  Most people use that phrase when referring to a rare event.  Instead, it refers to an implied rule as a result of an explicit exception.  For example, the explicit rule, "No swimming after dark," implies that you MAY swim during daylight hours.  And I think it's safe to say neither darkness nor daylight are particularly rare events.)

I prefer my convention for two reasons:

Advantages and Disadvantages

While passing parameters by reference can occasionally be a useful method for "returning" values from a Sub, it also has some drawbacks.

The main advantage of this approach is that it is an easy way to return multiple values from a Sub.  Another advantage is that it can cut down on repetition in your calling code if you are modifying a single variable via multiple procedures.

However, passing parameters by reference can lead to side effects, where changes made to the parameter within the Sub unexpectedly affect other parts of the code. This can make your code more difficult to debug and maintain. Additionally, passing parameters by reference can lead to unexpected behavior if the caller is not aware of the changes made to the parameter.  This can lead to runtime errors and logic errors that can be particularly difficult to debug.

In conclusion, while you can "return" values from a Sub via a parameter passed by reference, it is important to understand the advantages and disadvantages of this approach before deciding to use it. In general, it is better to use Functions for returning values and Subs for procedures that do not return values, but in certain situations, passing parameters by reference can be a useful tool for returning values from a Sub.

Examples of When it Makes Sense to Use ByRef to Return Values from Subs

Swapping the Value of Two Variables

This is something you might need to do when creating a sorting algorithm.  Passing both values by reference to a Sub is much cleaner than the equivalent approach using a Function:

Sub Version:

Sub SwapValues(ByRef x As Variant, ByRef y As Variant)
    Dim temp As Variant
    temp = x
    x = y
    y = temp
End Sub

Sub Main()
    Dim A As Integer
    Dim B As Integer

    A = 5
    B = 10

    SwapValues A, B

    Debug.Print A, B
End Sub

Function Version:

Function SwapValues(ByVal x As Variant, ByVal y As Variant) As Variant()
    Dim temp As Variant
    temp = x
    x = y
    y = temp
    
    SwapValues = Array(x, y)
End Function

Sub Main()
    Dim A As Integer
    Dim B As Integer

    A = 5
    B = 10

    Dim Result As Variant
    Result = SwapValues(A, B)
    A = Result(0)
    B = Result(1)

    Debug.Print A, B
End Sub

Sorting an Array In-place

The example below removes duplicates from an array, but the same logic would apply to a procedure to sort an array, reverse an array, apply a mathematical operation to every element in the array, etc.

Sub Version:

Sub SortStringArray(ByRef StringArray() As String)
    Dim i As Long
    Dim j As Long
    Dim minIndex As Long
    Dim temp As String
    
    For i = LBound(StringArray) To UBound(StringArray) - 1
        minIndex = i
        For j = i + 1 To UBound(StringArray)
            If StringArray(j) < StringArray(minIndex) Then
                minIndex = j
            End If
        Next j
        
        If minIndex <> i Then
            temp = StringArray(i)
            StringArray(i) = StringArray(minIndex)
            StringArray(minIndex) = temp
        End If
    Next i
End Sub

Sub TestSortStringArray()
    Dim TestArray(0 To 4) As String
    TestArray(0) = "dog"
    TestArray(1) = "cat"
    TestArray(2) = "bird"
    TestArray(3) = "ant"
    TestArray(4) = "mouse"
    
    Debug.Print Join(TestArray, ",")
    SortStringArray TestArray
    Debug.Print Join(TestArray, ",")
End Sub
How it Works: Selection Sort
The procedure SortStringArray is an implementation of the selection sort algorithm. It sorts an array of strings in place, meaning that the original array is modified.

The algorithm iterates over the elements in the array, and for each iteration, it finds the minimum element in the subarray starting from the current iteration position and moves it to its proper position in the sorted portion of the array. This process continues until the whole array is sorted.

The procedure uses two nested For loops to iterate over the array elements. The outer loop runs i from the lower bound of the array to the upper bound minus one, and the inner loop runs j from i + 1 to the upper bound of the array.

In each iteration of the outer loop, the minIndex variable is initialized to the current iteration position i. The inner loop compares each element StringArray(j) with the current minimum StringArray(minIndex) and updates minIndex with the index of the minimum element.

If minIndex is different from i, the current minimum element is not at its proper position in the sorted portion of the array. In this case, the element at position i is swapped with the minimum element using a temporary variable temp.

This process continues until the whole array is sorted. The end result is a sorted array of strings, where the elements are ordered in ascending order.

Function Version:

Function fnSortStringArray(StringArray() As String) As String()
    Dim SortedArray() As String
    ReDim SortedArray(LBound(StringArray) To UBound(StringArray))

    Dim i As Long
    For i = LBound(StringArray) To UBound(StringArray)
        SortedArray(i) = StringArray(i)
    Next i

    For i = LBound(SortedArray) To UBound(SortedArray) - 1
        Dim minIndex As Long
        minIndex = i
        Dim j As Long
        For j = i + 1 To UBound(SortedArray)
            If SortedArray(j) < SortedArray(minIndex) Then
                minIndex = j
            End If
        Next j
    
        If minIndex <> i Then
            Dim temp As String
            temp = SortedArray(i)
            SortedArray(i) = SortedArray(minIndex)
            SortedArray(minIndex) = temp
        End If
    Next i

    fnSortStringArray = SortedArray

End Function

Sub TestSortStringArray()
    Dim TestArray(0 To 4) As String
    TestArray(0) = "dog"
    TestArray(1) = "cat"
    TestArray(2) = "bird"
    TestArray(3) = "ant"
    TestArray(4) = "mouse"
    
    Debug.Print Join(TestArray, ",")
    
    Debug.Print Join(fnSortStringArray(TestArray), ",")
End Sub

Memory considerations

When you sort an array in place using a Sub, you modify the original array directly in memory. This means that the original array is overwritten and no additional memory is consumed.

On the other hand, when you sort an array by returning a new sorted array, as in the Function version, a new array is created in memory to store the sorted results. This approach consumes additional memory as you are now using two arrays instead of one.

In most cases, the amount of memory consumed by the additional array is relatively small and is unlikely to cause significant performance issues. However, if you are working with very large arrays, you may want to consider the memory impact of the approach you choose.

Final Thoughts

Use Functions when you want to return a value, Subs when you don't.

In some cases, though, modifying parameters passed by reference to a Sub is better than returning a copy from a Function.

If modifying parameters, make it clear in your code.

Acknowledgements
  • Portions of this article's body generated with the help of ChatGPT
  • One or more code samples generated with the help of ChatGPT

Cover image created with Microsoft Designer