Excel VBA Function to Return Multiple Values

Get FREE Advanced Excel Exercises with Solutions!

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.

An overview image of Excel vba function 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

Getting multiple values using ByRef arguments in Excel VBA

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

🔍Code Breakdown

  • Function Result(ByRef V1 As Integer, ByRef V2 As String, ByRef V3 As Double) dictates the construction of a function where passing argument By Ref is a read-only variable that can’t change its value inside an Excel VBA function in the 1st sub-procedure.
  • Then, the assigned value in the V1, V2, and V3 and Boolean value TRUE indicate the successful execution.
  • Next, Dim int_Val As Integer, Dim str_Val As String Dim dbl_Val As Double represent the data value type of a custom function argument in the 2nd sub-procedure.
  • If Result(int_Val, str_Val, dbl_Val) delineates that the function is TRUE then show the result, concatenated the values, in the message box once we type the MsgBox function.

`

Note: If you don’t specify a procedure argument or By val or ByRef for a function, ByRef is the default passing mechanism.

Read More: How to Execute VBA Function Procedure in Excel


2. Return Two or More Values Using Collection Object

Getting numerous values using the 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

🔍Code Breakdown

  • Function Result(ByVal val As Collection) As Boolean; Here creating the function, we declare val variable as a Collection object, and Boolean dictates the logical operation based on TRUE or FALSE.
  • val.Add means adding different items to the variable.
  • In the 2nd Sub-Procedure, we declare val as the new collection that represents the set of items.
  • If Result(val) calls all values assigned in the val variable once the condition is TRUE.
  • For a = 1 To val.Count is the loop where we find out the number of outcomes.
  • MsgBox “Value ” & a & “: ” & val(a) shows the value for each item.

3. Return Numerous Values Using Dictionary Object

Getting several values using the 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

🔍Code Breakdown

  • Function Result(ByVal dictval As Object) As Boolean; Here creating the function, we declare dictval variable as dictionary object and Boolean dictates the logical operation based TRUE or FALSE.
  • dictval(“Val1”)  is used to assign values.
  • In the 2nd sub-procedure, Dim dictval As Object to declare dictval as a dictionary object. The key variable is also declared as a variant.
  • Set dictval = CreateObject(“Scripting.Dictionary”) dictates to hold the values from the code.
  • If Result(dictval) calls up all the values once the logic is TRUE.
  • For Each key In dictval.Keys; Here, the For loop picks up all the values.
  • Finally, MsgBox shows all the values in a pop-up message box separately.
Note: While using a dictionary object, the object’s index starts at 0.

4. Return Several Values Using an Array

Getting a couple of 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

🔍Code Breakdown

  • Function Result() As Variant to create a function with an array.
  • Dim arrval(0 To 2) As Variant dictates the variable is arrival with an index of 0 to 2.
  • arrval(0)=10 delineates the value for the 1st array.
  • In the 2nd sub-procedure, we again declare the variable of array typing Dim arrval() As Variant
  • arrval = Result() delineates the picking up of the values.
  • For i = 0 To 2; For loop pick up all the values from the array.
  • Finally, MsgBox “Value ” & i + 1 & “: ” & arrval(i) display 3 outputs with a pop-up message box separately.
Note: The index of the array generally starts from 0.

5. Return a Variety of Values From CSV String

Getting a variety of values using 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

🔍Code Breakdown

  • In the 1st sub-procedure, texts = “10,Exceldemy,3.1416” dictates the assign of values in the texts variable. Also, to declare a variable as string type Dim texts as String.
  • In the 2nd sub-procedure, we declare str and arr as string and variant respectively.
  • str = Result() to pick up the values.
  • arr = Split(str, “,”) to separate the string values based on commas.
  • For i = LBound(arr) To UBound(arr); Here we use the For loop to pick up the values and show the writing MsgBox function.
Note: You must split the CSV to conduct with an array.

6. Return Several Different Values with User-Defined Type

Getting several different values using 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

🔍Code Breakdown

  • In the 1st sub-procedure, we declare all the data types. Type values represent the User Defined Type.
  • Write Function Getvalue() As values to create a function named Getvalue and declare the value type values. Also, declare val variable as values in the 2nd sub-procedure.
  • val.Val1=10 dictates to assign value for variable val.
  • Getvalue = val means all the value of val is assigned in the Getvalue function.
  • Again val = Getvalue() to assign the value in val from the Getvalue function in the 3rd sub-procedure.
  • For i = 1 To 3; For loop picks up all 3 values and shows it with pop-up message boxes once we use the MsgBox function.
Note: You must declare a user-defined type in a module.

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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
MD Tanvir Rahman
MD Tanvir Rahman

MD Tanvir Rahman, BUET graduate in Naval Architecture and Marine Engineering, dedicated over a year to the ExcelDemy project. He is an Excel and VBA Content Developer. Having authored 50+ insightful articles, he actively updates and improves over 80 articles, reflecting his commitment to accuracy and currency, managing day-to-day operations, and analyzing and developing Excel and VBA tutorials. His broad interests encompass Data Analysis, Advanced Excel, VBA Macro, Excel Templates, Excel Automation, and Excel Power Query, showcasing a... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo