VBA Dictionary Case Insensitive? How CompareMode Works

VBA & macros · Updated November 18, 2026

Yes. A VBA Scripting.Dictionary can treat keys as case-insensitive: set its CompareMode property to vbTextCompare before adding any keys. With that setting, keys such as "North" and "NORTH" are considered the same key; the default, vbBinaryCompare, distinguishes them.

What CompareMode Controls

A dictionary stores key-and-item pairs, much like a lookup table. The key identifies an item, and methods such as Exists, Add, and the Item property use that key to find or manage the pair. CompareMode tells the dictionary how to compare string keys. It does not change the stored item, and it does not convert the key text to uppercase or lowercase.

For example, if a dictionary uses text comparison and contains the key "North", checking Exists("NORTH") returns True. The dictionary treats the spellings as equivalent for lookup and duplicate detection. That is different from changing the displayed key to "NORTH": the spelling originally supplied remains the key text you see when retrieving the keys.

Case sensitivity is therefore a choice about matching, not about how a string is stored. This matters when a macro imports names, product codes, email addresses, or other text from sources where capitalization is inconsistent.

Set CompareMode Before Adding Keys

Set the comparison mode immediately after creating the dictionary and before adding the first key. Once the dictionary contains data, changing CompareMode raises an error. This is a frequent cause of a macro failing when someone adds the setting later in an existing procedure.

For Excel for Microsoft 365 on Windows, a late-bound dictionary can be created with CreateObject. The following short procedure shows the correct order. It prints True and then 12 in the Immediate window because the lookup ignores capitalization.

Option Explicit

Sub CheckCaseInsensitiveKey()
    Dim dict As Object

    ' Save a copy of the workbook before running a macro.
    ' VBA macros cannot be undone with Ctrl+Z.
    Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare

    dict.Add "North", 12
    Debug.Print dict.Exists("NORTH")
    Debug.Print dict("north")
End Sub

To run it, open the Visual Basic Editor, insert a standard module, and place the procedure there. On Windows, Alt+F11 opens the editor; on Mac, the editor shortcut is commonly Option+F11, though keyboard settings and function-key configuration can affect it. Run the macro from the editor or Excel’s Macro dialog. The Immediate window in the editor displays the two results.

The example uses late binding, so it does not require selecting a library reference in the VBA editor. If you instead declare a variable as Scripting.Dictionary and want IntelliSense and compile-time type checking, you need to add a reference to Microsoft Scripting Runtime in the VBA project’s references. That approach is often called early binding.

Available Comparison Modes

The commonly relevant settings are VBA’s comparison constants. For ordinary case-insensitive matching, vbTextCompare is the usual choice. The mode applies to string keys; it is not a general setting for comparing the dictionary’s values.

  • vbBinaryCompare compares string keys using binary comparison. It is the default, so "North" and "NORTH" are different keys.
  • vbTextCompare compares string keys as text without distinguishing ordinary differences in letter case. It is the setting to use when capitalization should not create separate entries.
  • vbDatabaseCompare is intended for database comparisons in Microsoft Access. It is not the normal choice for an Excel workbook macro.

Text comparison follows text-comparison rules, which can depend on locale. Do not assume it performs every kind of text cleanup: case-insensitive comparison does not mean trimming spaces, removing accents, ignoring punctuation, or treating different spellings as equivalent. If your data needs those rules, normalize it explicitly or define a separate matching strategy.

Duplicate Keys and Safe Lookups

With vbTextCompare in effect, adding "North" and then adding "NORTH" is an attempt to add the same key twice. The dictionary’s Add method raises an error for a duplicate key. That is expected behavior, not evidence that the comparison mode failed.

Check for an existing key before adding it when duplicates are possible. Decide what a duplicate should mean for your task: skip it, replace its item, combine the values, or report it. For example, a contact-import macro may want to keep the first email address, while a sales summary may want to add later amounts to the existing total.

Use Exists for a safe test before reading an item. A subtle behavior of Scripting.Dictionary is that requesting an item through dict(key) when the key is absent can create that key with an empty item. Code that assumes a missing lookup merely returns a harmless blank can therefore alter the dictionary accidentally. A clear pattern is:

If dict.Exists("NORTH") Then
    Debug.Print dict("NORTH")
Else
    Debug.Print "Key not found"
End If

