VBA error #9 "subscript out of range"

Question

I have used VBA with Excel for many years. Since I changed from an old desktop version of Excel (2012), I have been having trouble.

The problem occurs several ways, but always with the same error message which halts the execution of the macro.

The simplest version of the problem occurs as follows:

1 I open six Excel files, five calculating workbooks, and one workbook with charts. That macro works properly.

2 Then, as an experiment, immediately I run a corresponding macro to close the same workbooks. It halts on the first workbook and displays the message.

3 Here is the amazing part, if I "comment out" all the workbooks but one, any one, it still halts on the first workbook on the list.

4 In other words, even without operating the spreadsheets as I would ordinarily, the mere act of running the closing macro is sufficient to trigger the message. 

Thank you for your help. Palmer Hinsdale

Answer 1

TBH, I never looked into the reasoning or motivation behind the change. I just know it solves errors where there were none beforehand.

Answer 2

Same answer for this as your other question(s):

We need to see your full code or better your files.

Andreas.

Answer 3

Please see the solution I posted to your previous question.

https://answers.microsoft.com/en-us/msoffice/forum/all/excel-vba-question/142a8a2b-2f41-4315-91db-632169406dd2?LastReply=true#LastReply

Older versions of Excel and VBA had the option to include the workbook's file extension when referencing another workbook. Newer versions that use individual application windows for each open workbook should always use the full workbook name and extension.

Answer 4

Newer versions that use individual application windows for each open workbook should always use the full workbook name and extension.

Why?

That would make sense if can open 2 files with the same name from different folders, but that's not possible:

Andreas.

Answer 5

The discussion on the MS Community and my searches in the general internet revealed that Error #9, "subscript out of range" covers the inability of the application to "see" the offending file in a variety of circumstances.

This message apparently cover a variety of reasons that the file may not be "seen" by the application. One individual suggested uninstalling and reinstalling the printer. However, that idea was plainly NOT APPLICABLE to my situation because this computer does not, and never has had access to a printer.

I also must confess that a side benefit of my effort to find the solution caused me to clean up some fairly Crufty code.

Thanks to all. Palmer.