How To Fix VBA Code Error Index Out Of Range Problems


If your computer has a VBA code error index out of range, this guide will help you fix it. An out of range index error occurs when you try to reference the index of an invalid collection. Most likely, it is not actually in the Windows index. xls. The window index must match the workbook name displayed in the Excel title bar.



  • 2 minutes to read.
  • array elements and collection members are available only in their specific ranges. This error has the following causes and solutions:

    • You specified a non-existent array element. The index may be greater or less than the range of possible indices, or the array may not have dimensions currently assigned in the application. Check the declaration of an array to check the upper and lower bounds. Use the UBound and LBound functions to conditionally access the array when working with arrays containing If index specified as variable , check the spelling of the variable name.

  • You have declared an array, but you have not specified the number of elements. For example, the following code throws this error:

    vba code error subscript out of range

      Dim MyArray () as integer  MyArray (8) = 234 'Throws error 9. 

    Visual Basic does not implicitly specify the size of undefined ranges of an array from 0 to 10. Instead, you must use Dim or ReDim to explicitly specify whichThe number of elements in the array.

  • You mentioned a non-existent collection member. Try using the For Each … Next construct instead of specifying the index items.

  • You used a short form index that implicitly specifies an invalid element. For example, if you have! Collection operator! Implicitly specifies a key. For example an object! Key name. The value corresponds to the object. element (key name). Value. In this case, an error will be generated if the key name represents an invalid key in the collection. Use a valid collection key name or index to resolve the issue.

  • For more information, select an item and press F1 (Windows) or HELP (Macintosh).

    What Is Out Of Range Index Error In Excel VBA?

    For example, if you access a sheet that does not contain a workbook, you will receive a runtime error 9: “Index out of range.”


    If you click the “Exit” button, the subroutine will end. Click Debug to navigateGo to the line of code where the error occurred and Help will take you to a page on the Microsoft website.

    Solution 1: Check The Tables

    You may have set the wrong value for an array element. Therefore, it is a good idea to check the value set for the array element and make sure it is correct. Also be sure to check the table declaration and the upper and lower limits. If the tables have been resized, be sure to use the LBound and UBound functions to handle access. Check the spelling of the variable name if the index is a variable.

    Array in VBA Excel

    Solution 2: Specify The Number Of Items

    In some cases, you might not have set the number of elements in your code causing the error. It is recommended to specify the number of elements in an array using the Dim or ReDim functions.

    Use Dim and ReDim to define the number of items

    Solved e 3: Change The Design

    This error often occurs when the user specifies an invalid or nonexistent collection member. Therefore, instead of specifying index items, it is recommended that you use the “Everyone” construct.

    Use For each … Another construct

    Solution 4. Check The Name And L ‘key Pointer

    In some cases, it is possible that you have used the abbreviated form of the index and that you have specified an invalid element. Therefore, we recommend that you use a valid key name and index for the collection.

    Please use a valid key name
    • Missing element. In some cases, you may have referenced an element in a non-existent order. The index may be greater or less than the range of possible indices, or the array may not have any dimensions assigned to the array at this point in the application.
    • UndefinedN elements: you may not have defined the number of elements in your code. It is important that the number of elements in the array is determined using the “Dim” or “ReDim” commands.
    • Invalid collection member: In some cases, the user may have referenced a collection item that does not exist. This error can occur if a member of the collection is incorrectly referenced.
    • Label: You may have used a shorthand index and implicitly specified an invalid item. It is important to use a valid key name.



    How do you fix a run time error 9 subscript out of range?

    Button for debugging error messages. Sometimes, when you try to run a macro, you receive an error message such as: For example, the “Run-time error ‘9’” “Index out of range” error shown below. This error message contains 3 buttons: Finish, Debug, and Help. To stop the macro, click the End button.