Ошибка vba run time error 9

I found a macro on the web to protect a worksheet with a password. It works fine, but when I save the file I get the message: run-time error ‘9’: subscription out of range. I have never programmed or used visual basic before and could use some help . Thank you

The macro is:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

'Step 1:Protect the sheet with a password
    Sheets("Sheet1").protect Password:="btfd"

'Step 2: Save the workbook
    ActiveWorkbook.Save
End Sub

asked Feb 20, 2014 at 22:06

«Subscript out of range» indicates that you’ve tried to access an element from a collection that doesn’t exist. Is there a «Sheet1» in your workbook? If not, you’ll need to change that to the name of the worksheet you want to protect.

answered Feb 20, 2014 at 22:10

Why are you using a macro? Excel has Password Protection built-in. When you select File/Save As… there should be a Tools button by the Save button, click it then «General Options» where you can enter a «Password to Open» and a «Password to Modify».

answered Feb 20, 2014 at 22:21

Gordon BellGordon Bell

13.2k3 gold badges45 silver badges64 bronze badges

When you get the error message, you have the option to click on «Debug»: this will lead you to the line where the error occurred. The Dark Canuck seems to be right, and I guess the error occurs on the line:

Sheets("Sheet1").protect Password:="btfd"

because most probably the «Sheet1» does not exist. However, if you say «It works fine, but when I save the file I get the message: run-time error ‘9’: subscription out of range» it makes me think the error occurs on the second line:

ActiveWorkbook.Save

Could you please check this by pressing the Debug button first?
And most important, as Gordon Bell says, why are you using a macro to protect a workbook?

answered Feb 21, 2014 at 9:17

Matteo NNZMatteo NNZ

11.9k11 gold badges52 silver badges89 bronze badges

Источник

Home > VBA > VBA Subscript Out of Range Runtime Error (Error 9)

Subscript Out of Range Error (Run Time: Error 9) occurs when you refer to an object or try to use a variable in a code that doesn’t exist in the code, in that case, VBA will show this error. As every code that you write is unique, so the cause of the error would be.

In the following example, you have tried to activate the “Sheet1” which is an object. But as you can see in the workbook no worksheet exists with the name “Sheet1” (instead you have “Sheet2”) so VBA show “Subscript Out of Range” to notify you that there’s something wrong with the code.

Subscript Out of Range

There could be one more situation when you have to face the error “Subscript Out of Range Error” when you are trying to declare a dynamic array but forget to use the DIM and ReDim statement to redefine the length of the array.

Now in the above code, you have an array with the name “myArray” and to make it dynamic we have initially left the array length blank. But before you add an item you need to redefine the array length using the ReDim statement.

And that’s the mistake we have made in the above code and VBA has returned the “Script Out of Range” error.

Sub myMacro()
Dim myArray() As Variant
myArray(1) = "One"
End Sub

How Do I Fix Subscript Out of Range in Excel?

The best way to deal with this Subscript Out of Range is to write effective codes and make sure to debug the code that you have written (Step by Step).

When you run a code step by step it is easy for you to know on which line of that code you have an error as VBA will show you the error message for Error 9 and highlight that line with yellow color.

The other thing that you can do is to use an “Error Handler” to jump to a specific line of error when it happens.

In the following code, we have written a line to activate the sheet but before that, we have used the goto statement to move to the error handler. In the error handler, you have a message box that shows you a message with the Err. Description that an error has occurred.

So, when you run this code and the “Sheet1” is not in the workbook where you are trying to activate it. It will show you a message box just like below.

And if the “Sheet1” is there then there won’t be any message at all.

Sub myMacro()

Dim wks As Worksheet

On Error GoTo myError
Sheets("Sheet1").Activate

myError:
MsgBox "There's an error in the code: " & Err.Description & _
". That means there's some problem with the sheet " & _
"that you want to activate"

End Sub

What is VBA

