Fix Subscript Out of Range Error in Visual Basic for Applications (VBA)



RECOMMENDED: Click here to fix Windows errors and optimize system performance

VBA Subscript out of range error occurs because the object we want to access does not exist. This is a type of error in VBA coding and a “Run Time Error 9”. It is important to understand the concepts to write effective code, and it is even more important to understand the error of your VBA code to debug the code effectively.

If you and your coding error do not know what this error is when you are absent.

A doctor cannot give drugs to his patient without knowing the disease. While the doctor and the patient know that there is a disease (error), it is important to understand the disease (error) as giving her medication. If you fully understand the error, it is much easier to find the solution.

Another reason may be that you do not have a standard printer on your computer. There are several solutions to solve this Runtime Error 9 problem, so try them all in the right order.

Adding a default printer

  1. Connect a printer to your computer, plug in the power cord and turn it on.
  2. Go to “Start” > “Control Panel” > “Printers and Fax Machines”.
  3. Click on “Add Printer” and follow the on-screen instructions to add a default printer.

Check the Arrays

It is possible that you have set an incorrect value for the array element. It is therefore recommended to recheck the value you have defined for the array element and make sure it is correct. Also make sure to check the table declaration and check the upper and lower limits. If the tables have been resized, be sure to use the LBound and UBound functions to condition access. Check the spelling of the variable name if the index is a variable.

Define the Number of Elements

In some cases, you may not have defined the number of elements in your code that will trigger the error. It is recommended to specify the number of elements in the table using the Dim or ReDim functions.

How to avoid the “Out of Range Error Subscript” error in VBA

Avoid using the functions integrated in Excel: ActiveWorkbook, ActiveSheet and Selection: Enter the return values and prefer qualified expressions.



Fix Update September 2019:

We recommend you try using this new tool. It fixes a wide range of computer errors, as well as protecting against things like file loss, malware, hardware failures and optimizes your PC for maximum performance. It fixed our PC quicker than doing it manually:

  • Step 1 : Download PC Repair & Optimizer Tool (Windows 10, 8, 7, XP, Vista – Microsoft Gold Certified).
  • Step 2 : Click “Start Scan” to find Windows registry issues that could be causing PC problems.
  • Step 3 : Click “Repair All” to fix all issues.

download
(optional offer for Advanced System Repair Pro -> Website | EULA | Privacy Policy | Uninstall)



Use the integrated only once and only in the most external macros (subs) and capture when starting the macro, e. g.

set wkb = ActiveWorkbook
set wks = ActiveSheet
Salt set = Selection
During and in macros, these integrated names are not reliable, but the return values are recorded, e. g.

Set wkb = Workbooks.add’ instead of Workbooks.add without entering a return value.
wkb.Activate’ instead of Activeworkbook.Activate
Also try to use qualified expressions, for example

wkb.Sheets(“Sheet3”).name = “foo” ‘ instead of Sheets(“Sheet3”).name = “foo”.
or

Set newWks = wkb.Sheets.Add.
newWks.name = “bar”‘ instead of ActiveSheet.name = “bar”.
Use qualified expressions, e. g.

newWks.name = “bar”‘ instead of `xyz.Select’ followed by Selection.name = “bar”.
These methods generally work better, produce less confusing results, are more robust to refactoring (for example, moving lines of code within and between methods), and work better in all versions of Excel. For example, the selection changes differently from one version of Excel to another during the execution of the macro.

Also note that you will probably find that using more qualified expressions does not require you to activate almost as many functions. (This may mean that the screen flashes less for the user.) So the entire Windows(expression) line. Activate could simply be deleted instead of being replaced by wkb. Activate at all.

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/subscript-out-of-range-error-9



RECOMMENDED: Click here to troubleshoot Windows errors and optimize system performance