Q:

vba: adding workbook to collection

I am trying to add an object workbook in the collection created. However, when I add a watch to the collection, I see the item1, but it says there is no variable, but there should be a workbook specified by the path. 
Do you have any idea where my mistake could come from?
Sub AddWbkToCollection()

    Dim Path As String        
    Dim Coll As Collection        
    Dim wbk As Workbook        
    Set Coll = New Collection

    Path = WshCockpit.Range("Path")        

    Set wbk = Workbooks.Open(Path)

    Coll.Add wbk, wbk.Name

    wbk.Close

    Set wbk = Nothing

End Sub

A:

Set wbk = Workbooks.Open(Path)

wbk isn't an object. it's a reference to an object, and that object is owned by Excel.
wbk.Close

Beyond this point, the wbk refers to an object that's gone. So there's still a reference in your collection, but it's a reference to nothing. VBA knows it's an object; IsObject returns True, wbk Is Nothing returns False.
But then, this throws an automation error:
Debug.Print wbk.Name

Because the object itself is gone. A Workbook object doesn't exist if it's not opened in Excel.
Therefore, a user collection of workbooks is redundant: Application.Workbooks contains all the workbooks you're ever going to need.

