Excel VBA Run-Time Error 9 in a For Loop

VBA & macros · Updated November 19, 2026

Excel VBA run-time error 9, “Subscript out of range,” means code tried to access an array element or collection item that does not exist. In a For loop, the usual cause is that the loop’s index does not match the valid bounds of an array, worksheet collection, or workbook collection; inspect the line that stops and check the value used as its index.

What Error 9 Means in a Loop

A For loop repeats a block of code while its counter moves through a range of values. Error 9 is not normally a complaint about the loop syntax or the number of repetitions. It occurs when a statement inside the loop uses the current counter to request an item outside a collection or array’s valid range.

For example, Excel’s Worksheets collection is numbered starting at 1. If a loop starts its counter at 0 and then uses that value in Worksheets(i), the first lookup is invalid. Arrays can have different lower and upper bounds, so a loop that assumes every array starts at 0 or 1 can fail too. The error message describes the invalid lookup, not necessarily the line where the loop begins.

Other VBA errors can occur in loops, but they have different meanings. A type mismatch, for example, points to an incompatible value or variable type. Error 9 specifically directs attention to a subscript: an index, item name, or other identifier used to retrieve something.

Find the Exact Failing Reference

When the error appears, choose Debug in the error dialog. The Visual Basic Editor highlights the statement that failed. Read that whole statement and identify every indexed expression, such as arr(i), Worksheets(i), or Workbooks(bookName). The loop counter may be responsible, but the failing expression could instead use another variable or a fixed name.

To see how the value changes, run the macro from the Visual Basic Editor and use Debug > Step Into to proceed one statement at a time. The View > Locals Window can show current variable values; the View > Immediate Window is useful for checking an expression while execution is paused. On Windows, F8 is the common Step Into shortcut. If the function key row on a Mac is configured for hardware controls, the equivalent may require Fn+F8; the menu command is an alternative.

Check the counter on the failing iteration, not just its initial value. A loop might run correctly for several passes and then reach an index that is one too large. If the highlighted line contains several lookups, inspect each one separately; the first invalid lookup on that line stops execution.

Check Array Bounds, Not Assumptions

VBA arrays do not all share the same lower bound. The declaration, the way the array was created, and the data source can affect its bounds. A fixed array declared from 1 to 3 has valid indexes 1, 2, and 3. A loop from 0 to 3 would fail when it reaches 0; a loop from 1 to 4 would fail at 4. Use LBound and UBound to get the actual lower and upper limits rather than guessing.

For example, this fixed-array loop uses the bounds it declares:

Sub ReadValues()
    Dim values(1 To 3) As Long
    Dim i As Long

    ' Visit each valid element in the array.
    For i = LBound(values) To UBound(values)
        Debug.Print values(i)
    Next i
End Sub

The values are initially zero because the array is declared as Long, but the loop’s indexing is valid. For an array with a different lower bound, the same loop pattern still follows its actual limits. This is more reliable than hard-coding the bounds in several places and then forgetting to update one when the array changes.

A dynamic array declared with empty parentheses, such as Dim values() As Variant, has not yet been allocated. Calling UBound(values) before assigning it a size can itself produce error 9. Allocate it with ReDim or assign data before looping. Also remember that IsArray(values) only tells you that the variable is an array; it does not prove that a dynamic array has been initialized.

Check Worksheet and Workbook References

Worksheet and workbook lookups are another frequent source of error 9. An item requested by name must exist in the collection being searched, and an item requested by number must be within that collection’s current count. A misspelled sheet name, a renamed tab, or a workbook that is not open can make a lookup fail.

For example, the following loop has an invalid first index because worksheet collections are one-based:

Sub ListSheetNames()
    Dim i As Long

    ' Worksheet indexes begin at 1, not 0.
    For i = 0 To ThisWorkbook.Worksheets.Count
        Debug.Print ThisWorkbook.Worksheets(i).Name
    Next i
End Sub

Change the start to 1, or avoid numeric indexing when it is not needed:

Sub ListSheetNamesSafely()
    Dim ws As Worksheet

    ' Visit each worksheet without calculating its index.
    For Each ws In ThisWorkbook.Worksheets
        Debug.Print ws.Name
    Next ws
End Sub

ThisWorkbook means the workbook containing the running VBA code. ActiveWorkbook means the workbook currently active in Excel, which might be a different file. Using the wrong qualifier can send code to the wrong workbook, where the expected sheet is absent. Use the workbook reference that matches your intention, and qualify collection references rather than relying on whichever workbook happens to be active.

