Yes. In VBA, a function can return a Scripting.Dictionary object; assign the dictionary to the function name with Set, then use Set again when you receive it. On Windows, a straightforward late-bound function can create and return one without adding a project reference.
What it means to return a dictionary
A dictionary is an object that stores pairs of keys and items. A key identifies an item, much like a lookup value identifies a row in a table. Because the dictionary is an object rather than a simple value such as a number or string, VBA uses the Set statement to assign the object reference.
Returning a dictionary does not copy its contents into a worksheet cell. It gives the caller an object reference that VBA code can use to look up, add, change, or remove entries. This is useful when a procedure needs to build a collection of related values and another procedure needs to work with that collection.
For example, a function might read product codes and prices, then return a dictionary keyed by product code. A calling procedure can ask for the price associated with a particular code without scanning the source data again. The same pattern can be used for settings, counts, or other data where each key should identify one item.
How the return assignment works
In VBA, the function name acts as the function’s return variable. For an object result, assign the object to that name with Set. The function declaration must also have an object-compatible return type, such as Object or Scripting.Dictionary.
There are two separate object assignments in the usual pattern. Inside the function, Set BuildPriceMap = dict makes the function return the dictionary. At the call site, Set prices = BuildPriceMap() stores the returned reference in the caller’s variable. Leaving out either Set is a common cause of errors.
Returning the reference means the caller can work with the returned dictionary directly. If the caller changes an item in that dictionary, it is changing that object, not a separate automatically created copy. If you need an independent copy, create another dictionary and copy the keys and items deliberately.
A complete late-bound example
Put this example in a standard VBA module in an Excel workbook. The function creates a dictionary of sample product prices, and the procedure that calls it prints each key and item in the Immediate window. In the VBA editor, open the Immediate window with Ctrl+G on Windows.
Option Explicit
Public Function BuildPriceMap() As Object
Dim dict As Object
' Create a Windows Scripting.Dictionary without a project reference.
Set dict = CreateObject("Scripting.Dictionary")
dict.CompareMode = vbTextCompare
dict.Add "A101", 12.5
dict.Add "B205", 8.75
' Return the object reference.
Set BuildPriceMap = dict
End Function
Public Sub ShowPriceMap()
Dim prices As Object
Dim key As Variant
' Receive the returned object with Set.
Set prices = BuildPriceMap()
For Each key In prices.Keys
Debug.Print key, prices(key)
Next key
End Sub
Run ShowPriceMap from the VBA editor or the Macros dialog. The Immediate window should show the two product codes and their prices. Before running any macro, save a copy of the workbook. Macro actions cannot be reversed with Ctrl+Z, even when a particular example only prints output.
The CompareMode line makes text keys compare without regard to letter case. It is set before any entries are added, as required: changing the comparison mode after adding dictionary entries can cause an error. Remove that line if case-sensitive key matching is what the application needs.
Late binding versus early binding
The example is late-bound: the variable is declared As Object, and CreateObject creates the dictionary at run time. This avoids requiring each user to set a reference to the Scripting Runtime in the VBA project. It is often convenient when a workbook will be shared among Windows users whose reference settings may differ.
Early binding gives VBA the specific dictionary type and can provide more helpful autocomplete and compile-time checking. To use it, open the Visual Basic Editor, choose Tools > References, and select Microsoft Scripting Runtime if it is available. Then declare the function and variable as Scripting.Dictionary and create the object with New Scripting.Dictionary. A missing reference can show as MISSING in the References list, which prevents the project from compiling until the reference is fixed or removed.
Neither style changes the essential return rule: assign the object to the function name using Set, and receive it using Set. Late binding reduces a reference dependency, but it does not make a Windows COM component available on every platform. In particular, the Scripting Runtime approach should not be assumed to work in Excel for Mac.
Using the returned dictionary
After receiving the object, you can retrieve an item by key, test whether a key is present, inspect the number of entries, or iterate over its keys. For instance, the following lines can go inside a procedure that already has a dictionary variable named prices:
prices.Exists("A101")returns whether that key is present.prices("A101")retrieves the item for that key.prices.Countgives the number of key-item pairs.prices.Remove "A101"removes a key and its item.
Check Exists before looking up a key when it may not be present. Reading an item through a missing key can create an entry with an empty item in a Scripting.Dictionary, which may hide a spelling mistake or an unexpected input. A deliberate existence check makes the intended behavior clearer.
Dictionary keys must be unique. Calling Add with a key that already exists raises an error; it does not create a second entry with the same key. If the intended behavior is to replace an existing value, assign to the item for that key after checking whether it exists. Consider also whether case-insensitive comparison is appropriate: with vbTextCompare, keys such as "abc" and "ABC" are treated as the same key.
Common errors and how to avoid them
A frequent error is assigning an object without Set. Use Set dict = CreateObject(...) when creating the dictionary, Set FunctionName = dict when returning it, and Set result = FunctionName() when receiving it. Ordinary assignment without Set is for values, not object references.
Another issue is calling a function that returns an object as if it returned a number or string. For example, assigning its result to a String variable is not a substitute for receiving the dictionary. Declare the receiving variable as Object or as the specific dictionary type when early-bound.
Also check that the function actually creates and assigns a dictionary on every successful path. If an error or conditional branch exits before the return assignment, the caller may receive Nothing. Before using a returned object, code can test If prices Is Nothing Then and decide how to handle that case. A function’s declared return type alone does not guarantee that it successfully created an object.
Finally, remember that a worksheet formula cannot display a dictionary as a normal cell value. A VBA function returning an object is intended for other VBA code, not for direct entry as a cell formula such as =BuildPriceMap(). If the goal is to show results on a sheet, write selected dictionary items into cells from a Sub, or return a supported scalar or array result instead.
Excel for Mac and portability
The code using CreateObject("Scripting.Dictionary") targets Excel for Microsoft 365 on Windows, where the Windows Scripting Runtime COM object is available. Excel for Mac does not provide that same Windows COM component, so late binding does not solve the platform difference. A workbook that must run on both Windows and Mac should avoid relying on this object unless its compatibility has been established for the exact environment.
For cross-platform VBA, a Collection or an array may be a better fit, depending on the task. A Collection can store values and objects, but it does not offer the same built-in key-existence check and key-management behavior as a Scripting.Dictionary. Arrays work well for a fixed or sequential set of data, but they do not provide the same convenient lookup by arbitrary key. Choosing among them depends on whether keyed lookup, portability, or simplicity matters most.
There is no separate dictionary-return syntax for 32-bit versus 64-bit Excel; the main concern here is whether the relevant object library is present and usable. If distributing a workbook, document platform assumptions and test the macros in the target Excel environment. If a procedure’s purpose is only to write results to a worksheet, a Sub that writes the results may be clearer than returning an object at all.
When to use a function rather than a Sub
Use a function when the caller needs a result it can store and use, such as a dictionary prepared from input data. This keeps creation logic in one place and lets multiple procedures request the same kind of result. The caller can then decide what to do with the returned object instead of requiring the builder to print values or change a particular worksheet.
A Sub is often more suitable when the main purpose is an action, such as filling a report range, formatting cells, or displaying a message. A Sub can still create a dictionary internally, but it does not return a value in the way a Function does. One practical design is to have a function build the dictionary and a separate Sub decide how to use or display it.
Keep the function’s contract clear: state what its keys represent, what the items contain, how duplicate keys are handled, and whether the returned object can be empty. Those details help the calling code use the result safely, especially if the function is shared across a larger workbook project.
Frequently Asked Questions
Can a VBA function return a Scripting.Dictionary?
Yes. Declare an object-compatible return type, assign the dictionary to the function name with Set, and receive the result with Set in the calling procedure.
Do I need to use Set when returning a dictionary?
Yes. A dictionary is an object reference. Use Set both for the function’s return assignment and for the caller’s assignment.
Can I use a dictionary-returning function in an Excel cell?
Not as a normal cell result. A worksheet cell expects a value it can display, not a VBA dictionary object. Use VBA code to read the dictionary and write suitable values to cells.
Does late binding make Scripting.Dictionary work on Mac?
No. Late binding avoids a compile-time reference, but Scripting.Dictionary is a Windows COM component. For a Mac-compatible workbook, consider a Collection, an array, or another approach supported by the target platform.
What happens if I add the same key twice?Add raises an error when the key already exists. Check with Exists first if the code should update an existing item or skip a duplicate.
Related
- Data Validation Multiple Selection in Excel: What Works
- Data Validation Interview Questions and Strong Answers
- Data Validation Greyed Out in Excel? Causes and Fixes
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Option Button in Excel VBA: Add, Group, and Use ItYes. In Excel for Microsoft 365 on Windows, add an option button from Developer > Insert, then choose…
- Excel VBA AutoFilter and Sort: Apply Both to Data SafelyYes. In Excel VBA, apply AutoFilter to a range that includes its headers, then sort that same complete…
- Microsoft Excel Macro Recorder: Record and Run MacrosMicrosoft Excel’s macro recorder captures a sequence of actions in Excel and converts them into VBA code that…
- Excel VBA String to Number: Safe Conversion MethodsIn Excel VBA, convert text that represents a number with a conversion function such as CDbl for decimals…
- How to Find a VBA Project Password in Excel SafelyExcel cannot display or reveal a forgotten VBA project password. To regain legitimate access, check your password manager…
- VBA Array Function: Syntax, Examples, and GotchasIn VBA, the Array function creates an array of values and returns it inside a Variant. Assign the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.