Here Exists respects the dictionary’s comparison mode. If the dictionary contains "North" and uses vbTextCompare, the test succeeds even though the test uses uppercase letters.

Case-Insensitive Keys Are Not Case Conversion

It is useful to separate three operations that are often confused. Setting CompareMode changes how the dictionary matches keys. Calling UCase$ or LCase$ changes the string you pass in. Trimming a string removes leading or trailing spaces. These operations solve different problems.

For instance, "North" and "NORTH" match under text comparison, but "North " includes a trailing space and should not be treated as the same cleaned value merely because the comparison ignores case. If imported data may contain accidental spaces, trim it before using it as a key:

Dim keyText As String
keyText = Trim$(CStr(Cells(2, 1).Value))

This snippet assumes the value to clean is in cell A2 on the active worksheet. In a larger macro, qualify the worksheet rather than relying on whichever sheet happens to be active. If you need accents, punctuation, or internal whitespace ignored as well, make that normalization explicit and consider whether distinct real-world entries could collapse into the same key.

Dictionary CompareMode Versus Option Compare

VBA also has the module-level statement Option Compare, which controls how certain string comparisons in that module behave. It does not set a dictionary’s CompareMode. A module using Option Compare Text can still create a dictionary that uses binary key comparison unless the dictionary’s own property is set.

Likewise, the worksheet’s case-insensitive behavior for many functions and operations does not automatically carry over to a VBA dictionary. The dictionary is a separate object with its own comparison setting. Set that property directly when the required key behavior matters, rather than relying on settings elsewhere in the workbook or code.

Choosing Between Late and Early Binding

Late binding, as in the example, declares the variable As Object and creates it at runtime. It avoids a project reference and can make a workbook easier to move between Windows computers with the scripting library installed. The trade-offs are fewer editor hints and less compile-time checking of dictionary members.

Early binding declares the specific dictionary type after you add the Microsoft Scripting Runtime reference. It offers IntelliSense and catches some mistakes earlier, but the reference must be available and correctly resolved on the target computer. Either approach can use CompareMode; neither makes keys case-insensitive by itself. The important steps remain choosing the comparison constant and setting it before adding keys.

Excel for the web does not run VBA macros. On Mac, availability and behavior of the Windows scripting component can differ, so do not assume that a workbook using CreateObject("Scripting.Dictionary") will run unchanged in every Mac installation. If cross-platform execution is required, verify the target environment and consider a solution that does not depend on that COM component.

When a Dictionary Is Not the Right Fit

A dictionary is useful when VBA needs fast key-based lookups or a convenient way to collect unique keys. If the task is simply to find a value in a worksheet, a worksheet lookup formula or Excel’s built-in tools may be easier to maintain. If you need case-sensitive matching, keep the default binary mode and test carefully, or implement explicit comparison logic suited to the data.

For data processing in VBA, a dictionary can also be used alongside a worksheet range: read the range into an array, build the dictionary in memory, and write results back in batches. That can be more efficient than repeatedly reading individual cells, but it is an optimization choice rather than a requirement. For a small list, prioritize clear code and predictable duplicate handling over complexity.

Frequently Asked Questions

Is a VBA dictionary case-insensitive by default?
No. A new Scripting.Dictionary defaults to vbBinaryCompare, which distinguishes keys that differ by capitalization. Set CompareMode = vbTextCompare before adding keys if you want case-insensitive string matching.

Can I change CompareMode after adding items?
No. Set it before the first key is added. Trying to change the mode after the dictionary contains data raises an error. If you need to change modes, create a new dictionary and copy the entries using the desired comparison behavior.

Does vbTextCompare convert keys to lowercase?
No. It changes the comparison behavior; it does not rewrite stored keys. The original key spelling remains available, even though lookups with different capitalization can match it.

Will dict.Exists ignore capitalization?
Yes, when the dictionary’s CompareMode is vbTextCompare. With the default binary mode, capitalization differences can make Exists return False.

Does Option Compare Text make dictionary keys case-insensitive?
No. Option Compare affects certain string comparisons in its VBA module, not the dictionary’s key-comparison property. Set dict.CompareMode directly.

Can I use Scripting.Dictionary in Excel for the web or on Mac?
Excel for the web cannot run VBA macros. On Mac, support for the scripting component can vary, so test the exact environment; a Windows workbook that creates a scripting dictionary may need a different approach to run across platforms.

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.