我有一个表格，它调用了Module1的许多子程序。在Module1中，我有一个公开声明的对象变量。使用该变量的想法是创建一个后期绑定，scripting.dictionary以避免必须对我当前的vba项目添加太多引用。该词典已成功创建，并已填充到Sub1中。但是，一旦Sub1完成并且Sub2被调用，我注意到字典变量已经还原回其原始类型的Object。
登录表格：
Public progresslbl As Object, subprogresslbl As Object, progressbar As Object, webBr As Object
Private Sub GetExports_Click()
...
...
...
progresslbl.Caption = "Requesting Exports"
RequestExports
'Wait for all emails to be received (reset currentsupplier and count emails, wait for currentsupplier = suppliercount)
WaitforEmails 'Still needs to be created
'Download Exports & Save them to destination user specifies
DownloadFiles
'Restore Outlook: remove temp folder and rule
progresslbl.Caption = "Restoring Outlook Settings"
RestoreOutlook
模块1：
Public IE As Object, downloadTo As String, Outlook As Object, Items As Object, err As Integer, itemdic As Object
'itemdic shows as type Object in Watch window
Sub RequestExports()
Set itemdic = CreateObject("Scripting.Dictionary"): itemdic.comparemode = vbTextCompare
'itemdic now shows at type scripting.dictionary in Watch window
For x = 1 To suppliercount
With IE.Document
esplogin.subprogresslbl.Caption = "Searching for Supplier " & x & " of " & suppliercount
currentsupplier = ActiveSheet.Range("A" & x).Value
delay 3 'Wait 3 seconds to allow screen to load fully
.getElementById("supplierSearchTextBox").Focus 'Select Search Box
.getElementById("supplierSearchTextBox").Value = currentsupplier 'Fill in Search Box
'Invoke keypress event so the contents are detected
Set evt = .CreateEvent("keyboardevent"): evt.initEvent "change", True, False
.getElementById("supplierSearchTextBox").dispatchEvent evt
Dim searchButton As Object: Set searchButton = .getElementsByTagName("a")(5)
searchButton.Click
delay 3
Dim supplierLink As Object: Set supplierLink = .getElementsByTagName("a")(6)
'Cycle through list of suppliers in excel until we find another active one
Do While supplierLink Is Nothing
err = err + 1
esplogin.subprogresslbl.Caption = "Supplier Not Found"
delay 1
ActiveSheet.Range("A" & x).Interior.Color = vbYellow
If x = suppliercount Then Exit For
esplogin.progressbar.Width = 150 / suppliercount * x
x = x + 1
esplogin.subprogresslbl.Caption = "Searching for Supplier " & x & " of " & suppliercount
currentsupplier = ActiveSheet.Range("A" & x).Value
'Select & Fill in Search Box
.getElementById("supplierSearchTextBox").Focus
.getElementById("supplierSearchTextBox").Value = currentsupplier
'Invoke keypress event so the contents are detected
Set evt = .CreateEvent("keyboardevent"): evt.initEvent "change", True, False
.getElementById("supplierSearchTextBox").dispatchEvent evt
Set searchButton = .getElementsByTagName("a")(5)
searchButton.Click
delay 2
Set supplierLink = .getElementsByTagName("a")(6)
Loop
'Login to supplier
supplierLink.Click
While IE.Busy
DoEvents
Wend
esplogin.subprogresslbl.Caption = "Exporting Supplier " & x & " of " & suppliercount
delay 4
Dim exportButton As Object: Set exportButton = .getElementsByTagName("button")(3)
exportButton.Click
delay 1
.getElementsByTagName("select")(0).Value = "all"
.getElementsByTagName("select")(1).Value = "5"
delay 1
.getElementById("btnExport").Click 'Click Export button
delay 2
'Click Ok button to close "Export sent to email" window
Dim exportResultOK As Object: Set exportResultOK = .getElementById("exportProductModalResul").getElementsByTagName("button")(1)
exportResultOK.Click
esplogin.subprogresslbl.Caption = "Awaiting Export Confirm. Email for Supplier " & x & " of " & suppliercount
delay 1
Set eitDashboardButton = .getElementsByTagName("a")(11)
eitDashboardButton.Click
End With
'Check to see if latestExport confirmation has arrived yet
Set latestExport = Items.Find("[Subject] = ""Product Updates Product Export confirmation""")
'If we haven't already found the latestExport wait and keep checking until we do
Do While latestExport Is Nothing
Set latestExport = Items.Find("[Subject] = ""Product Updates Product Export confirmation""")
Loop
esplogin.subprogresslbl.Caption = "Received Confirm. Email for Supplier " & x & " of " & suppliercount
With latestExport
BatchID = Mid(.Body, InStr(1, .Body, "Batch ID of ", vbTextCompare) + 12, InStrRev(.Body, ".", Len(.Body) - 1, vbTextCompare) - (InStr(1, .Body, "Batch ID of ", vbTextCompare) + 12))
itemdic.Add currentsupplier, BatchID
latestExport.Subject = "Product Updates Product Export confirmation - " & currentsupplier
latestExport.Save 'Save the updated subject
End With
esplogin.progressbar.Width = 150 / suppliercount * x
Next x
esplogin.progresslbl.Caption = "Export Requests Complete"
IE.Quit
Set IE = Nothing
Exit Sub
Restore:
RestoreOutlook
MsgBox ("Issue with Export code")
End Sub
Sub WaitforEmails(Optional currentcount As Integer = 0)
////As soon as the code reaches this point the item dic variable is now a type Object again and has no values
Dim item As Object, BatchID As String, k As Object
For Each item In Items
With item
If .Subject = "Product Updates: Product Export" Then
'Instr check for batch id (ie dic key) then whatever dic value it matches replace batch id in dic with download link
For Each k In itemdic.keys
If InStr(1, .HTMLBody, k, vbTextCompare) > 0 Then
'Store the download link in place of the batch id
itemdic(k) = Mid(.HTMLBody, InStr(1, .HTMLBody, "a href=") + 8, (InStrRev(.HTMLBody, ">here") - 2) - (InStr(1, .HTMLBody, "a href=") + 8))
Exit For
End If
Next
currentcount = currentcount + 1
If currentcount = (suppliercount - errs) Then Exit For 'we have all of the emails
End If
End With
Next
If Not currentcount = (suppliercount - errs) Then Application.OnTime Now + TimeValue("00:01:00"), "WaitforEmails(currentcount)"
While Not currentcount = (suppliercount - errs)
DoEvents
Wend
Exit Sub
Restore:
RestoreOutlook
MsgBox ("Issue with WaitforEmail code")
End Sub
'When moving to sub 2 itemdic now reverts back to showing as type Object in Watch window
Sub 2()
'Work with items in dictionary
'Application or Object-defined Error I believe?
'Some error
End Sub
我的问题：
有没有一种方法可以使后期绑定的字典变量跨子（在Module1中）保持其类型（及其内容/值），而不必添加引用？
哇，所以我感觉真是个白痴。这个问题一直以来一直困扰着我，至少部分是这样。我认为这个问题有两个方面：
itemdic到到达WaitforEmails子时没有它的值。但是，我确实注意到，当我到达第一个子句的末尾时，它itemdic具有正确的类型和值。我意识到的是，当它返回到用户窗体的代码时，它在监视窗口中（暂时）显示为没有值，并且再次成为对象类型，这很奇怪，但是我想这是因为每个窗口都在当前模块正在主动执行有意义的代码的范围。一旦WaitforEmails从用户窗体调用了该子程序，并且代码正在通过该子程序itemdic中的行，则正确显示了带有值和类型为Dictionary的子程序。key我用来遍历字典中各项的变量被声明为错误，应将其声明Variant为非Object（doh！）。本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句