Returning Multiple Values from a Function in VBA Using a UDT

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

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:

Option Compare Database
Option Explicit

Public Max As Variant
Public Min As Variant

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
    MaxMinColl.Add MaxMinObj
    
    Debug.Print MaxMinColl.Count
End Sub

Cover image created with Microsoft Designer