VBA Subscript Error Handling: Causes, Checks, and Fixes

In VBA, a subscript error usually means Error 9, “Subscript out of range”: code asked for an array element or collection item that does not exist. Handle it by checking the relevant bounds or name before using it, and use an error handler for failures that cannot be checked in advance; avoid using On Error Resume Next to hide errors across a whole procedure.

What VBA Error 9 means

A subscript is the index or key used to select an item from a group. In VBA, that group might be an array, a worksheet collection, or another collection. Error 9 tells you the requested item is outside the available range or cannot be found. It does not tell you which kind of reference failed, so the most useful clue is the line VBA highlights when you choose Debug in the error dialog.

For example, Worksheets("Summary") requests a worksheet named Summary, while values(4) requests the fourth element according to that array’s bounds. If the worksheet is absent, or the array has no element at that index, the reference can raise Error 9. The right fix depends on the object being indexed: confirm a sheet name, inspect array bounds, or validate a collection index.

Error 9 is different from a type mismatch, which commonly means a value has the wrong kind of data, and from an object variable error, which can mean an object was never assigned. Fixing the message without checking the failing reference can mask the actual problem.

Common causes in Excel VBA

These situations are frequent sources of Error 9 in Excel macros:

A worksheet or workbook name is wrong. A renamed, deleted, or misspelled tab will not match the string in the code. Spaces and punctuation matter. Workbook references can also point to a different file than intended.
An array index is outside its bounds. An array declared with bounds 1 to 5 does not have an element at index 0 or 6. The same issue can occur in a multidimensional array if the wrong dimension or index is used.
A collection index is invalid. Many VBA collections, including a workbook’s Worksheets collection, use item numbers starting at 1. Asking for item 0 or for an item beyond the collection’s count can fail.
A dynamic array has not been allocated. A dynamic array declared with empty parentheses has no usable elements until it has been sized with ReDim. Code that assumes elements exist before allocation can fail.
The code assumes the workbook state has not changed. A user may close a workbook, rename a sheet, or activate another workbook between steps. An unqualified reference may then resolve against an unintended workbook.

These causes are related, but they are not interchangeable. For instance, changing an array’s starting index will not fix a missing worksheet; identifying the exact failing line is the first step.

How to find the failing reference

When the error dialog appears during a macro run, choose Debug to stop on the statement that raised the error. Read the full statement and identify each indexed or named object on it. A line such as Debug.Print Worksheets("Data").Cells(rowNumber, 2).Value includes more than one possible issue: the sheet lookup, the row index, and the range reference. Test those parts separately rather than guessing.

In the Visual Basic Editor, use the Immediate window to inspect values while execution is paused. You can print a variable with ? rowNumber, for example, or inspect the relevant collection’s Count. For an array, check its lower and upper bounds with LBound and UBound. If a dynamic array might not have been allocated, investigate where it is dimensioned before calling a bounds function.

Also check which workbook the code is using. ThisWorkbook means the workbook that contains the running VBA project; ActiveWorkbook means the workbook currently active in Excel. They can be different, especially when code is stored in an add-in or personal macro workbook. Qualifying a sheet reference with the intended workbook often exposes a hidden assumption.

Prevent array index errors with bounds

Do not assume arrays begin at zero or one. The default lower bound is generally zero, but a module can specify Option Base 1, and an array can be explicitly dimensioned with chosen bounds. The dependable approach is to use the array’s actual bounds when iterating. For a multidimensional array, specify the dimension in the bounds functions.

For example, this macro explicitly creates three elements and loops only over valid indexes. Before running any macro, save a copy of the workbook; macro changes cannot be undone with Ctrl+Z.

Sub PrintArrayItems()
    Dim items() As String
    Dim i As Long

    ReDim items(1 To 3)
    items(1) = "North"
    items(2) = "East"
    items(3) = "West"

    For i = LBound(items) To UBound(items)
        Debug.Print items(i)
    Next i
End Sub

Here, LBound(items) returns 1 and UBound(items) returns 3, so the loop visits each assigned element. If the array is two-dimensional, use forms such as LBound(matrix, 1) and UBound(matrix, 1) for its first dimension, and use dimension 2 for its second. A hard-coded loop limit can become wrong when the array size changes.

For a dynamic array, make sure the code reaches the correct ReDim statement before accessing elements. If a procedure may receive an unallocated array, design and check that condition explicitly rather than assuming a bounds call will always succeed.

Handle missing worksheets safely

Worksheet references often fail because the code assumes a tab exists. Use a workbook-qualified reference and provide a handler when a missing sheet is an expected possibility. The following example reports the problem and exits instead of continuing with an invalid reference.

