VBA Dictionary Index: Access Items by Position

VBA & macros · Updated November 18, 2026

A VBA Scripting.Dictionary has no built-in numeric index for its entries: you retrieve an item by its unique key. To get an entry by position, copy the dictionary’s keys into an array with dict.Keys, select a key using the array’s bounds, then use that key to retrieve the value.

What “index” means in a VBA Dictionary

The word “index” can refer to two different things. A dictionary’s key is the identifier you use to look up a value, such as a product code or employee name. An array index is a number that identifies a position in an array. A dictionary uses the first kind of address, not the second.

For example, a dictionary might associate the key "A-17" with the value 125. The normal lookup is dict.Item("A-17"), or the shorthand dict("A-17"). There is no supported expression such as dict(1) that means “give me the second entry” unless 1 is itself a key in the dictionary.

This distinction matters when moving from a VBA Collection, whose items can be retrieved by a numeric position, or from an ordinary array. A dictionary is intended for finding values by meaningful keys. Its Count property tells you how many entries it contains, but does not create a positional index.

How Keys and Items work

The dictionary provides Keys and Items properties that return arrays. The keys array contains the dictionary’s keys; the items array contains the corresponding values. The positions in those two arrays match, so a key at a given array position identifies the value at that same position.

For instance, if the keys array contains "A-17" and then "B-04", the value at the second position in the items array belongs to "B-04". The clearest approach is usually to get the key from the keys array and then look up the value with that key. This keeps the relationship explicit and avoids treating the dictionary itself as an array.

These returned arrays represent the dictionary’s contents when the property is read. If code adds or removes entries afterward, an array already stored in a variable does not become a live view of the changed dictionary. Get a fresh array when you need the updated contents.

Get an entry by its position

The example below retrieves the second entry in a dictionary. It uses a one-based position for the reader-facing request—position 1 means the first entry—and converts that position into the actual bounds of the array returned by Keys. Checking the bounds is safer than assuming every array starts at zero.

Sub PrintDictionaryEntryByPosition()
    Dim dict As Object
    Dim keys As Variant
    Dim position As Long
    Dim arrayIndex As Long
    Dim entryKey As Variant

    ' Create a late-bound Scripting.Dictionary.
    Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare

    ' Add sample keys and their corresponding values.
    dict.Add "A-17", 125
    dict.Add "B-04", 250
    dict.Add "C-12", 90

    ' Ask for the second entry, counting from one.
    position = 2

    If dict.Count = 0 Then
        Debug.Print "The dictionary is empty."
        Exit Sub
    End If

    If position < 1 Or position > dict.Count Then
        Debug.Print "Position is outside the dictionary."
        Exit Sub
    End If

    keys = dict.Keys
    arrayIndex = LBound(keys) + position - 1
    entryKey = keys(arrayIndex)

    ' Use the key to retrieve its value.
    Debug.Print CStr(entryKey), dict.Item(entryKey)
End Sub

With the sample entries, the output is B-04 and 250. The example checks Count before calling LBound, so it handles an empty dictionary without trying to find the bounds of an empty result. It also checks that the requested one-based position is valid.

Before running a macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, even when they change workbook data. This sample only prints to the Immediate window, but the backup habit is important for macros that edit cells or files.

Loop through every key and value

If the goal is to process all entries rather than select one particular position, a loop is often more useful. A straightforward approach is to get the keys array and use each key to retrieve its value:

Sub PrintAllDictionaryEntries()
    Dim dict As Object
    Dim keys As Variant
    Dim i As Long

    ' Create and populate a sample dictionary.
    Set dict = CreateObject("Scripting.Dictionary")
    dict.Add "A-17", 125
    dict.Add "B-04", 250
    dict.Add "C-12", 90

    If dict.Count = 0 Then Exit Sub

    keys = dict.Keys

    For i = LBound(keys) To UBound(keys)
        Debug.Print CStr(keys(i)), dict.Item(keys(i))
    Next i
End Sub

Here i is an index into the keys array, not an index into the dictionary. The same key could also be used to retrieve the corresponding value from a separate items array at the matching position. Looking up by key makes the association especially clear when the code is later changed.

For a loop that only needs values, the Items property can be copied to a variant array and traversed in the same way. If the code needs both keys and values, remember that the two arrays must come from the same current dictionary contents. Avoid changing the dictionary between retrieving the arrays and using their positions.

