VBA & Macro Problems

How to Return Multiple Values from an Excel VBA Function

Camila MilosovichCamila Milosovich Nov 20, 2026 869 views

Question details

The user needs to return or share multiple values from a VBA procedure.

How to Return Multiple Values from an Excel VBA Function
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.
Before you start

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.

Solution 1Recommended

Return a Variant Array from a VBA Function

Use a Variant array when you want one function to return several values together.

1
Open the VBA editor

In Excel, select Developer > Visual Basic, then choose Insert > Module.

2
Add the function

Paste this code into the module: Public Function GetValues() As Variant GetValues = Array("North", 42) End Function

3
Read the results

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.

Return a Variant Array from a VBA Function
Array indexes: The Array function in this example returns elements starting at index 0. Keep track of which position represents each result.
Free Microsoft Office alternative

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.

Open and edit common Microsoft Excel formats, including .xlsxUse familiar spreadsheet tools for everyday calculations and data reviewWork across documents, spreadsheets, and presentations in one lightweight suiteMove common Office files into WPS Office without recreating them
microsoft office alternative - wps office

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.