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

This message apparently cover a variety of reasons that the file may not be "seen" by the application. 

Yes, and the reason for that is often the use of SELECT, SELECTION, ACTIVATE, etc. all methods that affect the screen.

Newer versions shows a significant different behavior as older versions using this methods.

But if you don't show us your code, we can't help you.

Andreas.

Answer 3

Thank you Andreas. This afternoon (Pacific time), I will provide additional coding showing the use of those methods. Palmer.

Answer 4

Andreas. I have attempted to send you a file from the VBA editor of the macros. So far I have failed to be able of attach the file. Palmer

Answer 5

Palmer,

please upload your file into my personal dropbox folder here

https://www.dropbox.com/request/xL1alVTF62mvh9HJClBw

Andreas.