Yes. In Access VBA, you can use the Scripting.Dictionary object to store pairs of keys and values, check whether a key exists, and quickly retrieve or update the value associated with it. Create it with a reference to Microsoft Scripting Runtime or with late binding; the choice affects editor support and how easily the code travels between computers.
What a dictionary does in Access VBA
A dictionary is a collection in which each item is found by a unique key. For example, a customer ID can be the key and the customer’s name or order total can be its value. Unlike an Access table, a dictionary normally exists only while the code is running. It is useful for temporary lookups, grouping records, removing duplicates, and keeping track of values that would otherwise require repeated searches.
The Scripting.Dictionary class is provided by the Windows Scripting Runtime. It is not a special Access table type or a built-in SQL query feature. Access VBA creates an object, adds key/value pairs, and then reads or changes them while the procedure runs. If the results need to persist after the procedure ends, write them to a table or another storage location.
Keys must be unique within one dictionary. Values do not have to be unique: several keys can point to the same value. A key might be a text code, a number, or another suitable value. In typical Access work, text identifiers are common, but choose a key that reliably distinguishes the records you mean to distinguish.
How to create the dictionary
There are two common binding choices. With early binding, add the Microsoft Scripting Runtime reference in the Visual Basic Editor by choosing Tools > References and selecting Microsoft Scripting Runtime. You can then declare a variable as Scripting.Dictionary and use New Scripting.Dictionary. The editor can offer the object’s members as you type, and VBA can check the declared type when compiling.
With late binding, declare the variable as Object and create it using CreateObject. This avoids a compile-time reference to the library, which can make a database easier to move between Windows computers with different reference configurations. The trade-off is that the editor cannot provide the same member suggestions or compile-time checking for the dictionary’s methods.
Public Sub MakeDictionaryLateBound()
Dim dict As Object
' Create the Windows Scripting.Dictionary at run time.
Set dict = CreateObject("Scripting.Dictionary")
dict.Add "North", 12
Debug.Print dict("North")
End Sub
Both approaches use the same dictionary features. Late binding does not make the object available on every operating system: Microsoft Access is a Windows desktop application, and the Scripting Dictionary is a Windows COM object. If your database is used in a controlled Windows environment, early binding is often convenient during development. If reference mismatches are a deployment concern, late binding is often more portable between Windows installations.
Adding, finding, and changing items
Use Add to insert a new key and its value. Use Exists to test whether a key is present before reading it or adding it. The default property, commonly written as dict(key) or dict.Item(key), gets the value for a key and can also assign a new value to an existing key. These operations make a dictionary useful as an in-memory lookup table.
dict.Add key, valueinserts a new pair. Adding a key that already exists raises an error.dict.Exists(key)returns True when the key is present and False when it is not.dict(key)reads the item for a key; assigning to it updates the item.dict.Countreports the number of key/value pairs.dict.Remove keyremoves one pair, whiledict.RemoveAllclears the dictionary.dict.Keysanddict.Itemsreturn arrays of keys and values, respectively.
For example, if a dictionary contains a key named North, then dict.Exists("North") checks for it and dict("North") = 15 changes its value to 15. Calling Add "North", 15 again is not an update; it attempts to create a duplicate key and fails. Use an existence check to decide whether to add a new pair or update the old value.
Example: total amounts by category
A practical Access use is grouping a recordset in VBA. The following example assumes an existing table or query named Sales with fields named Category and Amount. It adds each category’s amount to a running total, treating a Null category as “(blank)” and a Null amount as zero. Change the source name and field names to match your database.
This example uses early binding, so select Microsoft Scripting Runtime in Tools > References first. It uses DAO objects, as is customary for Access data access, and prints the results in the Immediate window in the Visual Basic Editor. The sample assumes the category values can be represented as text and the totals fit the Currency data type.
Public Sub TotalSalesByCategory()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim totals As Scripting.Dictionary
Dim categoryKey As String
Dim amount As Currency
Dim key As Variant
' Group the source rows in memory; change names to match your database.
Set db = CurrentDb
Set rs = db.OpenRecordset( _
"SELECT Category, Amount FROM Sales;", dbOpenSnapshot)
Set totals = New Scripting.Dictionary
totals.CompareMode = vbTextCompare
Do Until rs.EOF
categoryKey = CStr(Nz(rs!Category, "(blank)"))
amount = CCur(Nz(rs!Amount, 0))
If totals.Exists(categoryKey) Then
totals(categoryKey) = CCur(totals(categoryKey)) + amount
Else
totals.Add categoryKey, amount
End If
rs.MoveNext
Loop
For Each key In totals.Keys
Debug.Print CStr(key), totals(key)
Next key
rs.Close
Set rs = Nothing
Set db = Nothing
Set totals = Nothing
End Sub
For instance, if the source has two rows for North with amounts 12 and 8, the printed total for North is 20. The code first checks for the key: on the first North row it adds the key with a value of 12; on the next it replaces that value with 12 plus 8. The recordset is opened as a snapshot because this procedure reads records rather than editing them.
Before running any VBA procedure that changes data, save a copy of the database. Macros and VBA changes cannot be undone with Ctrl+Z. This example only reads the recordset and prints totals, but adapting it to update tables changes the risk. For production work, consider error handling and ensure recordsets are closed if an error occurs.
Key comparison and Null values
The dictionary’s CompareMode controls how text keys are compared. The default is binary comparison, which treats upper- and lowercase text as different keys. Setting CompareMode to vbTextCompare makes text-key comparisons case-insensitive for the dictionary’s purposes. Set the mode immediately after creating the dictionary and before adding any keys. Changing it after entries have been added can raise an error.
Case-insensitive matching can be useful when codes such as abc and ABC should identify the same group. It is not automatically correct for every dataset: if capitalization is meaningful in your identifiers, keep the default comparison. Also consider leading or trailing spaces. A key such as "North" is not necessarily the same as "North "; normalize data deliberately if those should match.
Handle Nulls before using values as keys or doing arithmetic. Access’s Nz function can substitute a chosen value, as in the example. Choose a substitute that cannot be mistaken for a legitimate category, or handle blank and Null values separately if that distinction matters. Avoid assuming that an empty string, a Null, and a missing dictionary key all mean the same thing: they are different states.
Dictionary versus a Collection or query
Access VBA also provides the built-in Collection type. A Collection can hold items and can use keys, but it does not have the same convenient Exists method. Checking for a missing Collection key often requires error handling. A dictionary is generally a more direct fit when the code frequently asks whether a key exists, updates values by key, or needs arrays of keys and items.
A dictionary is not a replacement for every Access query. If a task is naturally a relational operation—filtering rows, joining tables, grouping totals for a report, or saving results—an Access query is often clearer and can use the database engine directly. A dictionary is handy when VBA needs a temporary lookup structure or when values must be accumulated as code processes records. For large datasets, compare approaches rather than assuming that loading all records into memory is faster or more suitable.
Common problems and practical safeguards
- “User-defined type not defined”: If the variable is declared as
Scripting.Dictionary, check that Microsoft Scripting Runtime is selected under Tools > References. Alternatively, use late binding with anObjectvariable. - “ActiveX component can’t create object”: The runtime object could not be created on that computer. Check that the database is running in a supported Windows environment and that the Scripting Runtime is available. Late binding avoids a missing VBA reference but cannot create a COM component that is unavailable.
- Duplicate-key error: Check with
Existsbefore callingAdd, or update the existing item instead. - Unexpected duplicate-looking categories: Check case sensitivity, spaces, data types, and Null handling. Two values that look alike on a form may not be identical strings.
- CompareMode error: Set
CompareModebefore inserting the first item. - Wrong value type: Dictionary items are Variants, so explicitly convert values when the calculation requires a type such as Currency or Long.
When debugging, use Debug.Print to inspect keys and values in the Immediate window, and compile the project after changing declarations or references. Keep key creation consistent throughout the procedure: converting all category values to strings, for example, avoids accidental differences caused by mixing numeric and text representations.
When to choose early or late binding
Choose early binding when you control the Windows computers that will run the database and want compile-time checking and member suggestions. It also makes the intended object type obvious to someone reading the code. The required reference is a dependency: if it is missing or broken on another machine, code that uses the declared type may fail to compile until the reference is corrected.
Choose late binding when avoiding that specific reference dependency matters more than editor assistance. Declare the object as Object and create it with CreateObject("Scripting.Dictionary"). Because VBA cannot verify the dictionary member names in the same way at compile time, misspellings may only become apparent when the code runs. Neither method eliminates the need to confirm that the Windows object itself is available.
Frequently Asked Questions
Is Scripting.Dictionary built into Access?
It is not an Access table feature or a native VBA collection class. It is a Windows Scripting Runtime object that Access VBA can use. You can refer to it early or create it with late binding, subject to the runtime being available.
How do I check whether a dictionary key exists?
Call dict.Exists(key). It returns True if the key is present. Use that result before calling Add when duplicate keys are possible, or before reading a key that might be absent.
How do I make dictionary keys case-insensitive?
Set dict.CompareMode = vbTextCompare immediately after creating the dictionary and before adding any items. This affects text-key comparisons; it does not remove spaces or otherwise normalize the keys.
Can a dictionary store objects as values?
Yes. Dictionary items can hold object references as well as ordinary values. In VBA, use Set when assigning an object reference to an item, and take care to manage the referenced objects’ lifetimes. For simple totals and labels, ordinary values are easier to handle.
Does a dictionary keep its data after Access closes?
No. A dictionary created in a procedure is an in-memory object, not permanent storage. Save results to an Access table or another durable destination if they must remain available after the code ends.
Related
- How to Merge Cells in Excel: Steps, Options, and Fixes
- Split Cell Contents Into Multiple Columns in Excel
- Excel Conditional Formatting: Find Partial Text Matches
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Len Function: Count String Characters in ExcelIn VBA, Len returns the length of a string: Len("VBA Len") returns 7, including the space. Use it…
- VBA Create New Workbook: Code, Options, and Tips in ExcelTo create a new Excel workbook with VBA, use Workbooks.Add. For example, Set newWb = Workbooks.Add creates a…
- VBA Error Handling Examples for Reliable MacrosVBA error handling examples usually use On Error GoTo to catch unexpected failures, On Error Resume Next briefly…
- Excel VBA: Protect a Sheet and Allow Filter and SortYes. In Excel VBA, protect the worksheet with AllowFiltering:=True and AllowSorting:=True. The filter controls must already exist, and…
- Excel VBA: Delete Rows Based on a Cell Value MatchTo delete worksheet rows when a cell matches a value, loop upward from the last used row and…
- VBA MsgBox New Line: Add Line Breaks to MessagesTo add a new line in a VBA MsgBox, join the message text with vbCrLf or vbNewLine where…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.