Custom Search

How to return multiple values from a VBA function (Part 2)

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!


Other Recent Articles from the MS Access category:

1.Solved - the size of the Access query result is larger than the maximum size of a database (2 GB)
2.How to easily get a list of field names in MS Access
3.How to count distinct records in MS Access
4.How to do transaction based processing in MS Access
5.How to open a document (local/network file or web page) from MS Access
6.How to use ADOX to create unique composite index - the VBA approach
7.How to do cross-table update queries in MS Access - the right way
8.Three efficient ways to get the number of records by using VBA
9.How to create a composite unique index (not as a primary key) in MS Access
10.Use VBA to get the correct number of records in a Recordset object
11.Disable Access Prompt when a record is changed, table deleted, or action queries run
12.How to hide and unhide a MS Access object
13.How to return multiple values from a VBA function (Part 3)
14.How to return multiple values from a VBA function (Part 1)
15.Three ways to programmatically duplicate a table in MS Access by VBA
16.Create a DLL by CSharp or VB.Net for VBA
17.How to correctly reference and call a DLL
18.How to register a C# or VB.Net DLL
19.Email address validation by Regular Expressions using VBA
20.Fix MS Access error: Query must have at least one destination field
21.How to unselect radio buttons in MS Access after it has been selected
22.How to Change Query Timeout Value for MS Access SQL Queries
23.What is Northwind Traders database

Copyright © 2017 All Rights Reserved.

This website is hosted by HostGator.

No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it.

Home | Feedback | Terms of Use | Privacy Policy