Workbooks | Worksheets | Array

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.

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).