When is for condition in VBA evaluated?

Question

I would like to ask when is for condition in VBA (maybe in lots of languages) evaluated. I'm trying to write code that creates two dictionaries from two tables in excel sheets. First dictionary keys are english names of words and values are class objects(instances) with word in other languages.

Second dictionary keys are months names in english and values are class objects(instances) with month name and index.

Because first dictionary can contain random words not just months I would like to remove random key:value pair from first dictionary.

What I do not understand is why I have to type commented line of code to correct working function? Why condition can be higher than dictionary.Count and when is evaluated.

Thank you for answers.

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

List class:

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

Month class:

Public index As Integer
Public name As String

The boundaries for `For` *in VBA* (not necessarily other languages) are evaluated once before the first iteration and the results are cached and not recalculated. There is however [a problem in this process](https://stackoverflow.com/q/42354228/11683) that manifests in certain conditions which most people do not get themselves into. — GSerg, Jan 04 '21 at 21:18
FYI it might be simpler to fill `months_dict` first, and then use its `Exists` method while filling the other dictionary, so you can skip adding months there. — Tim Williams, Jan 04 '21 at 22:28
Its also better to prevent information getting into a data structure rather than removing it afterwards. So set up a scripting dictionary with the English month names as keys. Then you can check if the value in Col A is a month name and ignore any actions if it is not. — freeflow, Jan 04 '21 at 23:21
Thank you all for answers. I know that the code is not the best practice but I was curious about boundaries. @GSerg, thank you for your link. — tompla, Jan 05 '21 at 07:16

score 1 · Accepted Answer · answered Jan 04 '21 at 21:59

When removing items during a loop it's a good idea to work backwards so removed items don't need to be corrected for. The resulting code is much simpler to reason about.

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

When is for condition in VBA evaluated?

1 Answers1