# Returning Multiple Values from a Function in VBA Using a UDT

You can return multiple values from a VBA procedure by using a simple User Defined Type (UDT). Though, some situations require a "complex" UDT.

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:

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

``````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).

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:

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

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