Subscript Out of Range in Excel VBA
The 'subscript out of range' error in Excel VBA occurs when you refer to a nonexistent collection member or a nonexistent array element.
Place a command button on your worksheet and add the code lines below. To execute the code lines, click the command button on the sheet.
Workbooks
The Workbooks collection in Excel VBA contains all the Workbook objects that are currently open.
1. The code line below closes sales.xlsm.
Result when there's no open workbook with this name:
Note: to fix this 'subscript out of range' error (run-time error 9), open sales.xlsm before clicking the command button.
Worksheets
The Worksheets collection in Excel VBA contains all the Worksheet objects in a workbook. Our workbook has 3 worksheets.
1. The code line below tries to place the word Hello into cell A1 on the 4th worksheet.
Result when you click the command button on the sheet:
Explanation: the 'subscript out of range' error pops up because there's no 4th worksheet. To fix this error, change the 4 to a 1, 2 or 3 (or insert a new worksheet by clicking the plus sign).
Array
An array is a group of variables. In Excel VBA, you can refer to a specific variable (element) of an array by using the array name and the index number.
1. The first code line below declares a String array with name Films. The array consists of five elements.
2. Next, we initialize each element of the array.
3. The final code line tries to display the 6th element using a MsgBox.
Films(1) = "Lord of the Rings"
Films(2) = "Speed"
Films(3) = "Star Wars"
Films(4) = "The Godfather"
Films(5) = "Pulp Fiction"
MsgBox Films(6)
Result when you click the command button on the sheet:
Explanation: the 'subscript out of range' error pops up because there's no 6th element. To fix this error, change the 6 to a 1, 2, 3, 4 or 5 (or declare a String array with 6 elements).
If you're new here, welcome to Excel Easy! Join over 1 million monthly Excel learners. You can find popular courses here: Data Analysis in Excel and Excel VBA.
