Part 2 - Using array, CSV, and user-defined type

This is part 2 of the article series - 8 ways to return multiple values from a VBA function. In this article, we are going to look at how to return multiple values by using array, CSV, and By reference argument.

Here are the other two parts of this article series.

Part 1 - Return multiple values by By Reference argument, collection, and dictionary object.
Part 3 - Return multiple values by recordset and VBA class.

Code used in this article is based on a simple Access form as shown below. Each button click event triggers a function that returns multiple values and displays them in Immediate Window.

A simple form with 8 buttons for testing use.

4. Return multiple values by using an array.

An array is a variable that can contain multiple values stored in array elements. These values can be assigned to array elements as well as retrieved by using integer index numbers. You can individually process each element or use a loop to automatically process all elements together. To return multiple values from a function, we return a single array variable that contains multiple elements. For more information, please refer to Microsoft MSDN resources about array here Understanding Arrays.

Function below defines an array of 3 elements. Three values are assigned to elements of the array by using array index. Finally, the array variable is returned by the function so that when the function is called, it returns the array variable as a variant data type.

' This function returns an array which can hold multiple values.
Public Function GetArray() As Variant
    
    ' Define a static array with 3 elements. Note that the index
    ' of the array is 0 based.
    Dim arr(3) As Variant
    
    arr(0) = "John"
    arr(1) = "Star"
    arr(2) = #6/30/2010# ' Hire Date
    
    GetArray = arr        
End Function

To show how the function we created above returns multiple values, we call the function from the click event cmdGetArray_Click() on Form1 for button [4. Get Array]. In this subroutine, function GetArray is called and returns the array variable. Then we display the first and last element in Immediate Window.

Private Sub cmdGetArray_Click()
    Dim arr As Variant
    arr = GetArray()
    
    ' Display first name
    Debug.Print arr(0)
    
    ' Display the last element which is the Hire Date.
    Debug.Print arr(2)
End Sub

Immediate Window shows two values.

5. Return multiple values by using CSV string.

Comma-separated values (CSV) string sometimes is extremely useful and can greatly simplify programming logic because string is easy to process and most of the time small in size. Normally we turn CSV string into array to process as array can be looped through by using For loop or For Each iteration statement.

Function below returns a CSV string which holds multiple values. These values are simply 1, 2, 3, but if you treat them as primary keys of some relational database table, the usefulness of this simple CSV string is endless.

Public Function GetCSV() As String
    Dim strTest As String
	
    strTest = "1,2,3"
    GetCSV = strTest          
End Function

Now it's time to put our function to test. Call the function from the click envent cmdGetCSV_Click() on Form1 for button [5. Get CSV]. In this subroutine, the function returns a CSV string. Then the string is converted to an array by using the VBA Split function. We then print the two values in Immediate Window.

Private Sub cmdGetCSV_Click()
    Dim str As String
    Dim arr As Variant
    
    ' Get the CSV string which contains multiple values.
    str = GetCSV()
    
    ' Split the string to get an array of 3 elements
    arr = Split(str, ",")
        
    ' Display the value in first element
    Debug.Print arr(0)
    
    ' Display the value in second element
    Debug.Print arr(1)
End Sub

Immediate Window shows two values.

6. Return multiple values by using user-defined type.

User-defined type can be used to store related data in one variable. Note that user-defined type must be defined in a module. Other programming languages may call it struct or record, which is a static data structure.

First, we define a type called Employees in a VBA Module.

The VBA Module and code.

' Define user-defined type.
Public Type Employee
    FirstName As String
    LastName As String
    IsManager As Boolean
    HireDate As Date
End Type

Then, we define a function in Form1 that uses the user-defined type to return multiple values.

' Define a function that returns a user-defined type which can hold multiple values.
Function GetEmp() As Employee
    Dim emp As Employee
    
    emp.FirstName = "John"
    emp.LastName = "Star"
    emp.IsManager = True
    emp.HireDate = #6/30/2010#
    
    GetEmp = emp
End Function

Finally, we call the function in a click event cmdGetUserType_Click() on Form1 for button [6. Get User Type] to displays two values (FirstName and LastName) in Immediate Window.

Private Sub cmdGetUserType_Click()
    ' Define a user-defined type
    Dim emp As Employee
    
    ' Retrieve a Employee type variable which 
    ' holds four values for an employee.
    emp = GetEmp()
    
    Debug.Print emp.FirstName
    Debug.Print emp.LastName
End Sub

Immediate Window shows two values.

Happy Coding!


Copyright© GeeksEngine.com