How to Return Multiple Values from an Excel VBA Function
Question details
The user needs to return or share multiple values from a VBA procedure.

- Product
- Microsoft Excel VBA and Microsoft Access VBA
- Device & OS
- not provided
- Scenario
- A VBA procedure calculates several results that the calling code needs to use.
- Observed behavior
- The user wants a practical alternative to using global variables for multiple results.
Open the Office file that contains your VBA code and identify the values and data types you need to pass back. In Excel, use a macro-enabled workbook such as an .xlsm file if you want to save the code.
Return a Variant Array from a VBA Function
Use a Variant array when you want one function to return several values together.
In Excel, select Developer > Visual Basic, then choose Insert > Module.
Paste this code into the module: Public Function GetValues() As Variant GetValues = Array("North", 42) End Function
Add and run this procedure to assign the return value to a Variant and access each element: Public Sub TestGetValues() Dim result As Variant result = GetValues() Debug.Print result(0), result(1) End Sub In the VBA editor, choose View > Immediate Window to see the output.

Pass Results Back Through ByRef Parameters
Use ByRef parameters when you prefer named variables and explicit data types over an array.
Work with Excel Files in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface for opening and editing Excel files. It can make moving everyday spreadsheet work easier, but it does not fix Microsoft VBA issues and VBA macro support may differ.

Frequently Asked Questions
Can a VBA function return values of different data types?
Yes. Declare the function As Variant and return an Array containing the values. Assign the result to a Variant in the calling procedure and access its elements by index.
Can I return multiple values without an array?
Yes. Pass variables as ByRef parameters to a Sub or Function and assign the results to those parameters. A user-defined type is another option when the values belong together.
Should I use global variables to share results?
Prefer a return value or ByRef parameters when possible. Global variables can be changed by other procedures, making it harder to track where a value came from.
Can Access TempVars share values between procedures?
Yes. In Access, add a named value with TempVars.Add, read it through TempVars("name"), and remove it with TempVars.Remove when it is no longer needed. TempVars are Access-specific and share state beyond a single procedure call.




