This is really stumping me. I put a question up yesterday regarding collections being passed between modules (see here), but it doesn't seem like I am getting anymore explanations on that one, so i am attempting to restate the problem more clearly in a generic way.
I have a module (module1) and a userform (userform1). I create a collection (or array) in userform1 and add worksheet objects to this array. I then pass control to module1, which calls a sub in userform1 called addNewFile, which is supposed to add the newly created workbook to the collection. However, each time module1 calls addNewFile i get one of two scenarios: 1) the collection has been erased and all worksheets that had been added are now gone (for a collection), 2) i get an error saying that i have a type mismatch (for an array). I don't know why this is happening, so here is the code below to illustrate better. Any help would be appreciated, even if it is just to tell me that it is not possible to store worksheet objects in arrays.
UserForm1
Dim workBooksCollection as New Collection 'can also define as an array
Private Sub CommandButton1_click()
Dim mainWorkBook as workbook
Set mainWorkBook = ActiveWorkbook
Dim testwb As Workbook
workBooksCollection.Add Item:=mainWorkBook, key:="main" 'Adds successfully
workBooksCollection.Add Item:=testwb, key:="test" 'Adds successfully
MsgBox "the size of the array is: " & usedWorkBooks.Count 'Prints off as size 2
Module1.initialize
'After running initialize, prints off as size 0, meaning collection has been erased
MsgBox "the size of the array is: " & usedWorkBooks.Count 'Prints off as size 0
End Sub
Public Sub addNewFile(filepath As String, sheetKey As String)
Dim newWorkBook As Workbook
Set newWorkBook = Workbooks.Open(filepath)
MsgBox "The name of the workbook is: " & newWorkBook.name 'Prints off name of workbook successfully
workBooksCollection.Add Item:=newWorkBook, key:=sheetKey
MsgBox "the size of the array is: " & workBooksCollection.Count 'Prints off as size 1
End Sub
Module1
Public Sub intialize()
Dim filepath as string
'The filepath is set to any path of a workbook
'This will print out that the array size is 1
UserForm1.addNewFile filePath, "secondBook"
End Sub
Sorry if i seem to be beating a dead horse here, but I really don't have any idea what is going on here. I am used to the idea of collections and lists being global and not changing when being referenced by another module. Any help on what is going on here would be great.
I would like to comment, but I can't since I had to redo my account because I got locked out of my original.
If my answer isn't helpful, I will delete in a bit, but what if you replace -
Dim workBooksCollection as collection 'can also define as an array
from the UserForm module, into Module 1 as:
Public workBooksCollection as collection 'can also define as an array
Does it help?