How do I create a Worksheets object

  • Thread starter Thread starter Tony Lin
  • Start date Start date
T

Tony Lin

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" <[email protected]>
References: <[email protected]>
Click to expand...
Subject: Re: 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: <[email protected]>
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!tk2msftngp13.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
Click to expand...
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
Click to expand...
VBA team at Microsoft make a mistake?
Tony Lin
Click to expand...


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/d...ingyourownclasscollectionthehouseofbricks.asp
 
Back
Top