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.

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
ABOUT THE AUTHOR

Kevin Arrows


Kevin Arrows is a highly experienced and knowledgeable technology specialist with over a decade of industry experience. He holds a Microsoft Certified Technology Specialist (MCTS) certification and has a deep passion for staying up-to-date on the latest tech developments. Kevin has written extensively on a wide range of tech-related topics, showcasing his expertise and knowledge in areas such as software development, cybersecurity, and cloud computing. His contributions to the tech field have been widely recognized and respected by his peers, and he is highly regarded for his ability to explain complex technical concepts in a clear and concise manner.