Inspecting elements of a collection

[tiredofit]

This code checks the version of Excel installed:

Option Explicit


Sub Test()


    Dim abc As Collection
    Set abc = New Collection
    
    abc.Add Module2.Version
    
    abc.Item (0)  ' FAILS HERE
    
End Sub

Option Explicit


Public Function Version() As Collection
    
    Dim Coll As Collection
    Set Coll = New Collection
    
    With ThisWorkbook
        
        Select Case Val(Application.Version)
            
            Case Is < 12
            
        
                ' Excel 97-2003
                
                Coll.Add Item:=".xls"
                Coll.Add Item:=-4143
                        
            Case Else
            
            
                'Excel 2007-2016
                
                Select Case ThisWorkbook.FileFormat
                
                    Case 51
                                    
                        Coll.Add Item:=".xlsx"
                        Coll.Add Item:=51
                
                    Case 52
                    
                        Select Case .HasVBProject
                        
                            Case True
                                            
                                Coll.Add Item:=".xlsm"
                                Coll.Add Item:=52
                                
                            Case False
                            
                                Coll.Add Item:=".xlsx"
                                Coll.Add Item:=51
                        
                        End Select
                
                    Case 56
                                        
                        Coll.Add Item:=".xls"
                        Coll.Add Item:=56
                    
                    Case Else
                    
                        Coll.Add Item:=".xlsb"
                        Coll.Add Item:=50
    
                End Select
        
        End Select
        
    End With
    
    Set Version = Coll
    
End Function

What I can't understand is when I try to inspect the elements of the collection using:

abc.Item (0)

it fails.


though this correctly shows 1:

abc.Count

What is wrong?


Thanks

 

[RoryA]

The index starts at 1, not 0, though I'm not really sure what you expect that line to actually do.

 

[John_w]

In addition, the Version function adds a Collection to the abc Collection, therefore:

    Debug.Print abc.Item(1).Item(1)
    Debug.Print abc.Item(1).Item(2)
    
    'or
    Debug.Print abc(1).Item(1)
    Debug.Print abc(1).Item(2)

 

[tiredofit]

Thanks, just what I wanted.

I expected it return the file extension and format.