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.
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
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
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
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:
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
Here's how we would need to rewrite the example function and calling code from above using our new complex UDT class module,
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