VBA - ThisWorkbook

[Jayashree Krishna]

Hi,

In our VBA code we used Sheets("xxx") to refer to the data in the sheets in
the workbook which has the code. This worked well even if we open several
other workbooks thru that code. But suddenly, when we upgraded to Excel 2007,
this code no longer worked. Then we refered using ThisWorkbook.sheets("xxxx").

Can anybody know why "ThisWorkbook" keyword is not required in Excel 2003
VBA to refer to the sheets in the same workbook, whereas it is required in
Excel 2007 to refer to the sheets ?
Thanks,
Krishna

 

[Dave Peterson]

If you use sheets("xxx") in a routine in a general module, then this sheet will
refer to the activeworkbook. It doesn't always refer to the workbook that owns
the code.

If you use sheets("xxx") in the ThisWorkbook module, then it will refer to the
object owning the code--ThisWorkbook.

I've never seen a difference in the way xl2003 and xl2007 works. I'd bet you're
mistaken. But since you didn't actually share a small snippet of code (and
where it was located), it's difficult to test.

Personally, I think you should qualify your objects--no matter where the code is
located or what version of excel you're using.

 

[Barb Reinhardt]

It's not required in Office 2003 either, but I'd have to say that it's highly
recommended. I suspect what's happening is that while the code is running,
"ThisWorkbook" is no longer the active workbook and it's trying to find the
sheets you reference in the Active Workbook. Let me give an example.

If your active workbook name is "WB1.name" then this line

Sheets("xxx")

References the sheet named "xxx" in WB1.

If your activeworkbook name is "WB2.name", this line references sheet "xxx"
in WB2.

If your code reads

ThisWorkbook.Sheets("xxx"), there is no doubt which workbook is being
referenced.

 

[Peter T]

"ThisWorkbook" refers to the Workbook containing the code. Excel VBA
supports implicit qualification to the ActiveWorkbook and ActiveSheet etc if
not otherwise qualified. If your code workbook is not active you must
qualify with a reference to the workbook you are working on, in this case
the code workbook.

Your code workbook might be referred to in any of these ways.

ThisWorkbook
Application.Workbooks("myBook.xls")
and if it is active -
ActiveWorkbook

Nothing has changed in this respect from Excel 2003 to 2007

(It is possible to rename "ThisWorkbook")

Regards,
Peter T

 

[Jayashree Krishna]

Thanks to everyone for your insight.
Regards,
Krishna