with - workbooks vba

Libro de Excel VBA que no agrega a Object Collection

Question

Intento crear una colección de libros de trabajo que me permita hacer un seguimiento más fácil de todos los libros que se abren. Debido a que los libros de trabajo serán nombrados de manera diferente cada vez, y debido a que necesito hacer que esto funcione sin suponer que Excel no tiene otros libros abiertos, no puedo usar el índice. Por lo tanto, he decidido usar una colección. Sin embargo, sigo recibiendo un error cuando coloco un segundo libro de trabajo, y no estoy seguro de lo que está pasando. He configurado el código en dos módulos (no estoy seguro de si eso sería un problema), pero acabo de proporcionar el código correspondiente para facilitar la lectura.

Sub run()
   Dim usedWorkbooks As New Collection
   Dim mainWorkBook As Workbook
   Set mainWorkBook = ActiveWorkbook
   Dim testwb As Workbook

   usedWorkbooks.Add Item:=mainWorkBook, key:="main" ''Added successfully
   usedWorkbooks.Add Item:=testwb, key:="test" ''Added successsfully
   addNewFile(usedWorkBooks)
End Sub

''In a separate module
Public Sub addNewFile(ByRef usedWorkBooks as Collection) 
   Dim ptCsv As String
   ptCsv = someFilePath
   ''Filegrabber.grab simply returns the path of the desired workbook and works correctly
   ''This is not the problem, can be substituted with any file path string
   ptCsv = FileGrabber.grab(ptCsv) 
   Dim ptCsvWorkBook As Workbook
   Set ptCsvWorkBook = Workbooks.Open(ptCsv) ''Successfully opens workbook

   ''Prints out the type of object as "Workbook"
   MsgBox "the object is: " & TypeName(ptCsvWorkBook)
   ''Fails to add. Says I need an object  
   usedWorkbooks.Add Item:=ptCsvWorkBook, key:="ptCsv"
End Sub 

No estoy seguro de si es porque la colección está en un módulo diferente, o si hay algún problema con mi código anterior. Estoy en una pérdida total. Cualquier ayuda sería genial.

ACTUALIZAR

Desde entonces he respondido a esta pregunta, pero en una nota relacionada, parece que cada vez que hago referencia a la colección, está borrando todas las partes anteriores de la Colección. Coloqué este código debajo en mi respuesta, pero lo estoy agregando aquí modificado para referencia:

''UserForm to login to system
Dim usedWorkbooks As New Collection 
Private Sub login_Click()
   Dim mainWorkBook As Workbook
   Set mainWorkBook = ActiveWorkbook

   usedWorkbooks.Add Item:=mainWorkBook, key:="main"
   usedWorkBooks.Add Item:=testwb, key:="test"
   MsgBox "the size of the array is: " & usedWorkBooks.Count ''Prints out 2 as the size

   intializeProcess ''a worksheet is added to the array here
   ''This will print out as saying there are 0 worksheets
   MsgBox "the size of the array is: " & usedWorkBooks.Count
End Sub

Public Sub addNewFile(filepath As String, sheetKey As String)
   Dim newWorkBook As Workbook

   Set newWorkBook = Workbooks.Open(filepath)

   usedWorkBooks.Add Item:=newWorkBook, key:=sheetKey
   MsgBox "the size of the array is: " & usedWorkBooks.Count

End Sub

''Part of the initialize Module
Public Sub intializeProcess()
   Call LoginModule.login(username, password, "pt", ie)
   ptCsv = FileGrabber.grab(ptCsv) ''ptcsv is set to some filepath by the grab sub

   ''This will print out that the array size is 1
   UserLogin.addNewFile ptCsv, "ptCsv"  
End Sub

No entiendo lo que está pasando. Parece que no puede pasar la referencia de colecciones en VBA? He leído publicaciones sobre cómo la Nueva operación en realidad no crea un objeto, pero no estaba claro para mí. Si crees que esta debería ser una pregunta separada, avísame y lo haré. Gracias.

Answer 1

El código anterior fue reescrito para evitar tener demasiado código en la página. No estoy seguro de qué cambios haya solucionado el problema, por lo que he agregado el código original que tenía antes aquí en caso de que alguien quiera resolver el problema original. Probablemente debería publicar todo el código cada vez que tenga problemas en el futuro:

''UserForm to login to system
Dim usedWorkbooks As New Collection ''Correct placement of collection
Private Sub login_Click()
   Dim usedWorkbooks As New Collection ''Should be defined outside of this sub
   Dim mainWorkBook As Workbook
   Set mainWorkBook = ActiveWorkbook

   usedWorkbooks.Add Item:=mainWorkBook, key:="main"
   intializeProcess
End Sub

Public Sub addToWorkbookCollection(obj As Workbook, name As String)
   usedWorkBooks.Add Item:=obj, key:=name
End Sub

''Part of the initialize Module
Public Sub intializeProcess()
   ptCsv = FileGrabber.grab(ptCsv)
   Dim ptCsvWorkBook As Workbook

   Set ptCsvWorkBook = Workbooks.Open(ptCsv)

   MsgBox "the object is: " & TypeName(ptCsvWorkBook)
   UserLogin.addToWorkbookCollection ptCsvWorkBook, "ptCsv"

End Sub

Y después de leer el código que acabo de ingresar arriba, me doy cuenta de que creo la colección dentro del subclono login_click (), creando así un problema de alcance. Perdón por la pregunta, pero al menos me ayudó a resolver mi problema.