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

# Returning Multiple Values from a Function in VBA Using a UDT
- URL: https://nolongerset.com/return-multiple-values-udt/
- Published: 2023-02-25T04:12:25.000Z
- Updated: 2026-05-08T12:49:07.000Z
- Description: You can return multiple values from a VBA procedure by using a simple User Defined Type (UDT). Though, some situations require a "complex" UDT.
- 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 `Function`

There are a few ways you can do this, but for this article I want to focus on using a User Defined Type (UDT).

## A Simple Example

The `GetMaxMin` Function below returns multiple values via a simple User Defined Type that we declare in the header section of the module:

```vba
Public Type typMaxMin
    Max As Variant
    Min As Variant
End Type

Function GetMaxMin(Numbers As Variant) As typMaxMin
    ' Find the maximum and minimum values in an array of numbers
    GetMaxMin.Max = Numbers(0)
    GetMaxMin.Min = Numbers(0)
    
    Dim i As Long
    For i = 1 To UBound(Numbers)
        If Numbers(i) > GetMaxMin.Max Then
            GetMaxMin.Max = Numbers(i)
        End If
        If Numbers(i) < GetMaxMin.Min Then
            GetMaxMin.Min = Numbers(i)
        End If
    Next i
End Function
```

In this example, the `GetMaxMin` Function takes an array of numbers as input and returns a value of type `typMaxMin`. This Function is designed to find the maximum and minimum values in the input array, and store them in a `typMaxMin` variable.

Here's an example of how you might use this Function in your code:

```vba
Sub TestGetMaxMin()
    Dim Numbers As Variant
    Dim MaxMin As typMaxMin
    
    ' Define an array of numbers
    Numbers = Array(5, 3, 8, 1, 9, 2, 7)
    
    ' Call the GetMaxMin Function to find the maximum and minimum values
    MaxMin = GetMaxMin(Numbers)
    
    ' Display the results
    MsgBox "The maximum value is " & MaxMin.Max & _
      " and the minimum value is " & MaxMin.Min
End Sub
```

First, we declare a variable `MaxMin` as our user-defined type `typMaxMin`. Next, we assign the result of the `GetMaxMin` function to this variable. Finally, to access the values that comprise the `MaxMin` variable, we use a dot (`.`) between the variable name and the type member.

## Alternative to UDTs: Passing By Reference

An alternative to this approach is to pass arguments by reference. I cover that approach in this article: [Using ByRef to Return Multiple Values from a Sub (or Function)](https://nolongerset.com/return-multiple-values-byref/).

[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.![](https://nolongerset.com/favicon.png)No Longer SetMike Wolfe![](https://nolongerset.com/content/images/2023/02/Designer--15-.jpeg)](https://nolongerset.com/return-multiple-values-byref/)

## Disadvantages of UDTs

Unfortunately, there are some major restrictions when it comes to simple UDTs:

- You cannot add them to a Collection or Dictionary
- You cannot use them as Public members of object modules
- They cannot contain any code

For me, the restriction that tends to trip me up the most is the first one. 

## Workaround: The "Complex" UDT

If I'm using a simple UDT and I run into one of the above showstoppers, the easiest fix is to transform the "simple" UDT into a "complex" UDT. I'm not sure how widely or consistently used the term "complex UDT" is. I use it to describe a very simple class module with a handful of public variables and no code.

For example, if I needed to add my `typMaxMin` variables to a Collection or Dictionary, I would create a complementary class module named `udtMaxMin` with the following code:

```vba
Option Compare Database
Option Explicit

Public Max As Variant
Public Min As Variant
```

The `udtMaxMin` class module, an example of a "complex" User Defined Type.

I use "typ" as a prefix for simple UDTs and "udt" as a prefix for complex UDTs.

Since complex UDTs are full-blown VBA objects, you need to instantiate them using the `New` keyword and assign them to object variables using the `Set` keyword.

Here's how we would need to rewrite the example function and calling code from above using our new complex UDT class module, `udtMaxMin`.

```vba
Function GetMaxMinObj(Numbers As Variant) As udtMaxMin
    'Instantiate the udtMaxMin object
    Set GetMaxMinObj = New udtMaxMin
    
    ' Find the maximum and minimum values in an array of numbers
    GetMaxMinObj.Max = Numbers(0)
    GetMaxMinObj.Min = Numbers(0)
    
    Dim i As Long
    For i = 1 To UBound(Numbers)
        If Numbers(i) > GetMaxMinObj.Max Then
            GetMaxMinObj.Max = Numbers(i)
        End If
        If Numbers(i) < GetMaxMinObj.Min Then
            GetMaxMinObj.Min = Numbers(i)
        End If
    Next i
End Function

Sub TestGetMaxMinObj()
    Dim Numbers As Variant
    Dim MaxMinObj As udtMaxMin
    
    ' Define an array of numbers
    Numbers = Array(5, 3, 8, 1, 9, 2, 7)
    
    ' Call the GetMaxMin Function to find the maximum and minimum values;
    '	note the use of the Set keyword here
    Set MaxMinObj = GetMaxMinObj(Numbers)
    
    ' Display the results
    MsgBox "The maximum value is " & MaxMinObj.Max & _
      " and the minimum value is " & MaxMinObj.Min
    
    ' You can't add a simple UDT to a Collection object,
    '	so the code below would not be possible with typMaxMin
    Dim MaxMinColl As Collection
    Set MaxMinColl = New Collection
    MaxMinColl.Add MaxMinObj
    
    Debug.Print MaxMinColl.Count
End Sub
```

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