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

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.

Jayashree Krishna wrote:
> 
> 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

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.
-- 
HTH,

Barb Reinhardt



"Jayashree Krishna" wrote:

> 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

"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" <JayashreeKrishna@discussions.microsoft.com> wrote in 
message news:82B7C937-056E-4C18-B0FC-E3E7AE27D6D4@microsoft.com...
> 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 

Thanks to everyone for your insight.
Regards,
Krishna


"Peter T" wrote:

> "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" <JayashreeKrishna@discussions.microsoft.com> wrote in 
> message news:82B7C937-056E-4C18-B0FC-E3E7AE27D6D4@microsoft.com...
> > 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 
> 
> 
> .
>