Part 1: Using By Reference argument, collection, and dictionary object

VBA functions (and other programming languages as well) can only return one value. It's a common and useful practice that we need to return more values from a function. This article sums up some of the tips and tricks by using VBA language as an example. Approaches showed here also apply to other programming languages.

When you process data by code, sometimes the data comes in pairs or related groups. In this case, we can use various data structures to organise them. Depending on your problem-solving situation, you may want to consider using data structures such as By Reference argument, collection object, dictionary object, array, CSV, user-defined types, database recordset, or classes.

Part 2 - Return multiple values by array, CSV, and user-defined type.
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 in Immediate Window.

A simple form with 8 buttons for testing use.

1. Return multiple values by using passing arguments By Reference.

Passing arguments By Reference is probably the most common way to return multiple values from a function.

It uses the ByRef keyword to tell the compiler that the variable passed to the function is only a pointer to a memory location where the actual value of the variable is stored. This way, the code inside the function itself can modify the value of the variable. Even through the function does not explicitly return the changed value, it can be retrieved by using the same variable name within the code that calls the function.

By contrast, passing arguments By Value instructs the function that the variable is read-only so the code inside the function can't change its value.

Note that in VBA and Visual Basic 6.0, if you do not specify ByVal or ByRef for a function or procedure argument, the default passing mechanism is ByRef. In Visual Basic .NET, the default behavior is passing arguments by value. It is a good coding practice for VBA and VB6 to include either the ByVal or ByRef keyword for each function argument (also called parameter).

The function below can modify its argument which is a reference pointer and it may hold a different value after the function is called. The reference pointer is defined by the ByRef keyword which is the default passing mechanism of VBA.

' Argument strWeekdayName can be changed inside the function.	
Public Function IsToday(ByRef strWeekdayName As String) As Boolean
    ' If the weekday passed in is equal to today's weekday name, return True.	
    If strWeekdayName = WeekdayName(Weekday(Date)) Then
    	' Explicit return value	
        IsToday = True
    Else
        ' If it does not equal to today's weekday name, return today's weekday name by assigning
        ' today's weekday name to the argument.
        strWeekdayName = WeekdayName(Weekday(Date))
        
        ' Explicit return value	
        IsToday = False
    End If    	
End Function

To test our function, we display the return values in Immediate Window by using Debug.Print. The function is called from the click envent cmbGetByRef_Click() on Form1 for button [1. Get ByRef].

In this subroutine, the function is called and returns a Boolean value as well as today's weekday name. Note that its argument strDayName is called again after the function. blnIsToday is the return value of the function, and strDayName is today's weekday name.

Private Sub cmbGetByRef_Click()
    Dim blnIsToday As Boolean
    Dim strDayName As String
    
    strDayName = "Monday"
    
    blnIsToday = IsToday(strDayName)
    
    Debug.Print blnIsToday
    Debug.Print strDayName
End Sub

Immediate Window shows two values.

2. Return multiple values by using a collection object.

A Collection object is an ordered set of items and each item can be referred to by using an index. Each item in the collection holds a specific index, starting from 1. Items (also can be called elements) of a collection do not have to share the same data type.

In this function, we create a collection object and assign two values to it. Then the collection object is returned by the function.

' This function returns a collection object which can hold multiple values.
Public Function GetCollection() As Collection
    
    Dim var As Collection
    Set var = New Collection
    
    ' Add two items to the collection
    var.Add "John"
    var.Add "Star"
    
    Set GetCollection = var
End Function

Then we call the function from the click event cmbGetCollection_Click() on Form1 for button [2. Get Collection] to display two values in Immediate Window. The value of each element can be retrieved by using the Item property.

Private Sub cmbGetCollection_Click()
    Dim Employee As Collection
    Set Employee = GetCollection()
            
    ' Use the collection's first index to retrieve the first item.
    ' This is also valid: Debug.Print Employee(1)
    Debug.Print Employee.Item(1)
    Debug.Print Employee.Item(2)
End Sub

Immediate Window shows two values.

3. Return multiple values by using a dictionary object.

The Dictionary object is a data structure that holds a set of key-value pairs. The key is a unique String value. The item can be any data type. Dictionary object is 0 based index which means the first item has an index of 0.

In this function, we create a dictionary object and assign two key-value pairs to it. Then the dictionary object is returned by the function.

' This function returns a dictionary object which can hold multiple values.
Public Function GetDict() As Dictionary
    Dim var As Dictionary
    Set var = New Dictionary
    
    ' The "key" is the item name and the "value" is the description.
    var.Add "First Name", "John"
    var.Add "Last Name", "Star"
    
    Set GetDict = var
End Function

Then we call the function from the click envent cmdGetDictionary_Click() on Form1 for button [3. Get Dictionary] to display values of the items in the dictionary object in Immediate Window. The value of an item can be retrieved by using either the key name or the item's index number.

Private Sub cmdGetDictionary_Click()
    Dim Employee As Dictionary
    Set Employee = GetDict()
                
    ' Use an item's key name to get its value.
    Debug.Print Employee.Item("First Name")
    Debug.Print Employee.Item("Last Name")
    
    ' Use an item's index number to get its key.
    Debug.Print Employee.Keys(0)
    
    ' Use an item's index number to get its value.
    Debug.Print Employee.Items(0)
End Sub

Immediate Window shows four values.

Happy Coding!


Copyright© GeeksEngine.com