Geek Answers Handbook

Excel VBA: Why is the Sheets collection not a collection?

A simple question in Excel VBA: if the Tables object is a collection, why does the following sentence return false? (entered on the Inmediate tab)

debug.print TypeOf Sheets Is Collection
Falso

I discovered this because I created a function that takes a Collection parameter as a parameter. The function works with variables that I declared as Collection, but does not work with other collections (for example, with a collection of sheets).

Perhaps VBA is failing in inheritance and / or polymorphism?

Change . As JosieP explained and I understood, the Sheets collection is not inherited from the Collection / interface class, therefore it is not a collection and cannot be used polymorphically as a collection,

So now the question is: why is Sheets not a subclass of Collection? Why didn’t they inherit from Collection? This should be a piece of cake, given the methods / properties already implemented in Sheets. As I can see, only 4 methods are required for the Collection class / interface:

Add
Count
Item
Remove

And in the tables 4 similar methods / properties are already implemented:

Add
Count
Item
Delete

So the mapping would be trivial. They could easily transform a collection of sheets into a collection.

Change 2 . I thank you for the suggestion about MS, but I won’t do it because they will probably say: "This is not a design error, this is a feature" :). More food for thought: run the following code in a new book:

Sheets.Add.name = "sh2"
Sheets.Add.name = "sh3"

Dim col As Collection
Set col = New Collection
col.Add Sheets(1)
col.Add Sheets(2)
col.Add Sheets(3)

For Each ele In col
    Debug.Print "ele in col:  " & ele.name
Next ele

For Each ele In Sheets
    Debug.Print "ele in Sheets:  " & ele.name
Next ele

, "col", "Sheets" , " ", . , :

    iIterable       (interface)
        |
        |
    iCollection     (interface)
       / \
      /   \
     /     \
Collection  cSheets    (classes)

() cSheets.

, ( Collection) iCollection, Collection, .

+4
1

Sheets - Object, Collection.

JosieP

Sheets VBA Collection ( Remove )

, .

MSDN

enter image description here

Sub Main()

    Dim c As Collection
    Set c = New Collection

    Debug.Print VarType(c), TypeOf c Is Collection

    Debug.Print VarType(Sheets), TypeOf Sheets Is Object

End Sub

, Object Browser F2 find Sheets

Microsoft. , Collection , Sheets Chart.

Sheets Excel, , . , ICollection , , , Microsoft ( )

for each , VBA . IDE, ( VB6, VBA).

, , . , IUnknown NewEnum() ( ). - ( Sheets Sheets).

. .

. , , .

+6

More articles:


All Articles