未添加到对象集合的Excel VBA工作簿
问题描述:
我正在尝试创建一个工作簿集合,这将允许我更轻松地跟踪打开的所有工作簿。由于工作簿每次都会有不同的命名,并且因为我需要在不假设Excel没有打开其他工作簿的情况下使其工作,所以我无法使用索引。因此我决定使用一个集合。但是,当我放入第二个工作簿时,我不断收到错误信息,但我不确定发生了什么。我已经将代码设置为两个模块(我不确定这是否会成为问题),但我刚刚提供了相关代码以便于阅读。未添加到对象集合的Excel VBA工作簿
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
我不确定是因为集合在不同的模块中,还是上面的代码有问题。我处于全面亏损状态。任何帮助都会很棒。
UPDATE
因为我已经回答了这个问题,但在一个相关的说明,似乎每一次我引用它是消灭所有集合的前几部分的集合。我把这段代码放在我的答案中,但我在这里添加它作为参考:
'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操作如何实际上不创建对象的文章,但对我而言这并不明确。如果你认为这应该是一个单独的问题,让我知道,我会做到这一点。谢谢。
答
上面的代码被重写,以防止在页面上有太多的代码。我不确定我是否修改了这个问题,所以我添加了原来的代码,以防有人想解决原始问题。我也许应该张贴整个代码每当我对未来的问题:
'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()子里面,这样就产生一个范围问题。对这个问题抱歉,但至少它帮助解决了我的问题。
此代码适用于我... – Trace 2012-07-31 16:14:26
不要在您对'AddNewFile'的调用中使用小括号 – 2012-07-31 16:17:05
而且我明确将第二个子项添加到单独的模块中。 – Trace 2012-07-31 16:17:05