Geospatial Activities

4.14 Other flow control structures

We have already use the “For…Next” loop to control the flow of execution of our code.  There are a number of other “loop” statements that can be used when we do not have a simple counter.  These statements will loop until the result of a Boolean statement is either true or false.  These are referred to as “Do/While” and “Do/Until” statements and they are used when we want to loop until a certain condition is try.  Let’s say we have data from a logistic growth model and we want to work on the data in the linear portion of the curve.  The logistic function is given by:

We can write the following program to create data for a logistic curve:

Sub LogisticCurve()
'
' Function to write out a logistic curve
'
    Dim P As Double ' Current population value
    Dim K As Double ' Carrying capacity (1 for the unit curve)
    Dim P0 As Double ' Starting population, must be non-zero
    Dim r As Double ' Growth rate
    Dim t As Double ' Time counter
    Dim Denom As Double ' Denominator used for checking for divide by zero
    Dim i As Integer ' Loop counter
   
    K = 1
    r = 1
    P0 = 0.01
    t = 0
   
    For i = 1 To 100
        Denom = K + P0 * (Exp(r * t) - 1)
       
        If (Denom = 0) Then
            P = 0
        Else
            P = (K * P0 * Exp(r * t)) / Denom
        End If
       
        Cells(i, 1) = P
       
        t = t + 0.1
    Next i
 
End Sub

Copy this code into Excel and plot the results.  You should see a nice logistic curve with 100 values.

To work on the linear portion of the curve lets say we need to wait until the data reaches 0.3 and then operate on the data until it reaches 0.7.  The code below uses the Do/While statement to find the data after 0.3 and then another Do/While to operate on the data until the values are over 0.7.

Sub LinearPorition()
    Dim P As Double
    Dim Row As Integer
   
    Row = 1 ' start at the first row
    P = Cells(Row, 1) ' get the first value
  
    Do While (P < 0.3) ' wait until P is greater than or equal to 0.3
        Row = Row + 1
        P = Cells(Row, 1)
    Loop
  
    Do While (P <= 0.7) ' operate on the data until P is greater than 0.7
        Row = Row + 1
        P = Cells(Row, 1)
    Loop

End Sub

Here we have used the most common Do/While function where we do not enter the loop unless the Boolean condition is true.  There are times when you will want to enter the loop at least once and then check the Boolean condition to see if you should stay in the loop.  For this you can use the Do/While with the check at the bottom of the loop:

Do
      ..
Loop While (condition)

There is also a pair of Do/Until statements which work exactly like do the Do/While statements except they wait for the Boolean statement to become true and then exit.

Do Until (condition)
      …
Loop

Do
      …
Loop Until (condition)