Run Time Error 9: Subscript Out of Range Fix in VBA (3 Solutions)

Why it happens

When you encounter a runtime error 9 in VBA, it’s usually because your code is trying to access an array, collection, or list element that doesn’t exist—like reaching for a seat in an empty theater. 🎭 The error occurs when VBA can’t find the index (or "subscript") you’re referencing, either because it’s out of bounds or the object itself is empty.

Let’s break down the most common culprits and why they trigger this frustrating roadblock.

🔍 1. Accessing Non-Existent Array Indices

Arrays in VBA are zero-indexed by default, meaning the first element is at position 0, not 1. If your code assumes a 1-based index or tries to access an index beyond the array’s declared size, VBA throws Error 9. For example:

• Problem: Declaring an array with Dim myArray(5) creates 6 elements (indices 0 to 5). Accessing myArray(6) is out of range.
• Why it happens: The ReDim or initial declaration sets an upper bound, but your loop or reference exceeds it. Dynamic arrays (resized with ReDim Preserve) are especially risky if not handled carefully.
• Fix: Always validate indices with UBound() or LBound() before accessing them. For example:

  If index <= UBound(myArray) And index >= LBound(myArray) Then
          ' Safe to access myArray(index)
      End If

📊 2. Empty Collections or Objects

Collections, dictionaries, and objects (like worksheets in Excel) can be empty when your code runs. If you assume they contain data but they don’t, any attempt to access an item by index or key will fail. For instance:

• Problem: A loop iterates through a collection named myCollection, but the collection has 0 items. Trying to access myCollection(1) crashes.
• Why it happens: The collection might be newly created, filtered to zero results, or cleared elsewhere in the code. Similarly, Excel objects like Worksheets("Sheet1") may not exist if the sheet was deleted.
• Fix: Check the collection’s Count or object’s Exists property before accessing it. For example:

  If myCollection.Count > 0 Then
          ' Safe to loop through items
      End If

  Or for Excel objects:

  On Error Resume Next
      Set ws = Worksheets("Sheet1")
      If Err.Number = 0 Then ' Object exists
          ' Proceed with code
      Else
          MsgBox "Sheet not found!"
      End If
      On Error GoTo 0

🔄 3. Off-By-One Errors in Loops

Loops are a prime culprit for Error 9 because they often assume a predictable range of data. A classic mistake is using For i = 1 To 10 when the actual data only has 9 items—or worse, when the data is dynamically generated and shorter than expected.

• Problem: A loop runs from 1 to 10, but your array or collection only has 5 items. Accessing index 6 to 10 triggers the error.
• Why it happens: Hardcoded loop limits ignore real-time data changes. For example, reading a CSV file might return fewer rows than anticipated, or a user-deleted row in Excel throws off your calculations.
• Fix: Use For i = LBound(data) To UBound(data) for arrays or For Each loops for collections. For Excel ranges, use:

  Dim lastRow As Long
      lastRow = Cells(Rows.Count, "A").End(xlUp).Row
      For i = 1 To lastRow ' Dynamic range

🔗 4. Incorrect String or Text Parsing

When splitting strings or parsing text (e.g., CSV data), VBA may return fewer elements than your code expects. For example, splitting "apple,banana" by commas gives 2 items, but your loop assumes 3.

• Problem: Using Split() on a string with fewer delimiters than expected, then trying to access a non-existent index.
• Why it happens: The input data doesn’t match the assumed format. For instance, a missing comma in a CSV row or an empty cell in Excel.
• Fix: Validate the split result’s length before accessing elements:

  Dim myArray As Variant
      myArray = Split("apple,banana", ",")
      If UBound(myArray) >= 1 Then
          ' Safe to use myArray(1)
      End If

How to solve it

Encountering a Run-Time Error 9 in VBA can be frustrating, but the good news is that most issues stem from predictable causes. Below, we’ve mapped out the most common triggers and their fixes—so you can get back to coding without the headache. 🚀

🔍 Cause 1: Empty or Non-Existent Array/Collection

When your code tries to access an element in an array, collection, or dictionary that doesn’t exist (e.g., MyArray(5) when the array only has 3 elements), VBA throws this error. Here’s how to fix it:

• 🔥 Check array bounds: Before accessing an element, verify the array’s size. Use UBound(MyArray) to confirm the last valid index.
• 🍳 Add bounds checking:

  If UBound(MyArray) >= 5 Then
      ' Safe to access MyArray(5)
      Else
      MsgBox "Index out of range!", vbExclamation
      End If

• 👨‍🍳 Use collections safely: If working with collections, check if the item exists first:

  If Not MyCollection Is Nothing Then
      ' Proceed with MyCollection("Key")
      End If

💡 Prevention Tip: Always initialize arrays and collections with known dimensions or populate them dynamically before use. Avoid hardcoding indices!

📊 Cause 2: Incorrect Loop Indexing

Loops like For i = 1 To 10 can overrun array limits if the array only has 5 elements. This often happens when copying data or iterating through ranges.

• ⏰ Adjust loop limits: Use UBound or LBound to set dynamic loop ranges:

  For i = LBound(MyArray) To UBound(MyArray)
      ' Process each element safely
      Next i

• 🔪 Debug with Debug.Print: Add temporary checks to log array sizes during testing:

  Debug.Print "Array size: " & UBound(MyArray)

• 🎯 Handle edge cases: If working with Excel ranges, account for empty cells or filtered data:

  Dim lastRow As Long
      lastRow = Cells(Rows.Count, "A").End(xlUp).Row
      For i = 1 To lastRow
      ' Safe loop for populated cells
      Next i

💡 Prevention Tip: Test loops with small datasets first. Use Option Explicit to catch undeclared variables that might cause off-by-one errors.

🛠️ Cause 3: Dynamic Data Mismatches

Errors often pop up when your code assumes data exists but the source (e.g., worksheet, API, or database) changes. For example, a Range("A1").Value might return empty if the cell is deleted.

• ✨ Validate data existence: Use IsEmpty or check cell values:

  If Not IsEmpty(Range("A1")) Then
      ' Proceed with processing
      Else
      MsgBox "No data found!", vbWarning
      Exit Sub
      End If

• 🌡️ Handle optional parameters: If working with dictionaries or objects, check for Nothing`:

  If MyObject Is Nothing Then
      Set MyObject = New MyClass
      End If

• 📊 Use error handling: Wrap risky operations in On Error Resume Next (sparingly!) or structured error handling:

  On Error GoTo SafeExit
      ' Code that might fail
      SafeExit:
      If Err.Number <> 0 Then
      MsgBox "Error " & Err.Number & ": " & Err.Description
      End If