Keys, duplicates, and case sensitivity

Dictionary keys are unique. Calling Add with a key that already exists raises an error rather than creating a second entry with the same key. To update an existing value, assign to its item, for example dict.Item("A-17") = 140. To avoid an error when a key may or may not exist, check it with dict.Exists("A-17") before adding or updating.

Text key comparison is controlled by CompareMode. Set it before adding entries. With vbTextCompare, keys such as "North" and "north" are treated as equivalent for text comparison; with the default binary comparison, letter case can distinguish text keys. Changing the comparison mode after entries have been added can raise an error.

Be deliberate about key design. If two records can share the same name, a name alone is not a unique key. Use a unique identifier, or combine fields into a key in a consistent way. Otherwise, a dictionary cannot represent those records as separate entries under the same key.

Does a dictionary preserve a useful order?

The arrays returned by Keys and Items pair keys and values by position. That pairing is what makes positional access through the arrays possible. It does not mean that the dictionary is a sorted list, or that a numeric position is a durable identity for an entry.

Do not use a position as a substitute for a key when the code needs a particular record. An entry’s position is meaningful only within the array you obtained at that time. If you need alphabetical, numerical, or otherwise controlled order, copy the keys into an array and sort that array, then look up each value by its sorted key. Sorting the keys separately from their associated values can otherwise break the relationship between them.

This is also why code such as “the third dictionary entry is the customer I need” is fragile. If entries are added, removed, or obtained in a different sequence, the third position may refer to a different key. A stable customer ID is a better way to identify that customer.

Early binding, late binding, and Mac considerations

The sample uses late binding: Set dict = CreateObject("Scripting.Dictionary"), with the variable declared as Object. On Excel for Microsoft 365 on Windows, this avoids setting a reference in the Visual Basic Editor. The trade-off is that VBA does not provide the same compile-time type information or IntelliSense for the dictionary variable.

With early binding on Windows, set a reference to Microsoft Scripting Runtime in the Visual Basic Editor under Tools > References, then declare the variable as Scripting.Dictionary and create it with New Scripting.Dictionary. Early binding can make the object’s members easier to discover, but a workbook moved to a computer without the required reference may encounter a missing-reference problem.

Excel for Mac does not provide the same Windows COM environment for Scripting.Dictionary, so code that creates it with CreateObject may fail there. For workbooks that must run on both platforms, consider a VBA Collection when its key and retrieval behavior fits the task, or use arrays and explicit search or sorting logic. Check the intended Excel platforms before building a solution around a Windows scripting component.

Common errors and practical fixes

  • “Object required” or an automation error: confirm that the dictionary was successfully created and that the platform supports the scripting component. On Windows, check for spelling errors in CreateObject("Scripting.Dictionary").
  • Duplicate-key error: use Exists to decide whether to add a new key or update the existing item. Do not call Add twice for the same key.
  • Subscript out of range: check that the requested position is within the array’s LBound and UBound. Also handle an empty dictionary before asking for array bounds.
  • Unexpected text-key matches: set CompareMode before adding entries and choose binary or text comparison intentionally.
  • Wrong value for a selected position: make sure the key and item arrays represent the same dictionary state. Prefer selecting a key and then calling Item(key).

When debugging, inspect dict.Count, the selected key, and the result of dict.Exists(key). Those checks help separate a bad position from a missing key or a comparison-mode issue.

Frequently Asked Questions

Can I write dict(1) to get the first item?
No. That expression asks for the value associated with the key 1. To get an entry by position, select a key from dict.Keys and use it to retrieve the value.

Are dictionary positions zero-based or one-based?
The dictionary has no positions of its own. The array returned by Keys has bounds you can inspect with LBound and UBound. Use those bounds rather than building code around an assumed starting index.

How do I find the position of a particular key?
Copy the keys with dict.Keys and scan the array until the key matches. The resulting array index is a position in that snapshot, not a permanent dictionary identifier. For lookup alone, use dict.Exists(key) and dict.Item(key) instead.

Can a dictionary contain two identical keys?
No. Each key must be unique under the dictionary’s comparison rules. Choose a more specific key or store multiple records together under one key if the data model requires repeated labels.

Is Scripting.Dictionary available on Mac?
It relies on Windows scripting/COM support and is not a dependable cross-platform choice for Excel VBA. Use an alternative such as a collection or arrays when the workbook must run on Mac, and verify the behavior on the target platform.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.