VBA ERROR Handling
VBA Automation Error (Error 440)
VBA Error 400
VBA Invalid Procedure Call Or Argument Error (Error 5)
VBA Object Doesn’t Support this Property or Method Error (Error 438)
VBA Object Required Error (Error 424)

VBA Out of Memory Error (Error 7)
VBA Overflow Error (Error 6)
VBA Runtime Error (Error 1004)
VBA Type Mismatch Error (Error 13)

Источник

Summary:
The runtime error 9 in Excel usually occurs when you use different objects in a code or the object you are trying to use is not defined. This post will discuss the reasons behind the Excel VBA error «Subscript out of Range” and the solutions to resolve the issue. It will also mention an Excel repair tool that can help fix the error if it occurs due to corruption in worksheet.

Contents

Causes of VBA Runtime Error 9: Subscript Out Of Range
Methods to Fix Excel VBA Error ‘Subscript out of Range’
Conclusion

Many users have reported encountering the error “Subscript out of range” (runtime error 9) when using VBA code in Excel. The error often occurs when the object you are referring to in a code is not available, deleted, or not defined earlier. Sometimes, it occurs if you have declared an array in code but forgot to specify the DIM or ReDIM statement to define the length of array.

Causes of VBA Runtime Error 9: Subscript Out Of Range

The error ‘Subscript out of range’ in Excel can occur due to several reasons, such as:

Object you are trying to use in the VBA code is not defined earlier or is deleted.
Entered a wrong declaration syntax of the array.
Wrong spelling of the variable name.
Referenced a wrong array element.
Entered incorrect name of the worksheet you are trying to refer.
Worksheet you trying to call in the code is not available.
Specified an invalid element.
Not specified the number of elements in an array.
Workbook in which you trying to use VBA is corrupted.

Methods to Fix Excel VBA Error ‘Subscript out of Range’

Following are some workarounds you can try to fix the runtime error 9 in Excel.

Method 1: Check the Name of Worksheet in the Code

Sometimes, Excel throws the runtime error 9: Subscript out of range if the name of the worksheet is not defined correctly in the code. For example – When trying to copy content from one Excel sheet (emp) to another sheet (emp2) via VBA code, you have mistakenly mentioned wrong name of the worksheet (see the below code).

Private Sub CommandButton1_Click()
Worksheets("emp").Range("A1:E5").Select
Selection.Copy
Worksheets("emp3").Activate
Worksheets("emp3").Range("A1:E5").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

When you run the above code, the Excel will throw the Subscript out of range error.

So, check the name of the worksheet and correct it. Here are the steps:

Go to the Design tab in the Developer section.
Double-click on the Command button.
Check and modify the worksheet name (e.g. from “emp” to “emp2”).

Now run the code.
The content in ‘emp’ worksheet will be copied to ‘emp2’ (see below).

Method 2: Check the Range of the Array

The VBA error “Subscript out of range” also occurs if you have declared an array in a code but didn’t specify the number of elements. For example – If you have declared an array and forgot to declare the array variable with elements, you will get the error (see below):

To fix this, specify the array variable:

Sub FillArray()
Dim curExpense(364) As Currency
Dim intI As Integer
For intI = 0 to 364
curExpense(intI) = 20
Next
End Sub

Method 3: Change Macro Security Settings

The Runtime error 9: Subscript out of range can also occur if there is an issue with the macros or macros are disabled in the Macro Security Settings. In such a case, you can check and change the macro settings. Follow these steps:

Open your Microsoft Excel.
Navigate to File > Options > Trust Center.
Under Trust Center, select Trust Center Settings.
Click Macro Settings, select Enable all macros, and then click OK.

Method 4: Repair your Excel File

The name or format of the Excel file or name of the objects may get changed due to corruption in the file. When the objects are not identified in a VBA code, you may encounter the Subscript out of range error. You can use the Open and Repair utility in Excel to repair the corrupted file. To use this utility, follow these steps:

