Run-time Error 9 subscription out of range : How to Fix

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

 

1st one min

 

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.

 

rename it min

 

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

 

workspace name change min

 

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.

 

excel options min 1

 

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.

 

trust center min 1

 

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.

 

enable macros min

 

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“.

 

develope visual basic min

 

Step 3 – When you have opened the Visual Basic console, select the step that has the suspicious value.

 

select the line min

 

Step 4 – After this, open the “Debug” menu and use the tools there to find out the bug that is causing this subscript error.

 



debug it min

 

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

 

open browse min e1707839238345

 

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.

 

open and repair min e1707839259705

 

After loading up the Excel file, you can finally run the visual basic code without any more issues.