Worksheets("name") vs Sheets("name")

P

Paul Lautman

Can anyone tell me the difference between the 2 forms of referring to
worksheets as shown in the subject?

I'm sure there must be a difference, but it is obviously too subtle for me.
 
F

Frank Kabel

Hi
the sheets collection would also include 'Charts', Excel4
macro pages, etc.

Worksheets only refers to the worksheet elements (without
chart pages for example)
 
T

Tom Ogilvy

Just to add, if you use either approach with a worksheet, you get the same
type object

? typename(Sheets("Sheet1"))
Worksheet
? typename(worksheets("Sheet1"))
Worksheet


A worksheet. There is no Sheet object. Using Sheets with a Chart would
return a Chart. (for example)

? typename(sheets("chart1"))
Chart

if you have other type sheets than worksheets, then if you did
Worksheets("Sheet1").Index, the value returned is an index into the Sheets
collection, not the worksheets collection. There is no attribute that will
return the index into the Worksheets collection (just for interest).

again, to illustrate. (tabs are Chart1, Sheet1, Sheet2, Sheet3)

? worksheets("Sheet1").Index
2
? worksheets(2).Name
Sheet2
? sheets(2).name
Sheet1
 
Top