> ## 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.

# Using ByRef to Return Multiple Values from a Sub (or Function)
- URL: https://nolongerset.com/return-multiple-values-byref/
- Published: 2023-02-24T04:33:33.000Z
- Updated: 2026-05-08T12:51:28.000Z
- Description: You can "return" multiple values from a VBA procedure by passing your arguments by reference.
- 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 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:

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

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

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2023/02/image-39.png)

## 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](https://nolongerset.com/return-multiple-values-udt/).

[Returning Multiple Values from a Function in VBA Using a UDTYou can return multiple values from a VBA procedure by using a simple User Defined Type (UDT). Though, some situations require a “complex” UDT.![](https://nolongerset.com/favicon.png)No Longer SetMike Wolfe![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2023/02/Designer--18-.jpeg)](https://nolongerset.com/return-multiple-values-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](https://peps.python.org/pep-0020/)–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 [](https://learn.microsoft.com/en-us/office/vba/language/how-to/user-defined-data-type)[User-Defined Type ](https://learn.microsoft.com/en-us/office/vba/language/how-to/user-defined-data-type)(UDT)
- Use a Function that returns a data structure with multiple items (e.g., a [Collection](https://nolongerset.com/vba-collections-aka-linked-lists/), [Dictionary](https://nolongerset.com/vba-dictionaries-aka-hash-tables/), or [Array](https://learn.microsoft.com/en-us/office/vba/language/glossary/vbe-glossary#array))
- Change the value of global variables ([caveat programmator](https://nolongerset.com/overuse-of-global-state/))
- Change the value of controls on a [global form](https://nolongerset.com/the-global-form/)
- Use a method in a [class module](https://nolongerset.com/class-programming-intro/) 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

[One if ByRef, and Two if ByValArguments in VBA are passed by reference by default. What does that mean? And what are the differences between passing by reference and by value?![](https://nolongerset.com/favicon.png)No Longer SetMike Wolfe![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2020/09/lantern-1864627_1920.jpg)](https://nolongerset.com/one-if-by-val-two-if-by-ref/)

[Great Intro to Class Programming in VBAFormer Access MVP Ben Clothier’s primer on class programming in VBA should be required reading for all Access developers.![](https://nolongerset.com/favicon.png)No Longer SetMike Wolfe![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2022/04/bullying-2775272_1920.jpg)](https://nolongerset.com/class-programming-intro/)

##### 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/); Photo by [Anne Nygård](https://unsplash.com/@polarmermaid?utm%5Fsource=unsplash&utm%5Fmedium=referral&utm%5Fcontent=creditCopyText) on [Unsplash](https://unsplash.com/photos/nMXPupuPeFI?utm%5Fsource=unsplash&utm%5Fmedium=referral&utm%5Fcontent=creditCopyText)

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