excel - 何时评估 VBA 中的条件？

Question

我想问一下何时评估 VBA（可能是多种语言）中的条件。我正在尝试编写从 excel 表中的两个表创建两个字典的代码。第一个字典键是单词的英文名称，值是带有其他语言单词的类对象（实例）。

第二个字典键是英文月份名称，值是具有月份名称和索引的类对象（实例）。

因为第一个字典可以包含随机单词而不仅仅是几个月我想从第一个字典中删除随机键：值对。

我不明白为什么我必须输入注释的代码行来纠正工作功能？为什么条件可以高于 dictionary.Count 以及何时评估。

谢谢你的回答。

Sub makro()
    Dim list_dict As Object

    Set list_dict = get_list_dict()

    Set list_dict = Nothing
End Sub

Function get_list_dict() As Object

Dim list_dict As Object
Dim months_dict As Object
Dim list_object As List
Dim months_object As Months
Dim object_index As Integer
Dim row_number As Integer

Set list_dict = CreateObject("Scripting.Dictionary")
Set months_dict = CreateObject("Scripting.Dictionary")

For row_number = 2 To ThisWorkbook.Sheets("List").Cells(2, "A").CurrentRegion.Rows.Count
    Set list_object = New List
    
    list_object.english = ThisWorkbook.Sheets("List").Cells(row_number, "A").Value
    list_object.slovak = ThisWorkbook.Sheets("List").Cells(row_number, "B").Value
    list_object.czech = ThisWorkbook.Sheets("List").Cells(row_number, "C").Value
    
    list_dict.Add list_object.english, list_object
Next row_number

For row_number = 2 To ThisWorkbook.Sheets("Months").Cells(2, "A").CurrentRegion.Rows.Count
    Set months_object = New Months
    
    months_object.name = ThisWorkbook.Sheets("Months").Cells(row_number, "B").Value
    months_object.index = ThisWorkbook.Sheets("Months").Cells(row_number, "A").Value
    
    months_dict.Add months_object.name, months_object.index
Next row_number

For object_index = 0 To list_dict.Count - 1
    If Not months_dict.Exists(list_dict.Keys()(object_index)) Then

        list_dict.Remove list_dict.Keys()(object_index)
        
        ' this one I understand 
        object_index = object_index - 1
        
        ' this one not 
        If object_index = list_dict.Count - 1 Then Exit For
        
    End If
    
Next object_index

Set get_list_dict = list_dict

Set list_dict = Nothing
End Function

列表类：

Public english As String
Public slovak As String
Public czech As String

月班：

Public index As Integer
Public name As String

标签： excelvba

Answer 1

在循环期间删除项目时，最好向后工作，因此不需要更正删除的项目。生成的代码更容易推理。

For object_index = list_dict.Count - 1 to 0 Step - 1
    If Not months_dict.Exists(list_dict.Keys()(object_index)) Then
        list_dict.Remove list_dict.Keys()(object_index)
    End If
Next object_index