How to Return Values from a Sub in VBA
You can "return" values from a Sub via a parameter passed by reference. What does that look like and when should you use it? Let's explore.
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
Subvia 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.
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
The "ByRef" Keyword is Optional
By default, all parameters are passed by reference in VBA, so the
ByRef keyword is not necessary in the
Some developers prefer to explicitly use
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
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:
- I'm lazy
- It boosts my code's Signal vs. Noise ratio
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 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 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 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
SortStringArrayis 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
Forloops to iterate over the array elements. The outer loop runs
ifrom the lower bound of the array to the upper bound minus one, and the inner loop runs
i + 1to the upper bound of the array.
In each iteration of the outer loop, the
minIndexvariable is initialized to the current iteration position
i. The inner loop compares each element
StringArray(j)with the current minimum
minIndexwith the index of the minimum element.
minIndexis 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
iis swapped with the minimum element using a temporary variable
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 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
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.
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.
- 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