Excel-字典对象的奇怪行为 [英] Excel - strange behavior of a dictionary object
问题描述
我对以下代码行有疑问:
I'm having problem with the following line of code:
Set DICT = RowMap(Range(Workbooks("A").Sheets(1).Cells(ITEM_NO_ROW, _
ITEM_NO_COLUMN), Workbooks("A").Sheets(1).Cells(ITEM_NO_ROW + 1, ITEM_NO_COLUMN).End(xlDown)))
此代码称为RowMap.我在RowMap的"End Function"处稍作休息，并在监视"窗口中检查rv和RowMap的计数.两种计数均为84.但是，一旦我按F8键进入常规程序并检查DICT的计数，它就是85，而不是84.
This code calls RowMap. I put a break at "End Function" of RowMap and check the count of rv and RowMap in the Watch Window. Both counts are 84 as they should be. However, as soon as I hit F8 which takes me to the main routine, and check the count of DICT, it is 85, not 84.
DICT是否与RowMap或rv完全相同?为什么DICT的计数增加1?哪一行代码可以做到这一点?我完全迷路了.
Shouldn't DICT be exactly the same as RowMap or rv? Why is the count of DICT incremented by 1? Which line of code makes it do that? I am completely lost.
我不知道此信息是否有帮助.上面的Set DICT行被包裹在对于rng中的每个单元格"循环中，并且该单元格被添加到DICT的末尾.
I don't know if this info would help or not. The above Set DICT line is wrapped in a "For each cell in rng" loop and it is the cell that is added to the end of the DICT.
任何帮助将不胜感激.
Function RowMap(rng1 As Range) As Object
'store item no and price in dictionary
Dim rv As Object
Dim c As Range
Dim v As long
On Error Resume Next
Set rv = Nothing
Set rv = CreateObject("scripting.dictionary")
For Each c In rng1.Cells
v = c.Value
If Not rv.Exists(v) Then
rv.Add v, c.Offset(0, 4) 'add item no and price
Else
MsgBox "Duplicate value detected in " & Book_Name & "!"
Exit For
End If
Next c
Set RowMap = rv
End Function
For Each wk In Application.Workbooks
If Left(wk.Name, 6) = "All FE" Then
ERROR_Sheet_No = ERROR_Sheet_No + 1
For Each sh In wk.Sheets
Set Report_Last_Cell = sh.Cells(5000, 3).End(xlUp)
'sort the data by group code
Set rng = sh.Range(sh.Cells(4, 1), Report_Last_Cell.Offset(0, 4))
rng.Sort key1:=sh.Cells(4, 4), order1:=xlAscending, Header:=xlNo
Set rng = sh.Range(sh.Cells(4, 3), Report_Last_Cell)
For Each cell In rng
If cell.Value <> "LAVENDER" And cell.Value <> "CLOSED" And cell.Value <> "VOID" And cell.Value <> "NO SALE" And _
InStr(cell.Value, "DISCOUNT") = 0 And InStr(cell.Value, "DEPOSIT") = 0 And Len(cell) <> 0 Then
Group_Code = cell.Offset(0, 1).Value
If Group_Code <> Old_Group_Code Then 'open the PHOTO_QUOTE file
'close the old PHOTO_QUOTE file first
On Error Resume Next
Workbooks(File_Prefix & Old_Group_Code & ".xlsx").Close
On Error GoTo 0
'open the PHOTO QUOTE file if exists
If Len(Dir(Flower_Path & File_Prefix & Group_Code & ".xlsx")) <> 0 Then 'if file is found
Workbooks.Open Flower_Path & File_Prefix & Group_Code & ".xlsx"
Photo_Quote_Book_Name = File_Prefix & Group_Code & ".xlsx"
On Error Resume Next
DICT.RemoveAll
Set DICT = Nothing
Set DICT = RowMap(Range(Workbooks(Photo_Quote_Book_Name).Sheets(1).Cells(PHOTO_QUOTE_ITEM_NO_ROW, _
PHOTO_QUOTE_ITEM_NO_COLUMN), Workbooks(Photo_Quote_Book_Name).Sheets(1).Cells(PHOTO_QUOTE_ITEM_NO_ROW + 1, PHOTO_QUOTE_ITEM_NO_COLUMN).End(xlDown)))
On Error GoTo 0
'check if ITEM NO exists
If Not DICT.Exists(cell.Value) Then
Copy_to_ERROR_sheet sh.Name, ERROR_Sheet_lastrow, 0, 0, 255
'check if price matches
ElseIf cell.Offset(0, 3).Value <> DICT(cell.Value) Then
Copy_to_ERROR_sheet sh.Name, ERROR_Sheet_lastrow, 0, 255, 0
End If
Else 'if the PHOTO_QUOTE file doesn't exist, copy shop, date, voucher no, item no, price to
' ERROR_BOOK_NAME and change color to red
Copy_to_ERROR_sheet sh.Name, ERROR_Sheet_lastrow, 255, 0, 0
End If 'If Len(Dir(Flower_Path & File_Prefix & Group_Code & ".xlsx")) <> 0 Then
Old_Group_Code = Group_Code
End If ' If Group_Code <> Old_Group_Code Then
End If 'If cell.Value <> "LAVENDER" And cell.Value <> "CLOSED" And cell.Value <> "VOID" And cell.Value <> "NO SALE" And _
InStr(cell.Value, "DISCOUNT") = 0 And InStr(cell.Value, "DEPOSIT") = 0 And Len(cell) <> 0 Then
Next 'For Each cell In rng
Next 'For Each sh In wk
End If 'If Left(wk.Name, 6) = "All FE" Then
Next 'For Each wk In Application.Workbooks
Close_PHOTO
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub 'Check_Price
推荐答案
下面是一个示例，如果您在使用字典时不小心使用监视"窗口，将会发生什么情况.
Here's an example of what can happen if you're not careful using the Watch window when working with a Dictionary.
在模块中输入此代码，并设置一个休息时间和两个手表:
Enter this code in a module and set a break and two watches as indicated :
Sub Tester()
Dim dict As Object
Set dict = CreateObject("scripting.dictionary")
dict.Add "A", 1
dict.Add "B", 2
dict.Add "C", 3 '<<< put a break here
dict.Add "D", 4
Debug.Print dict("D") '<< put a watch on `dict("D")`
Debug.Print dict.Count '<< put a watch on `dict`
End Sub
现在运行到中断处并检查监视"窗口-即使您的代码仍在中断处等待(并且尚未添加"C"键)，您的字典也已经有一个空的"D"槽(并且计数)是 3 ，而不是2).
Now run to the break and check the Watch window - even though your code is still waiting on the break (and the "C" key is not added yet), your dictionary already has an empty "D" slot (and count is 3, not 2).
即使您删除了 dict.从代码中添加"D"，4 ， dict("D")上的手表也将保留在监视"窗口中(除非您主动将其删除)，否则将继续添加该额外"键...
Even if you delete the dict.Add "D", 4 from your code, the watch on dict("D") will remain in the Watch window (unless you actively delete it) and will keep adding that "extra" key...
这篇关于Excel-字典对象的奇怪行为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
