Excel VBA Function to Return Multiple Values

Generally, a function can output a single value at a time while using the Excel VBA function. Any type of data can be declared that is supported by Excel VBA such as a number, string, date, boolean, or object. If you are required to obtain multiple values from an Excel function, By Reference argument, Collection, Dictionary object array, or a user-defined type can be some way to get a set of values altogether and return them as one entity. In this article, we will discuss several ways for Excel VBA function(s) to return multiple values.

As you can see in the above image, using the ByRef argument, we obtain three results in a single pop-up message box while using the Excel VBA function. In addition, we are planning to discuss several ways to return multiple values by using the Excel VBA function.

Excel VBA Function to Return Multiple Values: 6 Ways

In this article are going to expound on the use of ByRef argument, Collection object, Dictionary object, Array, CSV string, and User Defined Type (UDT) while using Excel VBA to return multiple values. To construct these approaches, we assigned three values in the separate variable including integer, string, and double values.

For your convenience, to run VBA code you need to open a code window in Excel.

1. Return Multiple Values through Reference by Passing Argument

The most frequent method for a function to return multiple values is likely passing parameters by reference. The code that executes within the function has the ability to modify the value of the variable. The modified value can be obtained by utilizing the same variable name in the function’s calling code even if the function doesn’t explicitly return it.

We are likely to show our returned value in a message pop-up box to test our function. In the 1st sub-procedure, we assigned a value for each variable. In the 2nd sub-procedure, we call the values from the 1st sub-procedure via function. Therefore, we obtain the result containing V1, V2, and V3 in a pop-up message box.

🎯Code:

Function Result(ByRef V1 As Integer, ByRef V2 As String, ByRef V3 As Double) As Boolean
'Assigning values to the variables V1, V2 and V3
V1 = 1
V2 = "Exceldemy"
V3 = 3.1416
'To indicate successful execution, Return TRUE
Result = True
End Function
Sub Return_values_byRef()
Dim int_Val As Integer
Dim str_Val As String
Dim dbl_Val As Double
'Call the custom created function and pass variables by reference
If Result(int_Val, str_Val, dbl_Val) Then
'Show the values in a pop-up message box.
MsgBox "Value 1: " & int_Val & vbCrLf & "Value 2: " & str_Val & vbCrLf & "Value 3: " & dbl_Val
End If
End Sub

Read More: How to Execute VBA Function Procedure in Excel

2. Return Two or More Values Using Collection Object

An ordered group of elements can be referred to as a Collection object by using an index to describe it. A collection’s items (also known as elements) are not required to have the same data type.

In the following code, Creating a function, we assigned values for 3 items in the 1st sub-procedure. Calling the custom-created function with a function, we obtain 3 individual pop-up message boxes as our outcome.

🎯Code:

Function Result(ByVal val As Collection) As Boolean
'Adding and assigning values to the variable 'val'
val.Add 10
val.Add "Exceldemy"
val.Add 3.14
'To indicate successful execution, Return TRUE
Result = True
End Function
Sub Return_values_byCollection()
Dim val As New Collection
Dim a As Integer
'Call the custom created function and pass the collection by reference
If Result(val) Then
For a = 1 To val.Count
'Here, we get 3 pop-up message box as val.count=3
MsgBox "Value " & a & ": " & val(a)
Next a
End If
End Sub

3. Return Numerous Values Using Dictionary Object

A bunch of key-value pairs are stored in the Dictionary object, which is a data structure. A distinct String value serves as the key. Any type of data might be the item. The first entry in a dictionary has an index of 0 since the object’s index starts at 0.

In the 1st Sub-Procedure, we assign 3 key values in a dictionary object. Calling up the function, we get 3 pop-up message boxes regarding output once we execute the code of the 2nd Sub-Procedure.

🎯Code:

Function Result(ByVal dictval As Object) As Boolean
'Assigning values to the dictionary
dictval("Val1") = 10
dictval("Val2") = "Exceldemy"
dictval("Val3") = 3.14
'To indicate successful execution, Return TRUE
Result = True
End Function
Sub Return_values_by_dictionary()
Dim dictval As Object
Dim key As Variant
'Creating a dictionary object and call the custom created function with it
Set dictval = CreateObject("Scripting.Dictionary")
If Result(dictval) Then
For Each key In dictval.Keys
'Here, we get 3 pop-up message box
MsgBox key & ": " & dictval(key)
Next key
End If
End Sub

4. Return Several Values Using an Array

A variable with many values retained within array elements is known as an array. Using integer index numbers starting from 0, these values can be stored in and accessed from array elements. Each piece can be processed independently, or all items can be processed automatically at once using a loop.

