2

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.

Teamothy
  • 2,000
  • 3
  • 16
  • 26
PhD
  • 11,202
  • 14
  • 64
  • 112
  • I think your "cycle detection" might choke on certain non-cycles. Say R8 and R9 both have R17 as a prerequisite... – jtolle Jul 19 '11 at 17:12
  • @jtolle: So for R8 the prerequisite would be R17 and R17 has no prerequisite so it proceeds. Same for R9. I'm working on ONE Requirement at a time - so for each requirement I'm just trying to get it's dependency graph tracing backwards, so to speak. I don't think it should choke. I managed to "prove it correct" in my head :P – PhD Jul 19 '11 at 17:15
  • I guess I don't understand what it is you're trying to do then. It seems like - issues with For vs. While loop aside - you'd process R4, adding R8, R9, and R10 to your list. Then you'd process R8, adding R17. And then you'd process R9, but you'd see that R17 was already there and stop. But that's not a cycle. Normally you'd do this sort of thing recursively and not try to squeeze it all into some nested loops. – jtolle Jul 19 '11 at 17:48
  • @jtolle: Ahhh! I see :) Makes sense...the only reason why it's a nested loop is I don't know how to write recursive functions with Excel VBA...I'm coding this up since no other expertise is available and I'm a regular programmer grappling with nuances of VBA :) – PhD Jul 19 '11 at 18:07
  • 2
    Here are some Stackoverflow links that you might find helpful. Recursion in VBA: http://stackoverflow.com/questions/4170794/adaptive-a-vba-excel-function-to-be-recursive ; Your cycle-detection problem: http://stackoverflow.com/questions/261573/best-algorithm-for-detecting-cycles-in-a-directed-graph – jtolle Jul 19 '11 at 18:22

1 Answers1

5

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
Chris Flynn
  • 953
  • 6
  • 11
  • I thought of that...will give it a try. But am still confused about why it "skips" the data? Wouldn't while do it too?? Don't know though – PhD Jul 19 '11 at 16:05
  • 2
    The user Fink has some insight as to why [here](http://stackoverflow.com/questions/3112751/why-doesnt-this-for-loop-process-the-full-data-set/3114247#3114247) – Chris Flynn Jul 19 '11 at 16:16
  • Exactly what I was looking for :) I knew excel had to do something smartly and make me feel dumb :D The link totally clarified that :) – PhD Jul 19 '11 at 16:43
  • Works like a charm!! Thanks for Fink's link - that just clarified everything! – PhD Jul 21 '11 at 05:13