Howto declare and fill a custom collection of worksheets ?

[minimaster]

In the Excel object model we have the built-in collections Worksheets
and Sheets.
I like to create a custom collection of worksheets which will contain
only a sub set of these collections.
Can someone help me how I have to declare such a custom collection and
how I can add sheets to it.

 

[minimaster]

I figured it out myself.

Sub WorksheetCollectioTest()

Dim wslist As New Collection

wslist.Add Worksheets("Sheet1")
wslist.Add Worksheets("Sheet2")

Dim ws As Worksheet

For Each ws In wslist
Debug.Print ws.Name
Next ws

End Sub

 

[minimaster]

Is it somehow possible to create a specific Worksheets collection
rather than a generic collection in order to get access to built-in
methods and properties fro the worksheets collection?

In excel 2007 this code creates an error 13 (type mismatch) for the
set command.

Dim wsColl As Worksheets
Set wsColl = ActiveWorkbook.Worksheets

Anyone an idea how I can create a custom Worksheets collection with
the same methods as the built-in Worksheets collection?

 

[Jim Cone]

Worksheets is the collection of worksheets in the active workbook, so using...
Worksheets(3) gives you access to the properties and methods of the third sheet in the workbook.

If you want to declare an object then...
Dim wsColl as Sheets
Set wsColl = Worksheets
wsColl(3) ' same as Worksheets(3)

If you want only specific worksheets then...
Dim wsColl As Sheets
Set wsColl = Worksheets(Array("Sheet1", "Sheet2", "Sheet4"))
(Note that here, wsColl(3) is Sheet4 - which could be anywhere in the workbook)

Also, see ActiveWindow.SelectedSheets
--
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware .
(free and commercial excel programs)




"minimaster" <[email protected]>
wrote in message
news:14154081-10c1-4163-94ff-e843d204c959@j31g2000yqe.googlegroups.com...

 

[Jim Cone]

Correction...
Worksheets(3) gives you access to the properties and methods of the third sheet in the workbook.
-should read-
Worksheets(3) gives you access to the properties and methods of the third worksheet in the
workbook.

 

[minimaster]

Thx a lot. Declaring the object wsColl "as Sheets" does the trick.
Is it a bug that I can't declare it "as Worksheets" collection?

 

[Jim Cone]

Re: "Is it a bug..."

I don't know.
Excel coding mysteries are usually explained as "being by design". It is what it is.

I have often wondered why there is no "Sheet" data type.
To iterate the Sheets collection, one has to use an "Object" data type...
Dim Sht as object
For Each Sht in Sheets
Next
--
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware .
(Formats & Styles xl add-in: lists/removes unused styles & number formats - in the free folder)



"minimaster" <[email protected]>
wrote in message
news:88825d3e-38a6-4c41-8e20-bb00a74a8cdb@m10g2000yqd.googlegroups.com...