How to Fix ‘Subscript Out of Range’ Error in Visual Basic for Applications?

Visual Basic for Applications (VBA) is an implementation of the Visual Basic programming language developed by Microsoft. VBA can be used for enabling UDFs, automating tasks and for accessing WinAPI through DLLs. However, quite recently, a lot of reports have been coming in where users are unable to execute certain commands and a “Subscript Out of Range, Error 9” Pops up while trying to do so.

Subscript out of range error

What Causes the “Subscript Out of Range” Error in VBA?

After receiving numerous reports from multiple users, we decided to investigate the issue and devised a set of solutions to fix it completely. Also, we looked into the reasons due to which it is triggered and listed them as follows.

  • Non-Existing Element: In some cases, it is possible that you might have referenced an element in the command that doesn’t exist. It is possible that the subscript is either larger or smaller than the range of possible subscripts or dimensions might not have been assigned to the array at this point in the application.
  • Undefined Elements: It is possible that you didn’t identify the number of elements in your code, it is important that the number of elements in an array is defined by using the “Dim” or “ReDim” commands.
  • Incorrect Collection Member: In some cases, the user might have referenced a collection member that doesn’t exist. If an incorrect reference is made towards the collection member, this error might be triggered.
  • Shorthand Script: It is possible that you have made use of a shorthand form of subscript and it implicitly specified an element that was invalid. It is important to use a valid key name.

Now that you have a basic understanding of the nature of the problem, we will move on towards the solutions. Make sure to implement these in the specific order in which they are presented to avoid conflict.

Solution 1: Checking Arrays

It is possible that you have defined an incorrect value for the Array element. Therefore, it is recommended to double-check the value that you have defined for the Array element and make sure that it is the correct one. Also, make sure that you check the declaration of the array and verify the upper and the lower bounds. If the arrays have been redimensioned, make sure to use the LBound and UBound functions to condition accesses. Check the spelling of the Variable name if the index is a variable.

Array in VBA Excel

Solution 2: Specifying Number of Elements

In some cases, it is possible that you might not have defined the number of elements in your code due to which the error is being triggered. It is recommended that you specify the number of elements in the array using the Dim or ReDim functions.

Using Dim and ReDim to specify the number of elements

Solution 3: Changing Construct

This error is commonly triggered when the user specifies an incorrect or non-existent collection member. Therefore, instead of specifying the index elements, it is recommended that you approach with the “For Each…Next” construct.

Using “For Each…Next” construct

Solution 4: Checking Keyname and Index

In some cases, it is possible that you might have used a shorthand form of the subscript and it specifies an invalid element. Therefore, it is recommended that you use a valid key name and index for the collection.

Using a Valid Keyname
Kevin Arrows
Kevin is a dynamic and self-motivated information technology professional, with a Thorough knowledge of all facets pertaining to network infrastructure design, implementation and administration. Superior record of delivering simultaneous large-scale mission critical projects on time and under budget.

Expert Tip

How to Fix ‘Subscript Out of Range’ Error in Visual Basic for Applications?

If the issue is with your Computer or a Laptop you should try using Restoro which can scan the repositories and replace corrupt and missing files. This works in most cases, where the issue is originated due to a system corruption. You can download Restoro by clicking the Download button below.

Download Now

I'm not interested