Replace Select Case with Scripting.Dictionary in VBA?

Yes—replace Select Case with a VBA Scripting.Dictionary when you need to look up a result by key, especially when the mapping is long, changes often, or is built from data. Keep Select Case for a short, fixed set of branches or when each case must run different code: a dictionary stores key-value pairs, not executable branches.

When a dictionary is a good replacement

A Select Case statement lists alternatives directly in code. That is easy to read when there are only a few stable options. A dictionary instead associates each unique key with an item, such as a description, rate, worksheet name, or status. It is useful when many inputs map to data values, when mappings are loaded from a worksheet or file, or when code needs to add and remove mappings while it runs.

For example, if codes such as A, H, and X each map to a category name, the dictionary can hold those pairs and retrieve the category for a supplied code. That can make the lookup portion of a large routine easier to maintain than a long list of cases. But if A should run one procedure, H should perform several unrelated operations, and X should exit early, the cases represent control flow, not just data. A dictionary is not a direct substitute for that logic.

What Select Case does differently

Select Case evaluates an expression and executes the statements in the matching branch. It can handle ranges and multiple values, and its branches can contain any valid VBA statements. For instance, a case can update cells, call a procedure, or return from a function. The mapping and the action are together in one place.

A dictionary separates the lookup table from the code that uses the result. A key has one associated item at a time; it does not automatically run a block of VBA. You can store a label or other value and then use that value in later logic, but if the next step still needs different actions, some branching may remain. This distinction is the most important test when deciding whether a conversion will actually simplify a routine.

Build a late-bound dictionary

This example maps short codes to text. Late binding declares the variable as Object and creates the dictionary at run time, so the project does not need a reference to the Microsoft Scripting Runtime just to compile. In the Visual Basic Editor, use Insert > Module, then place the procedure in a standard module.

Option Explicit

Public Sub DemoDictionaryLookup()
    Dim rules As Object
    Dim x As String
    Dim result As String

    ' Create a Scripting.Dictionary without a project reference.
    Set rules = CreateObject("Scripting.Dictionary")

    ' Set text comparison before adding any keys.
    rules.CompareMode = vbTextCompare
    rules.Add "A", "Approved"
    rules.Add "H", "On hold"
    rules.Add "X", "Rejected"

    x = "a"
    If rules.Exists(x) Then
        result = CStr(rules.Item(x))
    Else
        result = "Unknown code"
    End If

    MsgBox result
End Sub

With this example, the result is Approved: vbTextCompare makes the lookup treat the key a as matching the stored key A. The Exists check matters because an input not present in the dictionary needs an explicit fallback. Before running any macro, save a copy of the workbook; macro changes cannot be undone with Ctrl+Z.

How to convert a simple case statement

First identify whether each branch merely assigns a result. If it does, create one dictionary entry per input and replace the cases with a lookup and a defined missing-key result. For example, a case that assigns a department name can become a code-to-department dictionary. Keep the fallback explicit: returning an empty string, displaying a warning, or treating an unknown code as an error are different choices.

Next decide where the dictionary should be built. Creating it inside a small one-off procedure is straightforward. If a frequently called function rebuilds the same large mapping every time, construction overhead may undermine the benefit; build it once and reuse it, or keep the mapping in a worksheet table if users need to maintain it. Reuse requires care: clear or recreate the object when the source mappings change so lookups do not use stale values.

For a dictionary populated from cells, validate the source data as it is loaded. Decide what blank keys mean, whether spaces should be trimmed, and what to do with duplicate codes. These choices are part of the lookup rules, not details the dictionary can infer for you.

Dictionary behavior and duplicate keys

Dictionary keys are unique. Calling Add for a key that already exists raises an error rather than creating a second entry. That is useful when duplicates indicate bad source data, but it can interrupt a macro if duplicates are expected. Check with Exists before adding when you want to keep the first value, or assign through Item when the intended rule is to replace the existing value. Choose the policy deliberately: silently overwriting a value can conceal inconsistent input.

Set CompareMode before adding entries if text keys should be compared without regard to letter case. The default comparison is binary, so uppercase and lowercase strings may be distinct. Changing the comparison mode after keys have been added can raise an error. Text comparison does not automatically remove leading or trailing spaces; normalize values separately if the source data can contain them.

