How to Fix Runtime Error 9: Subscript Out of Range in Excel VBA

Runtime Error 9, also known as “Subscript Out of Range,” is a common error in Excel VBA (Visual Basic for Applications). It occurs when your code tries to access an array or collection element using an index that is outside the valid range of that array or collection. This comprehensive guide will help you understand the causes of this error and provide step-by-step solutions to resolve it.

Understanding the “Subscript Out of Range” Error

This error essentially means that VBA is trying to find something at a specific location (the subscript) within a data structure, but that location doesn’t exist. Imagine trying to get the tenth item from a list that only has five items – that’s essentially what’s happening.

Common scenarios include:

  • Incorrect Array Index: Accessing an array element with an index that is less than the lower bound or greater than the upper bound of the array.
  • Misspelled Sheet Name: Referencing a worksheet by a name that doesn’t exist or contains a typo.
  • Non-Existent Workbook: Attempting to access a workbook that is not open or whose name is misspelled.
  • Empty Collection: Trying to access an element from an empty collection (e.g., a collection of worksheets).
  • Using an Uninitialized Variable: Using a variable that has not been properly assigned a value before attempting to access it as an array or collection.

Troubleshooting and Solutions

Here are several strategies to identify and fix Runtime Error 9 in your VBA code:

1. Check Array Boundaries

The most frequent cause is an array index that is out of bounds. Ensure that the index you are using to access the array is within the valid range. Use LBound() and UBound() functions to determine the lower and upper bounds of the array, respectively.

Dim MyArray(1 To 10) As Integer
Dim i As Integer

For i = LBound(MyArray) To UBound(MyArray)
    MyArray(i) = i * 2
Next i

In this example, the loop iterates from the lower bound (1) to the upper bound (10) of MyArray, ensuring that all elements within the array are accessed correctly.

2. Verify Worksheet Names

If your code references worksheets by name, double-check that the names are spelled correctly and that the worksheets actually exist in the workbook. Worksheet names are case-insensitive, but any other discrepancies will cause an error.

Dim ws As Worksheet

'Correct way
Set ws = ThisWorkbook.Worksheets("Sheet1")

'Incorrect way (typo)
'Set ws = ThisWorkbook.Worksheets("Sheettt1") 'This would cause a runtime error 9 if "Sheettt1" doesn't exist

ws.Range("A1").Value = "Hello"

Using the Immediate Window (Ctrl+G in the VBA editor) to print the names of the sheets can also help.

For Each ws In ThisWorkbook.Worksheets
    Debug.Print ws.Name
Next ws

3. Confirm Workbook Existence

When accessing workbooks, ensure they are open and that you have the correct workbook name. Use Workbooks.Open to open a workbook if necessary.

Dim wb As Workbook

'Correct way
Set wb = Workbooks("MyWorkbook.xlsx")

'Incorrect way (workbook not open or name incorrect)
'Set wb = Workbooks("MyWrongWorkbook.xlsx") 'Error if the workbook is not open

wb.Sheets("Sheet1").Range("A1").Value = "Data"

4. Check for Empty Collections

Before attempting to access an element from a collection, ensure that the collection is not empty. You can use the Count property to check the number of elements in the collection.

Dim ws As Worksheet

If ThisWorkbook.Worksheets.Count > 0 Then
    Set ws = ThisWorkbook.Worksheets(1)
    ws.Range("A1").Value = "First Sheet"
Else
    MsgBox "No worksheets exist in this workbook."
End If

5. Initialize Variables

Always initialize your variables before using them, especially when dealing with arrays or collections. Uninitialized variables can cause unexpected behavior and lead to Runtime Error 9.

Dim MyArray() As Integer

'Correct way: Properly Dimensioning the Array
ReDim MyArray(1 To 5)

'Incorrect way: Using an Uninitialized Array
'MyArray(1) = 10 'This line would cause the error if the array is not dimensioned

MyArray(1) = 10

6. Use Error Handling

Implement error handling in your VBA code to gracefully handle potential errors, including Runtime Error 9. This can prevent your code from crashing and provide more informative error messages.

On Error GoTo ErrorHandler

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("NonExistentSheet")

Exit Sub

ErrorHandler:
    MsgBox "Error: " & Err.Description

7. Debugging Techniques

  • Step Through Your Code: Use the F8 key in the VBA editor to step through your code line by line. This allows you to observe the values of variables and identify the exact line where the error occurs.
  • Use Breakpoints: Insert breakpoints in your code (by clicking in the left margin of the code window) to pause execution at specific lines. This allows you to examine the state of your application at those points.
  • Watch Window: Add variables to the Watch Window to monitor their values as your code executes. This can help you identify when a variable’s value becomes invalid.

Conclusion

Runtime Error 9: “Subscript Out of Range” can be frustrating, but by understanding the common causes and applying the troubleshooting techniques outlined in this guide, you can effectively resolve it. Always double-check array bounds, worksheet names, workbook existence, and ensure that your variables are properly initialized. Implementing error handling and using debugging techniques will also help you identify and fix this error more efficiently, leading to more robust and reliable VBA code in Excel.