Search code examples
excelvbainheritancecollectionspolymorphism

Excel VBA: Why "Sheets" collection is not a "Collection"?

A simple question on Excel VBA: If "Sheets" object is a collection, why the following sentence returns false? (entered at the Inmediate panel)

debug.print TypeOf Sheets Is Collection
Falso

I have discovered it because I made a function that takes a Collection as parameter. The function works with variables that I have declared as Collection, but doesn't work with other collections (such as Sheets collection).

Perhaps VBA is failing in inheritance and/or polymorphism?

Edit: As JosieP and mehow have explained, the Sheets collection doesn't inherits from the Collection class/interface, so it is not a Collection, and can't be used polymorphically as a Collection.

So now the question is: Why Sheets isn't a subclass of Collection? Why they haven't inherited from Collection? It should be a piece of cake, given the methods/properties already implemented into Sheets. As I can see, the Collection class/interface only requires 4 methods:

Add
Count
Item
Remove

And Sheets already implements 4 similar methods/properties:

Add
Count
Item
Delete

So the mapping would be trivial. They could have converted Sheets collection into a Collection easily.

Edit 2: I thank mehow for the suggestion on asking MS, but I won't do it, since they will probably say "it is not a design bug, it is a feature" :). More food for thinking: Run the following code in a new workbook:

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

It is strange to me that both "col" and "Sheets" can be iterated the same way, with a "for each" loop, but they don't share a common interface. If I had to design the classes, the hierarchy would be:

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

and Sheets would be an object (instance) of cSheets class.

In this way, the function I made (that currently takes a Collection parameter), would take a iCollection parameter, so it could work both with a Collection instance and with Sheets.

Solution

  • Sheets is a collection of Object type and not an object of Collection type.

    Like JosieP said

    the Sheets class does not inherit from the VBA Collection nor does it implement a Collection interface (there is no Remove method for example)

    should be enough to understand.

    MSDN reference

    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

    To confirm open the Object Browser with F2 and type find Sheets

    Address your second question to Microsoft. But bear in mind that Collection can be empty while the Sheets always needs to have at least one Sheets or Chart object.

    Lots of things depend on the Sheets collection object in Excel that's why I think it had to be a separate class. I can't see nothing wrong by Implementing ICollection interface and providing a separate implementation but like I said ask Microsoft (good question).

    The for each loop is possible because each VBA class has attributes. Not all attributes are visible via the IDE but they do exist (more of VB6 general knowledge than just reference to VBA).

    Ok, now to answer your third question. You can iterate over a collection because it implements IUnknown interface NewEnum() (enumarable method). You specify the default iterating object over a collection of your type (in Sheets case its Sheets collection).

    See this link for a clearer explanation of the enumerable methods.

    See this answer to better understand how the iteration is possible.