为什么这个for循环处理完整的数据集？ [英] Why doesn't this for loop process the full data set?
问题描述
背景
我有一张活动票证分配电子表格。电子表格的每一行都是一个名称和分配的票数。
I have a spreadsheet of ticket allocations for an event. On each row of the spreadsheet is a name and the number of tickets allocated.
我需要更改电子表格，使每个名称每次重复一次在单独的行上，如下所示：
I need to change the spreadsheet so that each name is duplicated once per ticket on separate rows, like this:
我有一个宏来做到这一点，但它表现出奇怪的行为
I have a macro to do this, however it exhibits strange behaviour
问题
宏不循环遍历整个数据集。通过代码显示，尽管有意增加 LastRow 的值，但For循环只能循环指定原始值的很多次。每次迭代结束时， LastRow 的新值似乎被忽略。
The macro doesn't loop through the entire data set. Stepping through the code shows that, despite deliberately increasing the value of LastRow, the For loop only loops for however many times the original value specified. The new value of LastRow at the end of each iteration appears to be disregarded.
这似乎特别奇怪，因为等效的Do While循环工作正常（参见下面的使用Do While循环的工作代码）
This seems particularly odd as the equivalent Do While loop works fine (See below for the working code using a Do While loop)
问题
为什么发生问题部分（上述）中描述的行为，为什么它与等效结构不一致？
Why does the behaviour described in the problem section (above) occur, and why is it inconsistent with equivalent structures?
对于循环宏
Sub InsertSurnames()
Dim LastRow As Long
Dim r As Long
Dim surname As String
Dim tickets As Integer
Dim surnameCol As Integer
Dim ticketCol As Integer
Dim targetCol As Integer
surnameCol = 1
ticketCol = 3
targetCol = 4
LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
For r = 1 To LastRow
surname = Cells(r, surnameCol).Value
tickets = Cells(r, ticketCol).Value
If (Not (Len(surname) = 0)) Then
Cells(r, targetCol).Value = surname
For x = 1 To tickets - 1
Cells(r + x, 1).EntireRow.Insert
Cells(r + x, targetCol).Value = surname
Next x
LastRow = LastRow + tickets - 1
End If
Next r
End Sub
执行循环宏
Sub InsertSurnames()
Dim LastRow As Long
Dim r As Long
Dim surname As String
Dim tickets As Integer
Dim surnameCol As Integer
Dim ticketCol As Integer
Dim targetCol As Integer
surnameCol = 1
ticketCol = 3
targetCol = 4
LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
r = 1
Do While r <= LastRow
surname = Cells(r, surnameCol).Value
tickets = Cells(r, ticketCol).Value
If (Not (Len(surname) = 0)) Then
Cells(r, targetCol).Value = surname
For x = 1 To tickets - 1
Cells(r + x, 1).EntireRow.Insert
Cells(r + x, targetCol).Value = surname
Next x
LastRow = LastRow + tickets - 1
End If
r = r + 1
Loop
End Sub
推荐答案
编译器以不同的方式插入For循环结构，并使用不同的程序集调用将临时变量放入CPU缓存中，因此在每次迭代后不需要回到RAM读取变量，它可以从cpu的缓存中抓取它。这是为了提高性能，这就是为什么'For'循环通常比'While'循环更快。 'for'循环的极限变量仍然存在于内存中，但它不会在每次迭代期间读取。所以如果你改变原来设置上限的变量，你的循环仍然会运行到你设置的原始界限。 while循环在每次迭代时检查其exit子句，并且不缓存是可变的。一般来说，For循环应该在您设置一定量的迭代时使用，而不是while循环，当您不确定需要多少次循环时，需要更多的动态控制。
The compiler interperets the 'For' Loop construct differently, and uses different assembly calls to place the temporary variable into CPU cache, so after each iteration it doesn't need to go back out to RAM to read the variable, it can just grab it from the cpu's cache. This is by design to increase performance, thats why 'For' loops are generally faster than 'While' loops. The limit variable for the 'for' loop still lives in memory, but its not reading it during each iteration. So if you change the variable used to originally set the upper bound, your loop will still run to the original bound you set it to. While loops check its exit clause at each iteration, and does not cache is variable. Generally 'For' loops should be used when you have a set amount of iterations, as opposed to the while loop when you are not sure how many times you will need to loop, and need more dynamic control.
这篇关于为什么这个for循环处理完整的数据集？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
