Sometimes when we write and attempt to run sub-procedures in Excel VBA, Excel VBA returns a message box with the error message “Runtime error 9: Subscript out of range.”
The “Subscript out of range” error happens when we reference a non-existent collection member or a non-existent array element.
By the error, Excel VBA is telling us, “I have not found what you are looking for.”
This tutorial gives examples of specific causes of this error and how to fix it.
Before fixing the “Subscript out of range” error, you must identify its cause.
When you press the Debug button on the error message box, Excel VBA takes you to the statement that caused the error. By examining the line of code, you should be able to tell what caused the error. We outline some of the causes and solutions below.
Cause #1: Referencing a Non-Existent Item in a Collection
We can only access members of collections within their delimited ranges.
Therefore, attempting to access a collection member outside the defined scope will cause the “Subscription out of range” runtime error.
Suppose we have only Sheet1 and Sheet2 in our active workbook.
If we write the following sub-procedure and press F5 to run it:
We get the “Subscript out of range” error:
The error occurs because the Sheet3 object is not present in the Sheets collection, which consists of all the worksheets and chart sheets in the active workbook.
Therefore the code is referencing a non-existent entity in the Sheets collection.
How to Fix It
You can fix this error in any of the following ways:
- Create the non-existent object; in this case, add Sheet3 to the workbook.
- Check the spelling of the name of the collection member you want to access and ensure it is correct.
- In the Excel VBA code, refer to an object present in the collection; in this case, you can refer to either Sheet1 or Sheet2.
- Use the For Each…Next loop instead of referencing specific collection members. The loop allows us to loop through collection members and repeat particular actions, such as unhiding worksheets.
Also read: Not Enough Memory to Complete This Action in Excel – How to Fix?
Cause #2: Attempting to Access a Closed Workbook
If you try to access a closed workbook, you get the “Subscript out of range” error.
For example, if we have a closed workbook called “Employees” on our computer, running the following sub-procedure will generate the “Subscript out of range” error.
This error is generated because a closed workbook is not part of the collection of workbooks.
All the open workbooks on your computer make up the workbooks collection.
How to Fix It
You can use any of the following methods to fix the error.
- Open the workbook you want to access, then run the sub-procedure.
- Use the For Each…Next loop to check whether the workbook you wish to access is open. The loop goes through all the available workbooks and matches the name of the workbook you want to access against each open workbook. If a match is found, the workbook is open otherwise; the workbook is closed.
Also read: Excel VBA Runtime Error 1004
Cause #3: Referencing a Non-existent Array Element
If you reference a non-existent array element in your code, VBA displays the “Subscript out of range” to indicate something wrong with the code.
For example, the following code refers to an array element 11, which is not in the array declaration.
When we attempt to run the code, we get the “Subscript out of range” error.
How to Fix It
You can use any of the following techniques to solve the problem:
- Verify the upper and lower bounds of the array in the array declaration. If the dimensions are different from what you intended, adjust them accordingly.
- Ensure that you only refer to the array elements in the array declaration.
- Use LBound and UBound functions to direct the access of redimensioned arrays. The LBound function returns the lower boundary of the array, which can be either 0 or 1. The UBound function returns the upper limit of the array, which is equivalent to the number of items in the array.
- If the array dimensions are declared variables, ensure the variable names are spelled correctly.
Also read: VBA Type Mismatch Error (Error 13)
Cause #4: Not Specifying the Number of Elements in an Array
If you declare an array but do not specify the number of elements in the array and attempt to access an element in the array, VBA returns the “Subscript out of range” error.
For example, the following code results in the error:
How to Fix It
- Explicitly specify the number of elements in the array in the array declaration.
Cause #5: Misspelling the name of a Workbook
If you misspell the name of the workbook you want to access, Excel VBA will return the “Subscript is out of range error.”
For example, if you want to activate a workbook named “Employees.xlsx,” the following code will not activate the workbook but return an error:
Although the workbook is open, the sub-procedure does not activate it because its name needs to be corrected.
How to Fix It
- Check the spelling of the workbook’s name in the sub-procedure and ensure it is correct.
Also read: VBA Runtime Error 91
Cause #6: Specifying an Invalid Element
Sometimes when you use the shorthand form of a subscript, you may mistakenly specify an invalid element.
For example, the shorthand for ActiveSheet.Range(“C3”) is [C3]. If this shorthand form of the subscript refers to an invalid element, you will get the “Subscript is out of range error.”
How to Fix It
Use a valid index or name for the collection.
This tutorial has given reasons and solutions for the “Subscript out of range” error. This error happens when we reference a non-existent collection member or a non-existent array element.
Examples of specific causes include
- Referencing a worksheet or workbook not present in the collection.
- Misspelling object names in the code.
- Referencing a non-existent array element.
- Not specifying the number of elements in the array.
- Attempting to access a closed workbook.
- Specifying an invalid element.
The solutions to the error include using the For Each…Next construct, instead of referencing specific items in the code and specifying the elements in an array.
We hope you found the tutorial helpful.
Other articles you may also like: