> ## Content Index
> Fetch the complete content index at: https://nolongerset.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# How to Return Values from a Sub in VBA
- URL: https://nolongerset.com/return-values-from-a-sub/
- Published: 2023-02-11T04:59:00.000Z
- Updated: 2026-05-08T12:51:50.000Z
- Description: 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.
- Author: Mike Wolfe
- Tags: Subs vs. Functions, #Import 2026-05-20 02:59

*This article is one in a [series on Subs vs. Functions](https://nolongerset.com/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:

```vba
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](https://nolongerset.com/vba-squeezebox/)** 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](https://nolongerset.com/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](https://nolongerset.com/runtime-errors) and [logic errors](https://nolongerset.com/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:

```vba
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:

```vba
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:

```vba
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:

```vba
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](https://nolongerset.com/chatgpt-nls/)
- One or more code samples generated with the help of [ChatGPT](https://nolongerset.com/chatgpt-nls/)

*Cover image created with [Microsoft Designer](https://designer.microsoft.com/)*