In your MS Excel, click File > Open.
Browse to the location where the affected file is stored.
In the Open dialog box, select the corrupted workbook.
In the Open dropdown, click on Open and Repair.
You will see a prompt asking you to repair the file or extract data from it.
Click on the Repair option to extract the data as much as possible. If Repair button fails, then click Extract button to recover data without formulas and values.

If the “Open and Repair” utility fails to repair the corrupted/damaged macro-enabled Excel file, then try an advanced Excel repair tool, such as Stellar Repair for Excel. It can easily repair severely corrupted Excel workbook and recover all the items, including macros, cell comments, table, charts, etc. with 100% integrity. The tool is compatible with all versions of Microsoft Excel.

Conclusion

You may experience the “Subscript out of range” error while using VBA in Excel. You can follow the workarounds discussed in this blog to fix the error. If the Excel file is corrupt, then you can use Stellar Repair for Excel to repair the file. It’s a powerful software that can help fix all the issues that occur due to corruption in the Excel file. It helps to recover all the data from the corrupt Excel files (.xls, .xlsx, .xltm, .xltx, and .xlsm) without changing the original formatting. The tool supports Excel 2021, 2019, 2016, and older versions.

About The Author

Monika Dadool

Monika Dadool is a Technical content writer at Stellar who writes about QuickBooks, Sage50, MySQL Database, Active Directory, e-mail recovery, Microsoft365, Pattern Recognition, and Machine learning. She loves researching, exploring new technology, and Developing engaging technical blogs that help organizations or Database Administrators fix multiple issues. When she isn’t creating content, she is busy on social media platforms, watching web series, reading books, and searching for food recipes.

Источник

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.

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

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

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

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 2^nd position with Select function.

Code:

Sub Subscript_OutOfRange1()

  Sheets(2).Select

End Sub

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.

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 2^nd to 1^st then we may get a successful code run. Let’s add another sheet and see what happens.

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

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

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

Code:

Sub Subscript_OutOfRange2()

  Worksheets("Sheet1").Activate

End Sub

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”

Again compile and run the code.

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

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

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

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.

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.

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.

Cause #1: Referencing a Non-Existent Item in a Collection

We can only access members of collections within their delimited ranges.

Therefore, attempting to access a collection member outside the defined scope will cause the “Subscription out of range” runtime error.

Suppose we have only Sheet1 and Sheet2 in our active workbook.

If we write the following sub-procedure and press F5 to run it:

We get the “Subscript out of range” error:

The error occurs because the Sheet3 object is not present in the Sheets collection, which consists of all the worksheets and chart sheets in the active workbook.

Therefore the code is referencing a non-existent entity in the Sheets collection.

How to Fix It

You can fix this error in any of the following ways:

Create the non-existent object; in this case, add Sheet3 to the workbook.
Check the spelling of the name of the collection member you want to access and ensure it is correct.
In the Excel VBA code, refer to an object present in the collection; in this case, you can refer to either Sheet1 or Sheet2.
Use the For Each…Next loop instead of referencing specific collection members. The loop allows us to loop through collection members and repeat particular actions, such as unhiding worksheets.

Also read: Not Enough Memory to Complete This Action in Excel – How to Fix?

Cause #2: Attempting to Access a Closed Workbook

If you try to access a closed workbook, you get the “Subscript out of range” error.

For example, if we have a closed workbook called “Employees” on our computer, running the following sub-procedure will generate the “Subscript out of range” error.

This error is generated because a closed workbook is not part of the collection of workbooks.

All the open workbooks on your computer make up the workbooks collection.

How to Fix It

You can use any of the following methods to fix the error.

Open the workbook you want to access, then run the sub-procedure.
Use the For Each…Next loop to check whether the workbook you wish to access is open. The loop goes through all the available workbooks and matches the name of the workbook you want to access against each open workbook. If a match is found, the workbook is open otherwise; the workbook is closed.

Cause #3: Referencing a Non-existent Array Element

