VBA VBA Run-Time Errors Run-time error '9': Subscript out of range
Example
Incorrect code
Sub DoSomething()
Dim foo(1 To 10)
Dim i As Long
For i = 1 To 100
foo(i) = i
Next
End Sub
Why doesn't this work?
foo is an array that contains 10 items. When the i loop counter reaches a value of 11, foo(i) is out of range. This error occurs whenever an array or collection is accessed with an index that doesn't exist in that array or collection.
Correct code
Sub DoSomething()
Dim foo(1 To 10)
Dim i As Long
For i = LBound(foo) To UBound(foo)
foo(i) = i
Next
End Sub
Why does this work?
Use LBound and UBound functions to determine the lower and upper boundaries of an array, respectively.
Other notes
When the index is a string, e.g. ThisWorkbook.Worksheets("I don't exist"), this error means the supplied name doesn't exist in the queried collection.
The actual error is implementation-specific though; Collection will raise run-time error 5 "Invalid procedure call or argument" instead:
Sub RaisesRunTimeError5()
Dim foo As New Collection
foo.Add "foo", "foo"
Debug.Print foo("bar")
End Sub
