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.
Download Practice Workbook
To practice, please download the Excel Workbook file from the link below.
Excel VBA Function to Return Multiple Values: 6 Ways
We 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 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.
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
- 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, 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 represents 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.
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.
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
- Function Result(ByVal val As Collection) As Boolean; Here creating the function, we declare val variable as Collection object and Boolean dictates the logical operation based 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 the all value 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
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.
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
- 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.
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.
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
- Function Result() As Variant to create a function with an array.
- Dim arrval(0 To 2) As Variant dictates the variable is arrval 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 pop-up message box separately.
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.
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
- 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 For loop to pick up the values and show writing MsgBox function.
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.
'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
- In the 1st sub-procedure, we declare all the data type. 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.
Frequently Asked Questions(FAQ)
- Can 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 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.
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.