Sub ReadDataSheet()
    Dim ws As Worksheet
    Dim errorNumber As Long
    Dim errorText As String

    On Error GoTo ErrorHandler

    Set ws = ThisWorkbook.Worksheets("Data")
    Debug.Print ws.Range("A1").Value
    Exit Sub

ErrorHandler:
    errorNumber = Err.Number
    errorText = Err.Description

    If errorNumber = 9 Then
        MsgBox "The Data worksheet was not found in this workbook."
    Else
        MsgBox "Error " & errorNumber & ": " & errorText
    End If
End Sub

The procedure copies the error number and description before displaying a message, then distinguishes Error 9 from other failures. The message does not make the sheet appear; it tells the user what condition prevented the work. If a missing sheet is normal and should not interrupt the macro, check for its existence before trying to use it, or use a small lookup function that returns Nothing when no matching sheet is found.

Remember that ThisWorkbook is not always the workbook you want. If the macro is stored in an add-in but should operate on the user’s open workbook, pass the intended workbook into the procedure or deliberately use ActiveWorkbook. Do not switch between these references casually: an active-workbook change can make code target the wrong file without producing an obvious error.

Use On Error without hiding bugs

On Error GoTo Label activates a handler in the current procedure. It is a good fit when a procedure needs to report an unexpected failure, release resources, or exit cleanly. A handler should be specific enough to distinguish a known condition such as a missing sheet from other errors, and it should not silently claim that the operation succeeded.

On Error Resume Next tells VBA to continue at the statement after the one that failed. It can be appropriate for a very narrow, anticipated check, but it is risky across many lines: unrelated mistakes may also be skipped, leaving partially updated data. If you use it, limit its scope, inspect Err.Number immediately after the operation, and restore normal handling with On Error GoTo 0.

On Error GoTo 0 disables the active error handler in that procedure; it does not mean “continue at the next line after an error.” In a handler, save any error details you need before another operation can change them. For a known condition that can be checked directly, a normal test is often clearer and safer than deliberately causing an error and suppressing it.

Choose between checking and trapping

Use a direct check when the condition is predictable and easy to inspect. For example, check a collection’s Count before using a numeric item index, or loop through worksheets to find a name before accessing its properties. This makes the expected case visible in the code and avoids relying on an error as ordinary program flow.

Use an error handler when an operation can fail for reasons that are difficult to rule out in advance, or when you need a central cleanup and reporting path. Opening a file, interacting with an external system, or processing a sequence of operations can involve changing conditions. A handler can capture the failure and stop the procedure cleanly, but it should preserve useful diagnostic information and avoid concealing the error.

A practical decision process is:

Identify the exact statement and the object or index it references.
Ask whether the condition can be tested before the statement, such as a name or count.
If it can, validate it and choose a clear response for the missing case.
If it cannot, use a scoped handler, record the error details, and exit or recover deliberately.
Test both the expected case and the failure case, including renamed sheets or changed array sizes where relevant.

Common mistakes and misconceptions

One common mistake is treating On Error Resume Next as a fix. It changes what VBA does when an error occurs; it does not make a bad index valid or restore a missing worksheet. A macro may appear to finish while skipping important work. A better fix validates the reference or routes the error to a handler that explains the failure.

Another misconception is that every array starts at zero. Its lower bound depends on how it was declared and the module’s settings, so use LBound rather than assuming. Similarly, do not assume every collection is zero-based: common Excel object collections such as worksheets are indexed from 1.

Finally, a worksheet tab that looks correct may belong to another workbook, or the workbook containing the code may not be the workbook the user expects. Fully qualify references and verify the workbook object before investigating less likely causes. These checks are useful even when the macro works on one computer, because a different workbook state can reveal assumptions that were previously hidden.

Frequently Asked Questions

What is VBA runtime error 9?
Error 9 is “Subscript out of range.” It means VBA could not find the requested array element or collection item, often because an index is invalid or a workbook or worksheet name does not match.

How do I fix Subscript out of range in Excel VBA?
Use Debug to locate the failing statement, then check the array bounds, collection count, or exact workbook and worksheet name used on that line. Correct the reference or handle the missing case before continuing.

Should I use On Error Resume Next for Error 9?
Not as a general fix. It can skip the failing line and hide other problems. Reserve it for a short, expected check, inspect Err.Number, and restore normal handling with On Error GoTo 0.

Why does Worksheets(“Sheet1”) give a subscript error?
The named sheet may not exist in the workbook being referenced, or the code may be looking in a different workbook than intended. Check spelling and qualify the reference with the correct workbook.

How can I prevent an array subscript error?
Allocate dynamic arrays before accessing them, use LBound and UBound to loop over valid indexes, and check the dimension you mean when working with multidimensional arrays.

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.