CODEWITHSUNDEEP
excelvba
PhD
PhD

Reputation: 11334

How to iterate over dynamically increasing dictionary? Seems excel VBA precompiles "limit" of For-loop

I'm facing this weird issue with Scripting.Dictionary object in my VBA code. I wish to continue to iterate over the dictionary for its total length. I may add more items to the dictionary within the loop, so the size of the dictionary changes dynamically, so to speak. It seems that it only iterates over for as many elements originally in the dictionary and NOT the new ones!!

Here's the code:

'Recursively continue to get all dependencies

    numberOfPreReqsToIterate = preReqGraph.Count - 1
    For preReqCount = 0 To numberOfPreReqsToIterate
        listOfPreReqs = preReqGraph.items
        rowNumberOfDependency = Application.WorksheetFunction.Match(listOfPreReqs(preReqCount), Range("requirementNumber"), 0)
        listOfPreReqsForCurrentPreReq = Application.WorksheetFunction.Index(Range("preReqList"), rowNumberOfDependency)

        If listOfPreReqsForCurrentPreReq <> "" Then
            preReqs = Split(listOfPreReqsForCurrentPreReq, ",")

            'Add each prereq to dictionary
            For i = 0 To UBound(preReqs)

                'If prereq already exists implies cycle in graph
                If preReqGraph.Exists(Trim(preReqs(i))) Then
                    MsgBox ("YOU HAVE A CYCLE IN PREREQUISTE SPECIFICATION!")
                    End
                End If

                'If not then add it to the preReqGraph. The value for the key is the key itself
                preReqGraph.Add Trim(preReqs(i)), Trim(preReqs(i))
                numberOfPreReqsToIterate = numberOfPreReqsToIterate + 1

            Next i
        End If
    Next preReqCount

Conceptually I'm trying to get the entire 'graph' of dependencies and also detect a cycle if at all that is the case. I iterate over the cells to find out this graph and see if cycles exist. I need to be able to keep iterating over all items in the dictionary for as many items that exist. But it seems excel somehow "precompiles" the for loop so only the original upper bound/limit is taken but not for the new ones! I've tried hacking around and this is what I have...but to no avail.

Any ideas?

Attached is a sample of the excel sheet with dummy data…

enter image description here

For R4 the preReqGraph should contain everything from R8 through R17. But What I get is ONLY one level i.e., only R8 through R12 and R14...I'm stumped. I've even tried using the LBound and UBound of preReqGraph.items but to no avail.

Upvotes: 2

Views: 2089

Answers (1)

Chris Flynn
Chris Flynn

Reputation: 953

The easiest thing to do would be to change your for loop to a while loop and then manually increment your index variable.

Dim preReqCount as Integer
preReqCount = 0

While preReqCount <= numberOfPreReqsToIterate
    'Your Loop Logic

    preReqCount = preReqCount + 1
Wend

Upvotes: 5

Related Questions