Excel VBA Integer Subscript Out of Range Fix

VBA & macros · Updated November 22, 2026

“Integer subscript out of range” in Excel VBA means your code tried to access an array element or collection item that doesn’t exist — usually because the index is negative, too large, or the array hasn’t been sized yet. The error message is “Run-time error ‘9’: Subscript out of range,” and it appears when you reference something like MyArray(5) when MyArray only has 3 elements, or Sheets("Data") when no sheet named “Data” exists. Fixing it involves checking your array bounds, verifying object names, and using safer coding practices like LBound and UBound.

What Does “Subscript Out of Range” Mean?

In VBA, a “subscript” is the index number you use to access an element in an array or a member of a collection. For example, MyArray(0) uses subscript 0. The error occurs when that index falls outside the valid range of indices. For a fixed-size array declared as Dim MyArray(1 To 5) As Integer, valid subscripts are 1 through 5. Accessing MyArray(6) or MyArray(0) will trigger the error. Similarly, collections like Worksheets or Range have their own valid keys (names or numbers); referencing a non-existent key causes the same error.

Common Causes in Real Code

Several typical mistakes lead to this error:

  • Using an uninitialized dynamic array: If you declare Dim arr() As Integer but never call ReDim, any attempt to access arr(0) fails because the array has no dimensions.
  • Off-by-one errors: VBA arrays are often declared with a lower bound of 0 (unless you specify otherwise). If you think the array is 1-based and loop from 1 to UBound(arr), you might skip the first element or go past the last.
  • Mistyped sheet or workbook names: Sheets("Sheet1") fails if the sheet is actually named “Data” or if it’s a chart sheet.
  • Using a variable that holds an invalid index: If a variable like i is 0 but your array is 1-based, you get the error.
  • Empty collections: Trying to access Worksheets(1) when there are no worksheets is rare, but possible in a corrupted workbook.

How to Debug the Error

When you encounter the error, the VBA editor highlights the offending line. To fix it, follow these steps:

  1. Check the array’s bounds: In the VBA editor, hover over the array variable to see its current dimensions, or use the Immediate window (press Ctrl+G) and type ?UBound(MyArray) and ?LBound(MyArray) to see the valid range.
  2. Verify object names: For collections like Sheets or Workbooks, ensure the name you’re using matches exactly (case-insensitive, but spelling matters). Use Debug.Print Sheets.Count to see how many sheets exist.
  3. Add error handling: Temporarily wrap the code in a On Error Resume Next block and print the index to see what value is causing the problem.
  4. Use LBound and UBound in loops: Instead of hardcoding loop limits, always use For i = LBound(arr) To UBound(arr) to avoid off-by-one errors.

Practical Example: Fixing an Array Index Error

Suppose you have this code that fails:

Sub BadArray()
    Dim nums(1 To 3) As Integer
    Dim i As Integer
    For i = 0 To 3
        nums(i) = i * 10
    Next i
End Sub

Here, nums(0) is invalid because the array is 1-based. The fix is to start the loop at 1:

Sub GoodArray()
    Dim nums(1 To 3) As Integer
    Dim i As Integer
    For i = LBound(nums) To UBound(nums)
        nums(i) = i * 10
    Next i
End Sub

Using LBound and UBound makes the code robust even if you later change the array’s bounds.

How to Avoid the Error with Dynamic Arrays

Dynamic arrays are powerful but require careful handling. Always ReDim before use. For example:

Sub DynamicArray()
    Dim data() As Variant
    Dim i As Integer
    ReDim data(1 To 5)
    For i = 1 To 5
        data(i) = i * 2
    Next i
    ' Safe access
    Debug.Print data(3)
End Sub

If you need to resize while preserving data, use ReDim Preserve. Note that ReDim Preserve can only change the last dimension of a multi-dimensional array.

Collection References: Sheets, Ranges, and Workbooks

The same error occurs when referencing objects by name or index. For instance:

Sub AccessSheet()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Data")  ' Error if no sheet named Data
End Sub

To avoid this, check if the sheet exists before accessing it. A simple function:

Function SheetExists(sheetName As String) As Boolean
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = ThisWorkbook.Sheets(sheetName)
    On Error GoTo 0
    SheetExists = Not ws Is Nothing
End Function

Then use If SheetExists("Data") Then .... Similarly, when accessing a range like Range("A1"), ensure the worksheet is active or fully qualified, e.g., Worksheets("Sheet1").Range("A1").

Frequently Asked Questions

Why does my code work in one Excel version but not another?

Different Excel versions may have different default lower bounds for arrays if you use the Option Base statement, or if you rely on implicit worksheet names. The error is not version-specific, but the cause might be. Always explicitly declare array bounds and fully qualify sheet references to ensure compatibility.

Can I use On Error Resume Next to ignore this error?

You can, but it’s risky because it hides other errors too. It’s better to fix the root cause. If you must use it, do so only around the specific line and check a flag afterward. For example, to test if a sheet exists, use On Error Resume Next then check if the object is Nothing.

What is the difference between “Subscript out of range” and “Object variable not set”?

“Subscript out of range” (Error 9) means the index or key doesn’t exist. “Object variable not set” (Error 91) means you have a variable declared as an object but you haven’t assigned it with Set. They often occur together: if you try to access an array element of an uninitialized array, you might get Error 9 first.

How do I fix the error when using Application.WorksheetFunction.VLookup?

VLookup itself doesn’t cause this error, but if you pass an array as the lookup table and the row index is out of bounds, you might see it. For example, WorksheetFunction.Index(MyArray, 10) fails if MyArray has fewer than 10 rows. Always check UBound before using an index.

Does this error occur in VBA for Access or Word?

Yes, the same error appears in any VBA host because it’s a language-level error. The causes are identical: invalid array indices or collection keys. The debugging steps are the same.

Preventing the Error with Good Coding Habits

To minimize future occurrences, adopt these practices:

  • Always declare array bounds explicitly (e.g., Dim arr(1 To 10)) unless you have a reason not to.
  • Use LBound and UBound in all loops that iterate over arrays.
  • Check if an object exists before referencing it, especially sheets, workbooks, and named ranges.
  • Use Option Explicit to force variable declaration, which catches typos in variable names that could lead to invalid indices.
  • Test with small data sets and use the Immediate window to inspect variable values when debugging.

By understanding the root cause and applying these techniques, you’ll eliminate “Subscript out of range” errors from your VBA projects. Remember to save a copy of your workbook before running any macro, as macros cannot be undone with Ctrl+Z.

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.