How to return multiple values from a VBA function (Part 1)
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.
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.
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.
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.
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.
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.
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.
Immediate Window shows four values.
Copyright © 2013 GeeksEngine.com. All Rights Reserved.
This website is hosted by LunarPages.
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.