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.

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:

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.