Excel VBA工作簿未添加到对象集合 [英] Excel VBA Workbook not adding to Object Collection

问题描述

我正在尝试创建一个工作簿集合，这将使我能够更轻松地跟踪所有已打开的工作簿.因为工作簿每次都将以不同的方式命名，并且因为我需要在不假定Excel没有打开其他工作簿的情况下进行此项工作，所以我无法使用索引.因此，我决定使用集合.但是，当我放入第二本工作簿时，我总是收到错误消息，我不确定发生了什么.我已经在两个模块中设置了代码(我不确定这是否会有问题)，但是我只是提供了相关代码以便于阅读.

I am trying to create a collection of workbooks that will allow me to more easily keep track of all the workbooks that are opened. Because the workbooks will be named differently each time, and because I need to make this work without assuming that Excel has no other workbooks open, I cannot use the index. Therefore I have decided to use a collection. However, I keep getting an error when i put in a second workbook, and I am not sure what is going on. I have set the code up in two modules (I am not sure if that would be a problem), but i have just provided the relevant code for easy reading.

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 

我不确定这是因为集合位于另一个模块中，还是上面的代码有问题.我完全不知所措.任何帮助都会很棒.

I am not sure if it is because the collection is in a different module, or if there is something wrong with my code above. I am at a total loss. Any help would be great.

更新

此后，我已经回答了这个问题，但是在相关说明中，似乎每次我引用该收藏集时，它都会抹掉该收藏集的所有先前部分.我将此代码放在答案中，但我在此处添加了修改内容以供参考:

I have since answered this question, but on a related note, it seems that each time i reference the collection it is wiping out all of the previous parts of the Collection. I placed this code below in my answer, but i am adding it here modified for reference:

'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

我不知道发生了什么.似乎您无法通过VBA中的集合引用?我已经阅读了有关New操作如何实际上没有创建对象的文章，但是我不清楚.如果您认为这应该是一个单独的问题，请告诉我，我会这样做.谢谢.

I don't understand what is going on. It seems like you are unable to pass the reference of collections in VBA? I have read posts about how the New operation doesn't actually create an object, but that was unclear to me. If you think this should be a separate question let me know and i will make it so. Thanks.

推荐答案

上面的代码已被重写，以防止页面上包含太多代码.我不确定我做了什么更改来解决此问题，因此，如果有人想解决原始问题，我会在此添加以前的原始代码.以后每当我遇到问题时，我都应该发布整个代码:

The code above was rewritten to keep from having too much code on the page. I am not sure what changes I made fixed the issue, so I have added the original code I had before here in case someone wants to solve the original problem. I should probably post the whole code whenever i have problems in the future:

'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

在阅读完我刚刚在上面输入的代码后，我意识到我在login_click()子内部创建了集合，从而产生了范围问题.很抱歉这个问题，但至少可以解决我的问题.

And after reading the code I just entered above, I realize that I create the collection inside the login_click() sub, thus creating a scope issue. Sorry about the question, but at least it helped solve my issue.

这篇关于Excel VBA工作簿未添加到对象集合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！