Subscript Out of Range Error in Microsoft Excel VBA

Subscript out of Range in Microsoft Excel VBA is also commonly known as the Run-Time Error 9. This error happens when we select a cell or the sheet and the workbook which does not come under the particular Range or the criteria which are primarily defined in Microsoft Excel, respectively.

It is much like that, we have assumed out the specific Range of 100 cells or a column, and we have called out the values that are primarily stored in the rest of the 120 cells of the same column; that means that we, in turn going out of the Range to select and then call out the values which are not primarily present in our defined boundaries.

Furthermore, when we encounter this situation, we will get a "Run-Time Error 9" message while compiling and running the respective code.

Besides all these, the particular VBA Subscript Range error message usually guides us (individuals) to rectify the error related to the Range we selected in Microsoft Excel effectively.

# Example: Example related to the Excel VBASubscript out of Range

Moving further, let us now see the various examples that will make us understand the VBA (Virtual Basic for Application) Subscript Range Error in Microsoft Excel respectively.

# Example 1: VBA Subscript out of Range

Let us first we will consider a simple example. And for this, we need to move to the VBA (Virtual Basic for Applications) windows. Then after that, we will be adding out a new module by just going into the Insert menu option as it was effectively depicted in the below-attached screenshot.

Now, after that we we will get a window (i.e. white blank) of the Module. And this is the particular place in which we need to do coding work efficiently.

After that, we will write out the Subcategory of the performed function. For the convenience practice, we will now assign the name of a function in the Subcategory effectively, as seen in the below figure.

In Microsoft Excel, we have only one sheet named "Sheet1", as seen in the screenshot below.

Now after that, we will be writing out the code for the purpose of selecting out the sheet which is not still added and then we will see what exactly happens.

After that, we will move to the VBA (Virtual Basic for Application) window. Then we will write out the Sheets (2) that are efficiently followed by the respective Select function, as seen in the screenshot below.

That means that we are selecting the Sheet sequence of the 2^ndposition with the help of the Select function.

Code:

We will then start compiling the complete code or will do it step by step in order to get acknowledged that which particular part of the code has an error.

And it was known that, we have only single line of code, which we can directly run by clicking on the play button that is present just below the menu bar.

And after running of the code, we will be displayed out with the error message that is "Run-Time error 9, Subscript out of range" in the respective VBA (Virtual Basic for Application), as seen in the screenshot below.

After that, it will depict us that we are trying to select that particular sheet which basically doesn't exist. And if we add a new sheet or when we change the sheet sequence in the coding from 2to 1^, then we might get the successful code to run efficiently. After that, let us add another sheet and see what will happen.

Now again, we will be running the code. And after running the code, if we won't see

Kind of error means that our code is error-free, and we will surely get our desired output as well.

# Example 2: Subscript out of Range in Microsoft Excel

Now in the other example, we will again see a simple code for activating a Worksheet, and we will be writing the code again. And then, we will be writing the Subcategory in the name of the performed function, as depicted in the screenshot below.

Code:

Now, with the respective Worksheet's help, we can activate Sheet1, as depicted clearly in the screenshot below.

Code:

After that, we will compile the complete code, and then we will run it. And then, we will notice that no error message has popped up, meaning the code run is successful. We will be effectively putting out the space between "Sheet 1", as seen in the screenshot below.

And then, after that, again, we will be compiling and running the code, as seen in the below-attached figure.

From the above, we can see that, even if our process is complete and the way of writing the code is also correct, we have taken the valid sheet name as "Sheet 1". That, in reality, has no space in between "Sheet1".

And it will show that there are still chances of getting an error if we do not spell or write out the correct sheet and workbook names.

# Example 3: Subscript out of Range in Microsoft Excel

On moving further with the above topic now in this example, we will be seeing how to choose the Array range (incorrect one) that may primarily create and then will show Run-time error 9.

After that we will start writing Subcategory again in the name of the performed function as clearly seen in the below figure as well.

Code:

And with the help of DIM, we can easily define an Array of any size, and we will give it to String or Integers that primarily depends on what we want to store in an Array either we want to store numbers or the text.

Here in this example, we have taken an array of 2×3 as a String, as depicted in the figure below.

Code:

And doing the above will then create a table for 2 rows and 3 columns, and then we can easily store any values as per our need. As we have selected the String so for this we will take text or alphabets.

In the second line of code, we will be selecting out the created array and that with some extra or more columns, then after that, we will assign a text as. Here, we have selected an Array of 2×5 as depicted below.

Code:

Soon after that, we will be compiling and running the desired code. As seen in the screenshot below, we got a VBA Subscript of Range error message of Run-time error 9.

And the main reason behind forgetting this error is that we have selected an incorrect Array range within 2 extra columns from 2×3 to 2×5, which is basically beyond the code limit. Now, if we choose the correct Range of array as 2×3 again and see what happens?

And after compiling and running the code. We will see that we did not receive any error, which means our code run was successful.

Pros related to the Subscript out of range in Excel

The pros that are basically related with the respective VBA (Virtual Basic for Application) Subscript out of Range in Microsoft Excel are as depicted below:

1. The VBA (Virtual Basic for Application) Subscript out of Range in the Excel specially allows us (individuals) in order to know what kind of error has happened to be or occurred. So that, we can quickly and precisely find the practical solution to the obtained error code.
2. And we can make use of the particular VBA subscript out of range 'Run-time error 9' in order to know what specific kind of the error usually gets occurred in Microsoft Excel.

Things to Remember

The various important things that need to be remembered by an individual who all are working with subscript out of Range in Microsoft Excel are as follows:

1. It was recommended to an individual (who is working on the Excel VBA) to make use of the Subcategory in the name of the performed function with the sequence of code so that it is pretty easy to track it whenever they are needed.
2. And to avoid losing the written code, an individual must save the file as "Macro-Enabled Workbook", respectively.
3. And in case, if the particular individual gets encountered with huge lines of code, then in that case it is supposed to compile each line of code one by one with the help of pressing the F8 key. And by these method an individual can collect out the Steps and can judge which portion of the code is facing error in the very first attempt.