A dictionary can also store numeric values, objects, or other Variant-compatible items, not only text. The key and item are different concepts: use the key to find an entry and the item as the associated value. For a missing key, test Exists before reading its item so the routine can handle the absence intentionally.

Performance and maintainability trade-offs

A dictionary is commonly chosen for convenient key-based lookups, especially with many entries or mappings that are assembled dynamically. But replacing a short Select Case does not automatically make a macro faster. Setup has a cost, and a tiny fixed mapping may be clearer as cases. The overall speed also depends on what the macro does around the lookup, such as reading cells one at a time or repeatedly creating objects.

Maintainability is often the stronger reason to change. A data-driven mapping can be edited without adding another branch, and a worksheet-based source can let a workbook owner update labels without changing VBA. On the other hand, a long sequence of Add calls is still hard-coded data. Moving the values into a table may be more useful than changing the data structure if non-programmers must maintain them.

Keep the mapping visible and document its assumptions. If an unknown key is a normal possibility, give it a sensible fallback. If it signals a defect, report the key and stop or raise a controlled error rather than returning a plausible but incorrect value.

Early binding, references, and platform support

For early binding in Excel for Microsoft 365 on Windows, open the Visual Basic Editor, choose Tools > References, select Microsoft Scripting Runtime, and declare the variable as Scripting.Dictionary. Early binding provides more specific type information and can offer editor help and compile-time checking. The trade-off is that another computer may not have the expected reference available, which can cause a missing-reference problem.

Late binding with CreateObject avoids that compile-time reference, but it does not make the component available everywhere. Scripting.Dictionary is principally a Windows scripting component, so do not assume a workbook using it will work unchanged in Excel for Mac. Mac support and behavior can depend on the Excel and macOS environment; test on the actual target Mac rather than relying on Windows results. Excel for the web does not run VBA macros. If a workbook must work across platforms, consider a plain Select Case, a collection or array-based lookup, or a worksheet table with a compatible approach.

On Windows, Alt+F11 opens the Visual Basic Editor. On Mac, Option+F11 is the corresponding shortcut on many keyboards; some require Fn as well. Save a macro-enabled workbook as .xlsm if it must retain VBA. Saving as a macro-free workbook format removes the VBA project.

Common conversion mistakes

Expecting stored values to run code: a dictionary lookup returns an item; it does not execute a matching block.
Ignoring missing keys: check Exists and choose a fallback before attempting to use a lookup result.
Adding duplicates blindly: decide whether duplicates should be rejected, ignored, or used to replace the earlier item.
Setting text comparison too late: configure CompareMode before inserting keys.
Assuming case-insensitive means cleaned: comparison mode does not trim spaces or correct inconsistent spelling.
Assuming portability: a workbook that works on Windows may still need a different implementation on Mac or in Excel for the web.

Frequently Asked Questions

Is a Scripting.Dictionary faster than Select Case in VBA?
Not in every situation. A dictionary is convenient for many key-value lookups, but creating and populating it has overhead. For a small, fixed set of alternatives, Select Case may be simpler and sufficiently fast. Measure the real workload if performance is the reason for changing.

Can a dictionary replace Select Case when each case runs a different macro?
Not directly. A dictionary stores data associated with keys; it does not store and execute ordinary VBA code blocks. You can use a dictionary to choose a label or identifier and then handle that result, but that may leave a Select Case or another dispatch mechanism in place.

Do I need to add a reference for Scripting.Dictionary?
Not when using late binding with Dim rules As Object and CreateObject("Scripting.Dictionary"). Early binding requires selecting Microsoft Scripting Runtime under Tools > References in the Visual Basic Editor. A reference can improve editor support but may need to be resolved on other computers.

How do I make dictionary keys case-insensitive?
Set CompareMode to vbTextCompare immediately after creating the dictionary and before adding any keys. This affects text-key comparisons; it does not trim spaces or make different spellings equivalent.

Will Scripting.Dictionary work in Excel for Mac?
Do not assume it will work just because it runs in Excel for Windows. Test the specific Mac environment and workbook. Excel for the web does not execute VBA; for cross-platform use, choose an alternative that fits the target versions, such as Select Case or a table-based lookup design.

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.