EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home VBA VBA Resources VBA Error Handling Functions VBA Subscript out of Range
Secondary Sidebar
VBA Error Handling Functions
  • VBA Error
    • VBA IsError
    • VBA On Error
    • VBA IFError
    • VBA 1004 Error
    • VBA Subscript out of Range
    • VBA OverFlow Error
    • VBA Error Handling
    • VBA On Error Resume Next
    • VBA On Error Goto
    • VBA On Error GoTo 0
    • VBA Type Mismatch

VBA Subscript out of Range

By Ashwani JaiswalAshwani Jaiswal

VBA Subscript out of Range

Excel VBA Subscript out of Range

VBA Subscript out of Range or majorly knows as Run-Time Error 9 happens when we select such cell or sheet or workbook which actually does not come under range or criteria defined in Excel. It is like we have selected the range of 100 cells or a column and we have called out the values stored in 120 cells of the same column. Which means that we are going out of range to select and call out the values which are not in our defined criteria. When this kind of situation happens, we get a “Run-Time Error 9” message while compiling or running the code. VBA Subscript out of Range error message guides us to rectify the error which is related to the range we have selected in Excel.

Example of Excel VBA Subscript out of Range

Below are the different examples of VBA Subscript out of Range in Excel.

You can download this VBA Subscript out of Range Excel Template here – VBA Subscript out of Range Excel Template

VBA Subscript out of Range – Example #1

We will first consider a simple example. For this, we need to go to VBA windows and add a new module by going in Insert menu option as shown below.

VBA Subscript out of Range Example 1-1

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

We will get a white blank window of Module. This is where we need to do coding work.

All in One Financial Analyst Bundle(250+ Courses, 40+ Projects)
Financial ModelingInvestment BankingUS GAAPCFA-Level 1 & 2
Equity ResearchM & A ModelingPrivate Equity ModelingForex Trading
Price
View Courses
250+ Online Courses | 40+ Projects | 1000+ Hours | Verifiable Certificates | Lifetime Access
4.9 (86,258 ratings)

Now write Subcategory of performed function, for best practice keep the name of a function in Subcategory, as we did here for VBA Subscript out of Range.

Code:

Sub Subscript_OutOfRange1()

End Sub

VBA Subscript out of Range Example 1-2

Here in excel, we have only one sheet named as “Sheet1” as shown below.

VBA Subscript out of Range Example 1-3

But we will write a code to select a sheet which is not even added and see what happens.

Now go to VBA window and write Sheets(2) followed by Select function as shown below. Which means, we are selecting Sheet sequence of 2nd position with Select function.

Code:

Sub Subscript_OutOfRange1()

  Sheets(2).Select

End Sub

VBA Subscript out of Range Example 1-4

Now compile the complete code or do it step by step to know which part of the code is an error. As we have only one line of code, we can directly run the code by clicking on the play button below the menu bar. We will get an error Message saying “Run-Time error 9, Subscript out of range” in the VBA as shown below.

Result of Example 1-5

This shows that we are trying to select that sheet which doesn’t exist. If we add a new sheet or change the sheet sequence in code from 2nd to 1st then we may get a successful code run. Let’s add another sheet and see what happens.

VBA Subscript out of Range Example 1-6

Now again run the code. And as we did not see any error, which means our code completes the successful run.

Result of Example 1-7

VBA Subscript out of Range – Example #2

In another example, we will see again a simple code of activating a Worksheet. For this again we will write the code. Start writing the Subcategory in the name of a performed function or in any other name as shown below.

Code:

Sub Subscript_OutOfRange2()

End Sub

VBA Subscript out of Range Example 2-1

Now with the help of Worksheet, we will activate Sheet1 as shown below.

Code:

Sub Subscript_OutOfRange2()

  Worksheets("Sheet1").Activate

End Sub

VBA Subscript out of Range Example 2-2

Now compile the complete code and run. We will notice there is no error message been popped-up which means code run is successful. Now let’s put the space in between “Sheet 1”

VBA Subscript out of Range Example 2-3

Again compile and run the code.

Result of Example 2-4

As we can see above, even if our complete process and way of writing the code are correct but we have taken in correct sheet name as “Sheet 1”. Which in reality has no space between “Sheet1”.

This shows, there are the still chances of getting an error if do not spell or write correct sheet name or workbook name.

VBA Subscript out of Range – Example #3

In this example, we will see how choosing incorrect Array range may create and show Run-time error 9. Start writing Subcategory again in the name of the performed function as shown below.

Code:

Sub Subscript_OutOfRange3()

End Sub

VBA Subscript out of Range Example 3-1

Now with the help of DIM define an Array of any size and gives it to String or Integers. Which depends, what we want to store in Array, numbers or text.

Here we have considered an array of 2×3 as String as shown below.

Code:

Sub Subscript_OutOfRange3()

  Dim SubArray(2, 3) As String

End Sub

VBA Subscript out of Range Example 3-2

By this, it will form a table for 2 rows and 3 columns and we can store any values as per our need. As we have selected String then we will consider text or alphabets in it.

Now in the second line of code, select the created array but with an extra or more column and assign a text as ABC or any other text as per your choice. Here, we have selected an Array of 2×5 as shown below.

Code:

Sub Subscript_OutOfRange3()

  Dim SubArray(2, 3) As String

  SubArray(2, 5) = ABC

End Sub

VBA Subscript out of Range Example 3-3

Now compile and run the code. As we can see in below screenshot, we got a VBA Subscript out of Range error message of Run-time error 9.

Result of Example 3-4

Reason for getting this error is because we have selected an incorrect Array range within 2 extra columns from 2×3 to 2×5, which is beyond the limit of code. Now if we again select the correct range of array as 2×3 and see what happens.

Result of Example 3-5

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

Pros of Excel VBA Subscript out of Range

  • VBA Subscript out of Range allows us to know what kind of error has happened. So that we can specifically find the solution of the obtained error code.
  • As VBA subscript out of range ‘Run-time error 9’ is quite useful in knowing what kind of error has occurred in excel.

Things to Remember

  • It is recommended to use Subcategory in the name of the performed function with a sequence of code so that it would be easy to track it properly.
  • Save the file as Macro-Enabled Workbook to avoid losing written code.
  • If you have huge lines of code then it is better to compile each line of code one by one by pressing F8 key. This method compiles each step of code so that we can directly know which portion of code actually has the error in the first go.

Recommended Articles

This has been a guide to Excel VBA Subscript out of Range. Here we discussed why VBA Subscript out of Range error occurs (Run-time Error 9) along with some practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA IsError
  2. VBA Get Cell Value
  3. VBA On Error
  4. VBA XML
1 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more