4 Basic Solutions of ‘Subscript out of range’ Visual Basic Error
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.
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.
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.
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.
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.
