I have inherited a workbook template with a lot of VBA in it. Ever since we switched from Office 2003 to Office 2010 (and I converted the file to 2010 format), I've encountered a lot of perplexing problems. This is the latest one.

In the template there are a bunch of worksheets. One of them has VBA attached to the worksheet's Calculate event. If I have the template open, and then I open another workbook, the Calculate event fires for the worksheet, and I get an error on the following line of code:



Run-time error '9':
Subscript out of range

Now, I can go into Debug mode with this error. So, I do so. Then, I go to the workbooks and use CTRL-TAB to switch the view to make the template the active workbook. If I hit F5 to continue, Excel goes on it's merry way as if nothing was wrong. As long as the template is NOT the active workbook, I get this error message every time I do something that triggers the Calculate event (like hitting F9, or opening another file).

Any advice on what I can do? Is there maybe an If test I can wrap around my code to test if the template is the activeworkbook? Since it's a template, the filename is not as predictable as you might think. I'd need to address it's CURRENT filename, as a workbook, not just the template's filename.

Dennis

When you use the Sheets object, the object context is actually ActiveWorkbook.Sheets(). I think if you change the code to ThisWorkbook.Sheets(...), everything should work.

Hope this helps.

Tim

Tim,

Wow, that did it. Can't believe I've never heard of the "ThisWorkbook" object before this.

Now, if you'll excuse me, I'm off to apply this to a few hundred lines of code.

Thanks,

Dennis

Glad to help! Good luck with it.

If you look at the project in the VBA Project Explorer, You will actually see ThisWorkbook in the tree under "Microsoft Excel Objects". This is where you can put it in Workbook-level event handlers Like Workbook_Open() and Workbook_Activate().

Tim

Tim,

It's just one of many issues that have come up since the upgrade. Here's an example of what I've had to do to keep the workbook template from crashing. In 2003 this worked just fine:



Basically, the selected value from a combobox was written to a named range. Now the code looks like this:



Now that I look at it, I'm wondering if I should also add "ThisWorkbook." before the Range...

Dennis

Dennis, isn't this pretty much what we talked about in the last post exactly? fully qualify everything and the code will be much more reliable. QUALIFY, QUALIFY QUALIFY!

Nothing should be just "range" or just "sheets" or just "workbooks()" or just "cells".

Rather, everything should be:

ThisWorkbook.Worksheets("Sheet1").Range
ThisWorkbook.Worksheets("Sheet1")
ThisWork.Worksheets("Sheet1").Cells

Isaac,

I discovered this new problem while I was wrapping up fully qualifying all of my sheet references.

<Rant>

As you can see, the dyke is still full of holes, and I'm still plugging them up with every finger I have... It's just amazing to me how much less tolerant of this sort of thing Excel got between 2003 and 2010. You'd think it would've become MORE intuitive about this sort of thing, not LESS so.

For example, because the error was occurring in a PRIVATE subroutine that was a worksheet's Calculate event, you'd think that if you didn't qualify which workbook, you'd assume it was the workbook that the worksheet belonged to. And apparently, Excel used to think that way back in 2003, because this error never occured until now, and the code has been in place for about ten years.

</rant>

Dennis