How to fix Subscript out of range VBA error in Excel

Visual Basic for Applications also known as VBA is the official programming language for Microsoft Office Applications, i.e., Excel, Word, PowerPoint, Access etc. We can write simple to complex codes in VBA to accomplish our goals. While writing programs in VBA we can encounter many errors and one of the very common errors is “Subscript out of range” error.

In this tutorial we’ll learn why the “Subscript out of range” error occurs in VBA and how to fix this error through simple modifications in code. 

Reasons for Subscript out of range Error

This error usually occurs due to the following reasons;

• Making reference to an object which is not available or does not exist
• Making reference to an array item, before defining array length or beyond array length 

Let’s dive into both scenarios one by one and discuss the solutions for each of them in the following steps.

Case 1 – Making reference to non-existent objects

The subscript out of range error occurs when we try to make a reference to an object of a workbook or a sheet which does not exist at present. Let’s take a look at the pieces of code below which produce an error while accessing the non-existent objects.

Example 1 – Accessing a non-existent Workbook

In this example the code tries to write the word “Success” in the cell C16 of the workbook named “Monthly Sales Report.xlsx” while the actual name of the workbook is “Monthly Sales Report.xlsm”. Therefore, we’ll get the Subscript out of range error as shown below. On the error message box, if we select the “Debug” button then VBA will point toward the line which produced the error.

Now let’s fix this error by making appropriate amendments in the code.

Step 1 – Change the workbook name in the code to remove error

• As mentioned earlier the root cause of the error was the wrong name of the workbook. Now we’ll rectify the error by changing the name of the workbook to “Monthly Sales Report.xlsm” and run the code again to see if it resolves the error.

We can see that the error was resolved after modifying the name of the workbook appropriately and the word “Success” was written successfully in the sheet.

Example 2 – Accessing a non-existent sheet in a workbook

In this example the code tries to write the word “Success” in the cell C16 of the sheet named “Sales” while the actual name of the sheet in the workbook is “Sales Report”. Therefore, we’ll get the Subscript out of range error as shown below. On the error message box, if we select the “Debug” button then VBA will point toward the line which produced the error.

Now let’s fix this error by making appropriate amendments in the sheet name to avoid the error.

Step 1 – Change the sheet name in the code to remove error

• As mentioned earlier the root cause of the error was the wrong name of the sheet. Now we’ll rectify the error by changing the name of the sheet to “Sales Report” and run the code again to see if it resolves the error.

We can see that the error was resolved after modifying the name of the workbook appropriately and the word “Success” was written successfully in the sheet.

Case 2 – Making reference to array item beyond array length

The subscript out of range error can also occur when we try to make a reference to an object of an array beyond its actual length or the array length has not been defined yet. Let’s take a look at the pieces of code below which produce an error while accessing the array objects beyond the array length.

Example 1 – Accessing an array item before defining the array length

In this example the code tries to access the array item at position 1 and tries to assign a value of 25 and then prints it in the workbook at cell C16. However, as the length of array has not been defined yet, therefore, we’ll get the Subscript out of range error as shown below. On the error message box, if we select the “Debug” button then VBA will point toward the line which produced the error. 

Now let’s fix this error by making appropriate amendments in the sheet name to avoid the error.

Step 1 – Define the array length to remove error

• As mentioned earlier the root cause of the error was not defining the array length. Now we’ll rectify the error by defining the array length properly and then run the code again to see if it resolves the error.

We can see that the error was resolved after defining the array length appropriately and the value of first array item i.e., “25” was written successfully in the sheet in cell C16.

Example 2 – Accessing an array item beyond array length

In this example the code tries to access the array item at position 5 and tries to assign a value of 25 and then prints it in the workbook at cell C16. However, as the length of array is only 3. Therefore, we’ll get the Subscript out of range error as shown below. On the error message box, if we select the “Debug” button then VBA will point toward the line which produced the error. 

Now let’s fix this error by making appropriate amendments in the sheet name to avoid the error.

Step 1 – Change the reference to array object within array length to remove error

• As mentioned earlier the root cause of the error was trying to reference an object beyond array length. Now we’ll rectify the error by making reference to array object within the array length properly and then run the code again to see if it resolves the error.

We can see that the error was resolved after changing the reference index inside array length appropriately and the value of the first array item i.e., “25” was written successfully in the sheet in cell C16.