The error message “Run-time Error 9” appears in certain scenarios in Visual Basic in Microsoft Excel. Though the main reason for this error is the range issue in the main basic code, there are certain scenarios where you may face this dilemma. In this guide, we have discussed all the possible solutions where you may face this error and how you can troubleshoot this.
Fix 1 – Check the Range mentioned in the code
The “Run-time Error 9” comes up when the range asserted in the code is misconfigured. So, in that case, check the range of variables mentioned in the code.
Example –
Sub Newfunction() Dim Array() As Variant Array(1) = "Zero" End Sub
As you can see in the code, the Array is declared as a variant but without any proper range. That is the reason, when you run this code in the VB editor, you will get a subscript error.
Just changing the range in the code, you should be good to go.
Fix 2 – Adjust the name of the worksheet
If you are working with multiple worksheets at the same time, there is a chance that the name of the worksheet presented in the code is wrong.
Example –
Sub CommandButton1_Click()
Worksheets("Sheet").Range("A1:D5").Select
Selection.Copy
Worksheets("Sheet1").Activate
Worksheets("Sheet1").Range("A1:D5").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub
As, you can see, upon running the above-mentioned VBA code, Excel will provide you with an Error Code 9 Subscript out of range. The reason for this is the range of the workbook mentioned is the Sheet and the operation is done on the Sheet 1, instead of Sheet 2.
Now, either you can do this –
Step 1 – Just right-click the worksheet and tap “Rename” to rename the worksheet.
Step 2 – Rectify the name of the workbook.
Or,
you can re-adjust the code this way –
Sub CommandButton1_Click()
Worksheets("Sheet").Range("A1:D5").Select
Selection.Copy
Worksheets("Sheet2").Activate
Worksheets("Sheet2").Range("A1:D5").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub
Run this code and you won’t get any more subscript range issues anymore.
Fix 3 – Enable Macros
If the VBA script utilizes any Macro modules and you haven’t enabled them, enabling the Macros should help you fix the problem.
Step 1 – Go to the Excel workbook and click the “File” menu.
Step 2 – This time, click the “Options” menu from the left ribbon.
Step 3 – You have to find and open the “Trust Center” tab.
Step 4 – On the right pane, click on open the “Trust Center Settings” option.
Step 5 – There are lots of options here. Choose to open the “Macros” tab.
Step 6 – Now, depending upon the macro settings in your code, turn any of these options –
Disable all macros except digitally signed macros Enable all macros (not recommended: potentially dangerous codes can run)
Step 7 – Tap “OK” to save the changes in Excel.
Now, after saving these changes in Excel, you can run the VB code once more.
Fix 4 – Debug the code step by step
Just like other terminals, the Visual Basic module does have a debugging tool. Use these to troubleshoot the code.
Step 1 – After opening the MS Excel page, go to the “Developer” tab.
Step 2 – Next, tap the first option which is “Visual Basic“.
Step 3 – When you have opened the Visual Basic console, select the step that has the suspicious value.
Step 4 – After this, open the “Debug” menu and use the tools there to find out the bug that is causing this subscript error.
Now, you can test the code out yourself.
Fix 5 – Open and repair the Excel file
It is possible that the spreadsheet itself is corrupt. So, you should repair and open the Excel file.
Step 1 – Save the changes in the MS Excel app.
Step 2 – Launch the Microsoft Excel application separately.
Step 3 – This time, tap the first menu “File“.
Step 4 – Next, go this way –
Open > Browse
Step 5 – The next step is to go to the locaiton of the Excel file, manually.
Step 6 – Next, choose to select the file in there.
Step 7 – Finally, toggle the bottom option to “Open and Repair” from the list.
After loading up the Excel file, you can finally run the visual basic code without any more issues.