gpt4 book ai didi

excel - 如何迭代动态增加的字典?似乎excel VBA预编译了For循环的 "limit"

转载 作者:行者123 更新时间:2023-12-02 02:29:03 25 4
gpt4 key购买 nike

我在 VBA 代码中遇到了 Scripting.Dictionary 对象的奇怪问题。我希望继续迭代字典的总长度。我可以在循环内向字典中添加更多项目,因此字典的大小可以动态变化。看起来它只迭代字典中原来的元素,而不是新元素!!

代码如下:

'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

从概念上讲,我试图获取依赖关系的整个“图表”,并检测循环(如果确实如此)。我迭代单元格以找出该图并查看是否存在循环。我需要能够不断迭代字典中的所有项目,以获取尽可能多的现有项目。但似乎 excel 以某种方式“预编译”了 for 循环,因此仅采用原始上限/限制,但不采用新上限/限制!我尝试过破解,这就是我所拥有的......但没有成功。

有什么想法吗?

附件是带有虚拟数据的 Excel 工作表示例...

enter image description here

对于 R4,preReqGraph 应包含从 R8 到 R17 的所有内容。但我得到的只是一个级别,即只有 R8 到 R12 和 R14...我被难住了。我什至尝试使用 preReqGraph.items 的 LBound 和 UBound 但无济于事。

最佳答案

最简单的方法是将 for 循环更改为 while 循环,然后手动增加索引变量。

Dim preReqCount as Integer
preReqCount = 0

While preReqCount <= numberOfPreReqsToIterate
'Your Loop Logic

preReqCount = preReqCount + 1
Wend

关于excel - 如何迭代动态增加的字典?似乎excel VBA预编译了For循环的 "limit",我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/6750231/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com