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
This concept is easiest to demonstrate with an example.
A Simple Example
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,
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
Min parameters will be updated with the results, and then the Sub will display a message box showing the values of
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
- Explicitly by value using the keyword
- Implicitly by reference using no keyword at all
Many developers that I respect insist on always using the
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
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:
ByRefif I intend for my changes to be used outside of the procedure
ByValif 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
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.
- Portions of this article's body generated with the help of ChatGPT
- One or more code samples generated with the help of ChatGPT
UPDATE [2023-02-24]: Add section with link to article on using UDTs to return multiple values from a function in VBA.