VBA 中的条件循环使用 If, Then, Else, Else if 语句不循环到数组中的下一个 i
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15046778/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Conditional Loop in VBA using If, Then, Else, Else if statement not looping to next i in array
提问by user1977802
I am trying to work on a conditional loop. Everything seems to be working, however it is not advancing to the next i and next j and continuing the loop. It seems to be ending after one loop of the loop. I have been working on this for quite sometime and am very very new to VBA.
我正在尝试处理条件循环。一切似乎都在工作，但是它没有前进到下一个 i 和下一个 j 并继续循环。它似乎在循环的一个循环之后结束。我已经为此工作了很长一段时间，并且对 VBA 非常陌生。
Sub Read()
NumMonth = 12
Worksheets("Sheet1").Activate
ReDim Month(1 To NumMonth)
ReDim WCinit(1 To NumMonth)
ReDim WC(1 To NumMonth + 1)
ReDim Precip(1 To NumMonth)
ReDim RefET(1 To NumMonth)
ReDim Percolation(1 To NumMonth + 1)
ReDim Runoff(1 To NumMonth + 1)
For i = 1 To NumMonth
Month(i) = Cells(4 + i, 1).Value
WCinit(i) = Cells(4 + i, 10).Value
Precip(i) = Cells(4 + i, 2).Value
RefET(i) = Cells(4 + i, 3).Value
Next i
For j = 1 To NumMonth + 1
WC(j) = Cells(3 + i, 11).Value
Next j
Application.ScreenUpdating = True
End Sub
Sub Test()
Dim fc As Double
fc = 0.3
NumMonth = 12
i = 1
j = 2
Dim pwp As Double
pwp=0.1
Dim dz As Double
dz = 0.5 'm
Do
If WC(j - 1) + WCinit(i) > pwp And fc - (WC(j - 1) + WCinit(i)) + RefET(i) < Precip(i) Then
Runoff(i) = (Precip(i) - (fc - (WC(j - 1) + WCinit(i)) + RefET(i))) * 0.5
Percolation(i) = (Precip(i) - (fc - (WC(j - 1) + WCinit(i)) + RefET(i))) * 0.5
WC(j) = fc
ElseIf WC(j - 1) + WCinit(i) > pwp And fc - (WC(j - 1) + WCinit(i)) + RefET(i) < Precip(i) Then
Runoff(i) = 0
Percolation(i) = 0
WC(j) = WC(j - 1) + WCinit(i) + Precip(i) - RefET(i)
Else
WC(j) = pwp
End If
j = j + 1
i = i + 1
Loop While j<13
回答by chuff
I have done some work on the code that you provided (as presented below with annotation), which mainly needed attentive debugging of the logic of the program. You will still get a compiler error because two of the arrays were not assigned in your code, and I don't have enough information to do that properly.
我对您提供的代码做了一些工作（如下所示，带有注释），主要需要对程序逻辑进行细心调试。您仍然会收到编译器错误，因为您的代码中未分配两个数组，而我没有足够的信息来正确执行此操作。
The best resource available in VBA for taming recalcitrant code is the built-in debugger. With it, it is possible to step through the code line-by-line looking at how each variable is changing at each step.
VBA 中可用于驯服顽固代码的最佳资源是内置调试器。有了它，就可以逐行查看每个变量在每一步的变化情况。
If you are not familiar with the debugger, a number of good explanations are available online, such as Chip Pearson's explanation of the basics.
如果您不熟悉调试器，可以在网上找到许多很好的解释，例如 Chip Pearson对基础知识的解释。
One change that could make the loops and indexes easier to deal with is putting the "13th month" values into variables that are separate from the arrays, which would then be straightforward 12-month ensembles of the data.
可以使循环和索引更容易处理的一项更改是将“第 13 个月”值放入与数组分开的变量中，然后这将是 12 个月数据的简单集合。
That would be a way to reduce the confusing bookkeeping that is otherwise required ("Should the index at this point in the program be j, or j - 1, or j + 1?"). I don't know if that approach really fits with your data and calculations.
这将是一种减少否则需要的令人困惑的簿记的方法（“程序中此时的索引应该是 j，还是 j - 1，还是 j + 1？”）。我不知道这种方法是否真的适合您的数据和计算。
Option Explicit ' Require declaration of all variables
Private Const NumMonth As Long = 12 ' Make these variable available to all subroutines
Private month As Variant ' and functions in the module. Needed because they
Private WCinit As Variant ' would otherwise be out of scope in the Test()
Private WC As Variant ' subroutine.
Private Precip As Variant ' The arrays need to be declared as type Variant,
Private RefET As Variant ' so we can make the assignment
Private Percolation As Variant
Private Runoff As Variant
Sub Read()
Dim firstMoDataRow As Long
Dim lastMoDataRow As Long
firstMoDataRow = 5
lastMoDataRow = 16
' if you want to avoid using the transpose function in the following
' array assignments, you will need to reference the arrays as
' two-dimensional, e.g., WC(i,1) or WC(3,1), etc., with the
' second index always 1 (that's what many often do).
month = WorksheetFunction.Transpose(Range(Cells(firstMoDataRow, 1), Cells(lastMoDataRow, 1)).Value) ' Direct assignment of values
WCinit = WorksheetFunction.Transpose(Range(Cells(firstMoDataRow, 10), Cells(lastMoDataRow, 10)).Value) ' in the worksheet ranges
WC = WorksheetFunction.Transpose(Range(Cells(firstMoDataRow - 1, 11), Cells(lastMoDataRow, 11)).Value) ' to the arrays.
Precip = WorksheetFunction.Transpose(Range(Cells(firstMoDataRow, 2), Cells(lastMoDataRow, 2)).Value)
RefET = WorksheetFunction.Transpose(Range(Cells(firstMoDataRow, 3), Cells(lastMoDataRow, 3)).Value)
' ?! Percolation and runoff are not assigned in your code, so I don't know
' the correct row and column references in the worksheet.
End Sub
Sub Test()
Dim fc As Double
Dim pwp As Double
Dim dz As Double
Dim i as Long, j as Long
fc = 0.3
pwp = 0.1
dz = 0.5 'm
i = 1
j = 2
Do While i <= NumMonth And j <= NumMonth + 1 ' I am assuming here that you will sort out
' the correct initializing and incrementing
' of the loop indexes, which I have not
' puzzled out
If WC(j - 1) + WCinit(i) > pwp And fc - (WC(j - 1) + WCinit(i)) + RefET(i) < Precip(i) Then
Runoff(i) = (Precip(i) - (fc - (WC(j - 1) + WCinit(i)) + RefET(i))) * 0.5
Percolation(i) = (Precip(i) - (fc - (WC(j - 1) + WCinit(i)) + RefET(i))) * 0.5
WC(j) = fc
ElseIf WC(j - 1) + WCinit(i) > pwp And fc - (WC(j - 1) + WCinit(i)) + RefET(i) < Precip(i) Then
' You are getting a "Subscript out of range" error here because Runoff() and
' Percolation<> did not get assigned in the Read sub
Runoff(i) = 0
Percolation(i) = 0
WC(j) = WC(j - 1) + WCinit(i) + Precip(i) - RefET(i)
Else
WC(j) = pwp
End If
j = j + 1
i = i + 1
Loop
End Sub
