Subs vs. Functions

Using ByRef to Return Multiple Values from a Sub (or Function)

You can "return" multiple values from a VBA procedure by passing your arguments by reference.

Mike Wolfe

Feb 23, 2023 • 5 min read

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 multiple values from a Sub

This concept is easiest to demonstrate with an example.

A Simple Example

The GetMaxMin Sub below returns multiple values using parameters passed by reference:

Sub GetMaxMin(Numbers As Variant, ByRef Max As Variant, ByRef Min As Variant)
    ' Find the maximum and minimum values in an array of numbers
    Max = Numbers(0)
    Min = Numbers(0)
    
    Dim i As Long
    For i = 1 To UBound(Numbers)
        If Numbers(i) > Max Then
            Max = Numbers(i)
        End If
        If Numbers(i) < Min Then
            Min = Numbers(i)
        End If
    Next i
End Sub

In this example, the GetMaxMin Sub takes an array of numbers as input and two output parameters, Max and Min. These output parameters are passed by reference using the ByRef keyword, which means that any changes made to them inside the Sub will also be reflected outside the Sub.

Here's an example of how you might use this Sub in your code:

Sub TestGetMaxMin()
    Dim Numbers As Variant
    Dim Max As Variant
    Dim Min As Variant
    
    ' Define an array of numbers
    Numbers = Array(5, 3, 8, 1, 9, 2, 7)
    
    ' Call the GetMaxMin Sub to find the maximum and minimum values
    GetMaxMin Numbers, Max, Min
    
    ' Display the results
    MsgBox "The maximum value is " & Max & _
      " and the minimum value is " & Min
End Sub

When you run the TestGetMaxMin Sub, it will call the GetMaxMin Sub to find the maximum and minimum values in the Numbers array. The Max and Min parameters will be updated with the results, and then the Sub will display a message box showing the values of Max and Min:

Alternative to Passing By Reference: UDTs

An alternative to this approach is to return a user defined type (UDT) from a Function. I cover that approach in this article: Returning Multiple Values from a Function in VBA Using a UDT.

ByRef vs. ByVal vs. Implicit ByRef

There are three ways to pass arguments to a procedure (i.e., a Sub or Function) in VBA:

Explicitly by reference using the keyword ByRef
Explicitly by value using the keyword ByVal
Implicitly by reference using no keyword at all

Many developers that I respect insist on always using the ByRef and ByVal keywords. This is in line with the Zen of Python–a set of programming principles with which I generally agree–that includes the mantra,

Explicit is better than implicit.

I use a different approach, which goes back to my personal programming philosophy, which amounts to four words:

More signal. Less noise.

For me, adding ByRef or ByVal keywords on every parameter adds noise to the code. What's worse, the two keywords are so visually similar that if you pass 99% of your parameters ByRef that you won't notice the 1% that get passed ByVal. That said, most people who prefer to be explicit also prefer to pass most variables by value. And if you want to pass by value, you've got no choice but to be explicit about it.

For me, I pass 99.99% of my parameters implicitly by reference. However, I make it a policy to never change the value of parameters passed that way.

On those rare occasions, that I do want to change the value of a passed parameter, I always include the appropriate keyword:

ByRef if I intend for my changes to be used outside of the procedure
ByVal if I intend to change the parameter within the procedure but not affect the parameter passed by the calling code

With this approach, whenever I see either the ByRef or ByVal keyword in my code, it's a signal that I am doing something unusual.

Alternative Ways to Return Multiple Values

Of course, passing by reference is not the only way to return multiple values from a procedure. Here is a list of some alternatives:

Use a Function that returns a User-Defined Type (UDT)
Use a Function that returns a data structure with multiple items (e.g., a Collection, Dictionary, or Array)
Change the value of global variables (caveat programmator)
Change the value of controls on a global form
Use a method in a class module to change the value of class variables

The longer I program in VBA, the more I find myself turning to the last option in the list above.

If I find myself wanting to return multiple values from a single procedure, that gets my spidey senses tingling. At that point, I step back and ask myself, "Should this procedure be a method in a class module?" More often than not, the answer to that question is, Yes.

Additional reading

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; Photo by Anne Nygård on Unsplash

UPDATE [2023-02-24]: Add section with link to article on using UDTs to return multiple values from a function in VBA.