“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 Integerbut never callReDim, any attempt to accessarr(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
iis 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:
- 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. - Verify object names: For collections like
SheetsorWorkbooks, ensure the name you’re using matches exactly (case-insensitive, but spelling matters). UseDebug.Print Sheets.Countto see how many sheets exist. - Add error handling: Temporarily wrap the code in a
On Error Resume Nextblock and print the index to see what value is causing the problem. - Use
LBoundandUBoundin loops: Instead of hardcoding loop limits, always useFor 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
LBoundandUBoundin all loops that iterate over arrays. - Check if an object exists before referencing it, especially sheets, workbooks, and named ranges.
- Use
Option Explicitto 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
- Excel Template for Snowball Debt Payoff
- Excel Template for Paying Off Debt: Free & DIY
- Excel Template for Personal Expense Tracking
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Filter Multiple Criteria: Examples and CodeTo filter by multiple criteria in Excel VBA, use the range’s AutoFilter method: apply one criterion per field…
- VBA Sort Table by Column: Code and Common FixesTo sort an Excel table by one column with VBA, get the table and column by name, clear…
- Basic For Loop in VBA: Syntax, Examples, and Usage GuideA basic For loop in VBA repeats a block of code a set number of times. Write For…
- Import Multiple Files Using VBA in Excel: A Practical GuideYes. In Excel for Microsoft 365 on Windows, VBA can loop through files in a selected folder, open…
- VBA Code to Disable Copy and Paste in ExcelIn Excel for Microsoft 365 on Windows, VBA can intercept common copy, cut, and paste shortcuts such as…
- Excel VBA Insert Row: Examples and Safe MethodsTo insert a worksheet row with Excel VBA, use Rows(5).Insert Shift:=xlDown; this inserts a blank row above row…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.