Here, in the following image, we assigned 3 values as a set of elements in an array. Defining a function in the 1st Sub-Procedure, calling it to the 2nd Sub-Procedure, and executing it with a loop, we achieve 3 pop-up message boxes containing assigned values within the array.

🎯Code:

Function Result() As Variant
'code to process and assign values to the array
Dim arrval(0 To 2) As Variant
arrval(0) = 10
arrval(1) = "Exceldemy"
arrval(2) = 3.14
'Result calls the array values
Result = arrval
End Function
Sub Return_values_using_array()
Dim arrval() As Variant
Dim i As Integer
'Call the custom created function to get the array values
arrval = Result()
For i = 0 To 2
'Here, we get 3 pop-up message box regarding output
MsgBox "Value " & i + 1 & ": " & arrval(i)
Next i
End Sub

5. Return a Variety of Values From CSV String

Because strings are slightly simple to process and are often tiny in size, comma-separated values (CSV) strings can be very helpful and significantly simplify programming logic. For processing, we typically convert CSV strings into arrays since they can be toggled over employing the loops.

In the 1st sub-procedure, Creating a custom function, we insert a value separated by commas that represent CSV. Later, in the 2nd sub-procedure, calling the function, we split the values based on commas and with a For loop we are likely to generate output. Finally, with 3 pop-up message boxes, we get our desired outcome.

🎯Code:

Function Result() As String
Dim texts As String
'Assigning values in the variable separated by commas.
texts = "10,Exceldemy,3.1416"
Result = texts
End Function
Sub Return_values_using_CSV()
Dim str As String
Dim arr As Variant
str = Result()
'Split the string to get an array of 3 elements.
arr = Split(str, ",")
'exploring values from the lowest to highest value.
For i = LBound(arr) To UBound(arr)
'Here, we get 3 pop-up message box regarding output.
MsgBox "Value " & i + 1 & ": " & arr(i)
Next i
End Sub

6. Return Several Different Values with User-Defined Type

The User-defined type can be used to store similar data in one variable. Keep in mind that a module must define a user-defined type. It is a static data structure, often known as a struct or record in other computer languages.

In the 1st sub-procedure, we declared the user-defined data type and its value type. Then, we create a custom function to operate in the 2nd sub-procedure defining various values. After that, calling up the function and executing the code of the 3rd sub-procedure, we obtain 3 pop-up messages regarding the outcome.

🎯Code:

'Defining user-defined type.
Type values
Val1 As Integer
val2 As String
val3 As Long
End Type
' Creating a function that outputs a user-defined type
Function Getvalue() As values
Dim val As values
'Assiging values to each type
val.Val1 = 10
val.val2 = "Exceldemy"
val.val3 = 3.1416
Getvalue = val
End Function
Sub Return_values_using_User_defined_type()
'Define a user-defined type
Dim val As values
'val variable holds three values.
val = Getvalue()
For i = 1 To 3
'Here, we get 3 pop-up message box regarding output.
MsgBox "Value " & i & ": " & Choose(i, val.Val1, val.val2, val.val3)
Next i
End Sub

Read More: How to Use VBA User Defined Function

Frequently Asked Questions(FAQ)

• Can the Excel VBA function return multiple values?

Yes, by using the By Reference argument, Collection, and Dictionary object, User Defined Type, Array, etc. a user can return multiple values while using multiple values.

• How many values can a function return in VBA?

A function in VBA can only return one value at a time. Any data type VBA supports, including an integer, text, boolean, or object, may be used as the value.

• Can you use return twice in an Excel VBA function?

No, you can’t. You are allowed to return a single value at a time. However, using the By Reference argument, Collection object, User Defined Type, or array you can return two or multiple values at a time.

📝 Takeaways from This Article

• Obtain multiple values using By Reference arguments in Excel VBA.
• Get numerous values implementing the Collection object.
• Achieve several values utilizing the Dictionary object.
• Have a variety of values employing an array.
• Return two or multiple values incorporating User Defined Type.
• Obtain a variety of values from a CSV string.

Download Practice Workbook

To practice, please download the Excel Workbook file from the link below.

Conclusion

We briefly construe 6 different ways for the Excel VBA function to return multiple values. I hope you enjoyed your learning and will be able to return multiple values with the Excel VBA function. Any suggestions including queries are appreciated. Don’t hesitate to leave your thoughts in the comment section.

Related Articles

• How to Return a Value in VBA Function
• How to Make VBA Function with Arguments in Excel
• How to Use VBA Input Function in Excel
• VBA Sub Vs Function in Excel
• How to Create and Use ColorFunction in Excel
• Difference Between Subroutine and Function in Excel VBA
• How to Create Custom Function in Excel VBA