Worksheets contains worksheet tabs, while Sheets can also include chart sheets. A numeric index into Sheets may therefore refer to a different kind of sheet than expected. If code needs cells or worksheet-specific properties, use Worksheets and declare the loop variable as a Worksheet.

Review Loop Limits and Changing Collections

Compare the loop’s start and end values with the object being indexed. A common off-by-one mistake is to use 0 through Count for a one-based collection, or to use 1 through Count + 1. For collections, an index from 1 through Count is generally the intended numeric pattern. For arrays, use their actual bounds instead.

Also check whether the collection changes while the loop runs. If code deletes a worksheet or removes an item during a forward numeric loop, later indexes can refer to different items or exceed the new count. When removing collection members by index, a reverse loop is often safer because deleting a higher-numbered item does not shift the lower indexes that remain to be processed. If the loop only reads items, this issue is less likely.

A loop’s Step direction should match its start and end values. A negative step is appropriate when counting downward, such as from a high array index to a low one. A mismatched direction may mean the loop does not run as intended, though it does not by itself explain error 9; error 9 still requires an invalid lookup in the code that executes.

Use a Practical Debugging Checklist

Work through these checks in order. They help distinguish a bad loop limit from a wrong name or a workbook-context problem:

  • Choose Debug and identify the exact highlighted statement.
  • Write down the index or name used in each array or collection lookup on that line.
  • For an array, inspect its declaration or allocation and compare the index with LBound and UBound.
  • For a collection, compare a numeric index with its valid indexing convention and current Count.
  • For a named item, check spelling, spaces, workbook qualification, and whether the named item exists at that moment.
  • Check whether earlier code resized an array, closed a workbook, renamed a sheet, or removed a collection item.
  • Step through the loop again and confirm that each lookup stays within valid bounds.

If the failing line is long, temporarily split it into smaller statements and inspect the result of each lookup. For example, assign a worksheet reference to a Worksheet variable before reading a cell from it. This makes it easier to tell whether the sheet lookup or a later operation is the problem.

Prevent Repeat Errors

Prefer bounds-aware loops over constants that duplicate an array’s size. Use For Each for collections when you do not need the item number, and use explicit workbook references when working with more than one open file. If a sheet name is expected to vary, validate that it exists before using it instead of assuming the lookup will succeed.

It can also help to keep the collection lookup and the loop counter visibly connected. A counter named sheetIndex is clearer than a generic i when it indexes worksheets, and a variable named arrayIndex makes an array lookup easier to review. Clear naming does not prevent an invalid index, but it makes mismatched limits easier to spot during debugging.

Before running a macro that changes or deletes workbook content, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z. In Excel for Microsoft 365 on Windows, open the Visual Basic Editor from Developer > Visual Basic; if the Developer tab is hidden, enable it through File > Options > Customize Ribbon. Excel for the web cannot run or edit VBA macros, so use the desktop application. On Mac, the VBA Editor is also available from the Developer tab when that tab is enabled, though the ribbon layout and keyboard function-key behavior can differ.

Frequently Asked Questions

Does error 9 mean the For loop syntax is wrong?
No. The loop can be valid VBA while a statement inside it requests an array element, worksheet, or workbook that does not exist. Start with the highlighted line and check the lookup’s index or name.

Why does Worksheets(0) cause error 9?
Worksheet collection indexes begin at 1, so index 0 is outside the collection. Use indexes from 1 through the worksheet count, or use a For Each loop when you do not need a numeric index.

Can UBound cause error 9?
Yes. Calling UBound on a dynamic array that has not been allocated can raise error 9. Allocate the array before checking its bounds, and do not assume that IsArray confirms it has been initialized.

Why does my macro work on one workbook but fail on another?
The second workbook may have a different sheet name or structure, or the code may be using ActiveWorkbook when it should use ThisWorkbook, or vice versa. Confirm which workbook each reference targets and whether the requested item exists there.

Is error 9 the same as “Subscript out of range”?
Yes. “Subscript out of range” is the standard description for VBA run-time error 9. It indicates that an index or named reference points outside the items available to that expression.

Bottom Line

When error 9 appears in a For loop, focus on the expression that uses the loop counter or another index. Compare array indexes with LBound and UBound, check collection indexes and names, and verify the workbook or worksheet being referenced. Stepping through the failing iteration usually reveals whether the fix is a corrected loop limit, a safer For Each loop, or a more precise object reference.

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.