If you reference a non-existent array element in your code, VBA displays the “Subscript out of range” to indicate something wrong with the code.

For example, the following code refers to an array element 11, which is not in the array declaration.

When we attempt to run the code, we get the “Subscript out of range” error.

How to Fix It

You can use any of the following techniques to solve the problem:

Verify the upper and lower bounds of the array in the array declaration. If the dimensions are different from what you intended, adjust them accordingly.
Ensure that you only refer to the array elements in the array declaration.
Use LBound and UBound functions to direct the access of redimensioned arrays. The LBound function returns the lower boundary of the array, which can be either 0 or 1. The UBound function returns the upper limit of the array, which is equivalent to the number of items in the array.
If the array dimensions are declared variables, ensure the variable names are spelled correctly.

Cause #4: Not Specifying the Number of Elements in an Array

If you declare an array but do not specify the number of elements in the array and attempt to access an element in the array, VBA returns the “Subscript out of range” error.

For example, the following code results in the error:

How to Fix It

Explicitly specify the number of elements in the array in the array declaration.

Cause #5: Misspelling the name of a Workbook

If you misspell the name of the workbook you want to access, Excel VBA will return the “Subscript is out of range error.”

For example, if you want to activate a workbook named “Employees.xlsx,” the following code will not activate the workbook but return an error:

Although the workbook is open, the sub-procedure does not activate it because its name needs to be corrected.

How to Fix It

Check the spelling of the workbook’s name in the sub-procedure and ensure it is correct.

Cause #6: Specifying an Invalid Element

Sometimes when you use the shorthand form of a subscript, you may mistakenly specify an invalid element.

For example, the shorthand for ActiveSheet.Range(“C3”) is [C3]. If this shorthand form of the subscript refers to an invalid element, you will get the “Subscript is out of range error.”

How to Fix It

Use a valid index or name for the collection.

This tutorial has given reasons and solutions for the “Subscript out of range” error. This error happens when we reference a non-existent collection member or a non-existent array element.

Examples of specific causes include

Referencing a worksheet or workbook not present in the collection.
Misspelling object names in the code.
Referencing a non-existent array element.
Not specifying the number of elements in the array.
Attempting to access a closed workbook.
Specifying an invalid element.

The solutions to the error include using the For Each…Next construct, instead of referencing specific items in the code and specifying the elements in an array.

We hope you found the tutorial helpful.

Other articles you may also like:

Using Application.GetSaveAsFilename in VBA in Excel
Using Application.EnableEvents in VBA in Excel
SetFocus in Excel VBA – How to Use it?
How to Open Excel Files Using VBA
#NAME? Error in Excel – How to Fix!
#NUM! Error in Excel – How to Fix it?
SPILL Error in Excel – How to Fix?

Источник

Subscript Out of Range

How Do I Fix Subscript Out of Range in Excel?

Causes of VBA Runtime Error 9: Subscript Out Of Range

Methods to Fix Excel VBA Error ‘Subscript out of Range’

Method 1: Check the Name of Worksheet in the Code

Method 2: Check the Range of the Array

Method 3: Change Macro Security Settings

Method 4: Repair your Excel File

Conclusion

About The Author

Excel VBA Subscript out of Range

Example of Excel VBA Subscript out of Range

VBA Subscript out of Range – Example #1

VBA Subscript out of Range – Example #2

VBA Subscript out of Range – Example #3

Pros of Excel VBA Subscript out of Range

Things to Remember

Recommended Articles

Cause #1: Referencing a Non-Existent Item in a Collection

How to Fix It

Cause #2: Attempting to Access a Closed Workbook

How to Fix It

Cause #3: Referencing a Non-existent Array Element

How to Fix It

Cause #4: Not Specifying the Number of Elements in an Array

How to Fix It

Cause #5: Misspelling the name of a Workbook

How to Fix It

Cause #6: Specifying an Invalid Element

How to Fix It

Не пропустите эти материалы по теме: