I have figured out that the Worksheets and Sheets
properties both return a Sheets collection, not a
Worksheets collection.

How do I then create a Worksheets collection, that is a
collection including only worksheets but excluding chart
sheets?

By the way, is there any logical explanation for why the
Worksheets property was designed to return a Sheets
collection and not a Worksheets collection? I find this
to be one of the most CONFUSING aspects of working with
Excel VBA.

Tony Lin
Fremont, CA

Hi Tony,

If you want to create a Worksheets collection, you may create a workbook
first.
Sub f()
Dim wb As Workbook
Set wb = Application.Workbooks.Add
Dim ws As Worksheet
Set ws = wb.Worksheets.Add
End Sub
The wb.Worksheets is the newly created worksheets collection.

If you want to use a collection to collect the worksheet object, you may
try the collection object. e.g.
Sub f()
Dim wb As Workbook
Set wb = Application.Workbooks.Add
Dim ws As Worksheet
Set ws = wb.Worksheets.Add
Dim wc As Collection
Set wc = New Collection
wc.Add Item:=ws
End Sub

Excel has a hierachical structure, where the application object is the
toppest object. The workbooks is its children. Similarly, the Worksheets is
the property of application, sheets or workbook, which used to collect the
worksheet.


Regards,
Peter Huang
Microsoft Online Partner Support
Get Secure! www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

--------------------
From: "Tony Lin"
References:

Subject: How do I create a Worksheets object
Date: Thu, 21 Aug 2003 09:23:16 -0700
Lines: 181
MIME-Version: 1.0
Content-Type: multipart/alternative;
boundary="----=_NextPart_000_0026_01C367C5.DA1C93B0"
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID:
Newsgroups: microsoft.public.excel.programming
NNTP-Posting-Host: 12-235-103-45.client.attbi.com 12.235.103.45
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftn gp13.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.excel.programming:408838
X-Tomcat-NG: microsoft.public.excel.programming

Peter,
As I said in my original post and which you repeated, since
Application.Worksheets, workbook.Worksheets, Application.Sheets, and
workbook.Sheets all return a Sheets collection, and not a Worksheets
collection, how do you actually create a Worksheets collection?
It seems to me that the Worksheets collection is effectively inaccessible,
right?
Do you agree? Or is there another way to get a Worksheets object?
And why such strange behavior for the Worksheets property? Did the Excel
VBA team at Microsoft make a mistake?
Tony Lin
"Peter Huang [MSFT]" wrote in message
...
Hi Tony,

I have figured out that the Worksheets and Sheets
properties both return a Sheets collection, not a
Worksheets collection.

WorkSheets Property
For an Application object, returns a Sheets collection that represents
all
the worksheets in the active workbook. For a Workbook object, returns a
Sheets collection that represents all the worksheets in the specified
workbook. Read-only Sheets object.
Sheets Property
Returns a Sheets collection that represents all the sheets in the active
workbook, for an Application object. Returns a Sheets collection that
represents all the sheets in the specified workbook, for a Workbook
object.
Read-only Sheets object.

How do I then create a Worksheets collection, that is a
collection including only worksheets but excluding chart
sheets?

By the way, is there any logical explanation for why the
Worksheets property was designed to return a Sheets
collection and not a Worksheets collection? I find this
to be one of the most CONFUSING aspects of working with
Excel VBA.

I think the chart sheet is in the application.charts collection instead
of
the application.worksheets collections. That is to say, the
application.sheets collection equals to application.charts collection
plus
application.worksheets collections. Although worksheets will return
sheets
collections, the sheet is the sheets collection is worksheet.


Regards,
Peter Huang
Microsoft Online Partner Support
Get Secure! www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no
rights.
--------------------




Regards,
Peter Huang
Microsoft Online Partner Support
Get Secure! www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

I'm using Excel 2002 and Excel 2003 Beta. I have one workbook open
containing one worksheet and one chart sheet. Both the
ThisWorkbook.Worksheets and the Application.Worksheets contain one
member respectively of type Worksheet.

I concluded that both Worksheets collections include only worksheets
and exclude chart sheets in these versions of Excel.

You *could* write your own collection class. This would consist of a
class module containing a private instance of a collection object. You
would provide public wrapper methods/properties for the collection's
Add and Item properties, plus Count and Remove if appropriate (making
the Item property the default property of the class is recommended but
I have a feeling this is Excel 2000 and above only). The argument for
the Add method would be of type Worksheet meaning that only Worksheet
objects could be added to the collection from outside of the class.

Here's a link to a VB6 KB article about custom collection classes:

http://msdn.microsoft.com/library/de...ofbrick s.asp

(Peter Huang [MSFT]) wrote in message ...
Hi Tony,

If you want to create a Worksheets collection, you may create a workbook
first.
Sub f()
Dim wb As Workbook
Set wb = Application.Workbooks.Add
Dim ws As Worksheet
Set ws = wb.Worksheets.Add
End Sub
The wb.Worksheets is the newly created worksheets collection.

If you want to use a collection to collect the worksheet object, you may
try the collection object. e.g.
Sub f()
Dim wb As Workbook
Set wb = Application.Workbooks.Add
Dim ws As Worksheet
Set ws = wb.Worksheets.Add
Dim wc As Collection
Set wc = New Collection
wc.Add Item:=ws
End Sub

Excel has a hierachical structure, where the application object is the
toppest object. The workbooks is its children. Similarly, the Worksheets is
the property of application, sheets or workbook, which used to collect the
worksheet.


Regards,
Peter Huang
Microsoft Online Partner Support
Get Secure! www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

--------------------
From: "Tony Lin"
References:

Subject: How do I create a Worksheets object
Date: Thu, 21 Aug 2003 09:23:16 -0700
Lines: 181
MIME-Version: 1.0
Content-Type: multipart/alternative;
boundary="----=_NextPart_000_0026_01C367C5.DA1C93B0"
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID:
Newsgroups: microsoft.public.excel.programming
NNTP-Posting-Host: 12-235-103-45.client.attbi.com 12.235.103.45
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftn gp13.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.excel.programming:408838
X-Tomcat-NG: microsoft.public.excel.programming

Peter,
As I said in my original post and which you repeated, since
Application.Worksheets, workbook.Worksheets, Application.Sheets, and
workbook.Sheets all return a Sheets collection, and not a Worksheets
collection, how do you actually create a Worksheets collection?
It seems to me that the Worksheets collection is effectively inaccessible,
right?
Do you agree? Or is there another way to get a Worksheets object?
And why such strange behavior for the Worksheets property? Did the Excel
VBA team at Microsoft make a mistake?
Tony Lin
"Peter Huang [MSFT]" wrote in message
...
Hi Tony,

I have figured out that the Worksheets and Sheets
properties both return a Sheets collection, not a
Worksheets collection.

WorkSheets Property
For an Application object, returns a Sheets collection that represents
all
the worksheets in the active workbook. For a Workbook object, returns a
Sheets collection that represents all the worksheets in the specified
workbook. Read-only Sheets object.
Sheets Property
Returns a Sheets collection that represents all the sheets in the active
workbook, for an Application object. Returns a Sheets collection that
represents all the sheets in the specified workbook, for a Workbook
object.
Read-only Sheets object.

How do I then create a Worksheets collection, that is a
collection including only worksheets but excluding chart
sheets?

By the way, is there any logical explanation for why the
Worksheets property was designed to return a Sheets
collection and not a Worksheets collection? I find this
to be one of the most CONFUSING aspects of working with
Excel VBA.

I think the chart sheet is in the application.charts collection instead
of
the application.worksheets collections. That is to say, the
application.sheets collection equals to application.charts collection
plus
application.worksheets collections. Although worksheets will return
sheets
collections, the sheet is the sheets collection is worksheet.


Regards,
Peter Huang
Microsoft Online Partner Support
Get Secure! www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no
rights.
--------------------




Regards,
Peter Huang
Microsoft Online Partner Support
Get Secure! www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.