In a computer program, control flow refers to the order in which the statements are executed. In the programs we have seen until now there has been a simple flow of control – the statements have been executed in the order they have appeared, i.e. in sequence. Control structures are special statements that allow us to change the order of statements based on some condition. There are two types of control structure – control structures for selection, and control structures for repetition. Selection control structures allow us to specify whether some statements in the code will be executed or not, and repetition control structures allow us to decide how many times certain statements will be executed.
In this chapter we will take a closer look at both selection and repetition. Before we do that we will take a brief look at flowcharts, which are a handy tool for illustrating how these control structures work.
17.1 Flowcharts
A flowchart is a type of diagram that represents a process or an algorithm. It consists of boxes of various kinds connected with arrows. The directed lines that connect the boxes indicate the possible paths that can be taken in executing the algorithm/process. This is called the flow of control. It is a tool helping the user to design and document a process, and a means of communication to other people.
The most common boxes are shown in figure 17.1. Diamond-shaped boxes represent decisions. Inside the box is written a condition than can be evaluated as true or false. There are usually two branches from the box where one represents a true condition and the other a false condition. The branches are often labelled True and False, or Yes and No. Rectangular boxes represent processing steps, for example assignment statements in a program. Input/output boxes are shown as parallelograms. They are used to illustrate input or output from the program. In addition we have start and end boxes. They are drawn as rounded rectangles or circles with the words “Start” and “End” or similar phrases.
17.2 Control structures for selection
If…End If statement
Let’s start off with a practical example where we need to make a decision. Income tax is calculated based on the following rules:
• for income ≤ £35,000 the tax rate is 20 per cent
• for income > £35,000 the tax rate is 40 per cent.
Figure 17.1 A flowchart for adding numbers from 1 to 100.
If we rephrase the problem we can say that:
The tax rate is 20 per cent, but if the income is bigger than £35,000 it is 40 per cent.
To solve the problem we need an If…End If statement. This statement has the following syntax:
If condition Then
' statements that will be executed only if condition is true
End If
Condition is an expression then can be evaluated as true or false. If condition is true then, and only then, are the statements between If and End If executed. See figure 17.2.
We are now able to solve our problem. We need two variables, one to store the income that we fetch from cell A5 in the worksheet, and another one to store the calculated tax. The tax is written to cell A6. We also choose to define some constants for the tax rates and threshold.
Figure 17.2 An If…End If statement.
Sub CalculateTax()
Const RATE1 As Single = 0.2
Const RATE2 As Single = 0.4
Const THRESHOLD1 As Long = 35000
Dim income As Single
Dim tax As Single
' Read the income from cell A5
income = Worksheets(“Sheet1”).Range(“A5”).Value
' Calculate tax
tax = income * RATE1
' If the income is bigger than THRESHOLD1 we need
' to recalculate
If income > THRESHOLD1 Then
tax = income * RATE2
End If
' Store the result in cell A6
Worksheets(“Sheet1”).Range(“A6”).Value = tax
End Sub
There is a variation of the If…End If sentence which is written in one line. This is normally used if there is only one statement between If and End If, as is the case in the example above. We can therefore write
If income > THRESHOLD1 Then tax = income * RATE2
The end of the If…End If statement is marked by the end of the line, hence no End If is necessary. It is possible, but not recommended to place more than one statement after the reserved word Then. Each pair of statements must be separated with a colon.
If condition Then statement1: statement2: statement3
If…Else…End If statement
Sometimes we are facing a problem where we have to do one thing if a certain condition is true, and another thing if not. This situation can be solved with an If…Else…End If sentence. It has the syntax:
If condition Then
' statements to execute if the condition is true
Else
' statements to execute if the condition is not true
End If
Figure 17.3 An If…Else…End If statement.
It’s important to know that only one block of statements is executed. In the example above we actually have such a situation. The example can therefore be slightly better implemented with the following code:
Sub CalculateTax2()
Const RATE1 As Single = 0.2
Const THRESHOLD1 As Long = 35000
Dim income As Single
Dim tax As Single
' Read the income from cell A5
income = Worksheets(“Sheet1”).Range(“A5”).Value
' Calculate tax using an If…Else…End If statement
'If income > THRESHOLD1 Then
tax = income * RATE2 ' Only executed if income > THRESHOLD1
Else
tax = income * RATE1 ' Only executed if income <= THRESHOLD1
End If
' Store the result in cell A6
Worksheets(“Sheet1”).Range(“A6”).Value = tax
End Sub
The single-line form of the If…End If can also contain an Else clause:
If condition Then statement1 Else statement2
The IIf function
The IIf function is a viable alternative to the If…Else…End If sentence if we have a situation where we want to execute one sentence if the condition is true and another sentence if it is not. Say, for example, that we want to write a program to find out if a person is eligible to vote in a UK general election. We could solve the problem with an If…Else…End If statement, but we could also use the IIf function:
Sub EligibleToVote()
Dim age As Integer
Dim eligible As Boolean
' Read the age from cell A1
age = Worksheets(“Sheet1”).Range(“A1”).Value
' Check if eligible to vote
eligible = IIf(age >= 18, True, False)
' We could also have used an If…Else…End If sentence
'If age >= 18 Then
' eligible = True
' Else
' eligible = False
' End If
Worksheets(“Sheet1”).Range(“B1”).Value = IIf(eligible, “Can vote”, “Cannot vote”)
End Sub
The syntax for the IIf function is:
IIf(expr, truepart, falsepart)
Table 17.1 IIf function parameters
expr |
Expression to be evaluated. Required. |
truepart |
Value or expression returned if |
falsepart |
Value or expression returned if |
It operates the same way as the IF function in Excel.
For problems where we have to choose between more than two alternatives the If…ElseIf…Else…End If sentence can be used. It has the syntax:
If condition1 Then
' statements to execute if condition1 is true (1)
ElseIf condition2 Then
' statements to execute if condition2 is true (2)
…
ElseIf conditionN Then
' statements to execute if conditionN is true (n)
Else
' statements to execute if none of the above conditions is true
End If
When executing this sentence condition1 is tested. If it is True the statements following Then are executed and nothing else is done. If condition1 is False then condition2 is tested. If it is True then the statements following the second Then are executed. This continues until a true condition is met. If none is found then the statement after the last Else is executed. Both the ElseIf and Else clauses are optional. There can be only one Else and it has to be the last clause in the sentence. There can be as many ElseIf as you want but none can appear after an Else clause.
To illustrate the use of this sentence we can extend our tax calculation example with another rule:
• for income ≤ £35,000 the tax rate is 20 per cent
• for income > £35,000 and income ≤ £150,000 the tax rate is 40 per cent
• for income > £150,000 the tax rate is 50 per cent.
Sub CalculateTax3()
Const RATE1 As Single = 0.2
Const RATE2 As Single = 0.4
Const RATE3 As Single = 0.5
Const THRESHOLD1 As Long = 35000
Const THRESHOLD2 As Long = 150000
Dim income As Single
Dim tax As Single
' Read the income from cell A5
income = Worksheets(“Sheet1”).Range(“A5”).Value
' Calculate tax using an If…ElseIf..Else…End If statement
If income <= THRESHOLD1 Then
tax = income * RATE1
ElseIf income > THRESHOLD3 Then
tax = income * RATE3
Else
tax = income * RATE2
End If
' Store the result in cell A6
Worksheets(“Sheet1”).Range(“A6”).Value = tax
End Sub
Logical expressions
The condition in an If…Then sentence is an example of a logical expression.It is an expression that gives the value True or False when it is evaluated. Logical expressions are made with the use of VBA comparison operators. The comparison operators are listed in table 17.2.
Table 17.2 Comparison operators
All the comparison operators have equal precedence.
To create logical expressions with more than one condition we use the VBA logical operators given in table 17.3.
Table 17.3 Logical operators
Operator |
Action |
Not |
Logical Operator Not |
And |
Logical Operator And |
Or |
Logical Operator Or |
Not has the highest precedence, followed by And and Or. They all have lower precedence than the comparison operators.
Logical operators compare two or more Boolean expressions and return a Boolean result:
expr1 And expr2 And expr3
The compound expression is True only if expr1, expr2, and expr3 are all True
expr1 Or expr2 Or expr3
The compound expression is True if at least one of the expressions expr1, expr2, and expr3 is True
Not expr1
The compound expression is True if expr1 is False and vice versa.
Examples
' True if 5 < age <= 10
age > 5 And age <= 10
' True if temp is greater than 30 or pressure is greater than 765 temp > 30.0 Or pressure > 765.0
' True if the boolean variable raining is False and temp is _ ' greater than 20 Not raining And temp > 20
' Here are a few pitfalls…
' Always True (probably not what you meant) age > 5 Or age <= 10
' Never True
' age cannot be less than 0 and greater than 5 at the same time age < 0 And age > 5
To illustrate the use of the conditional and logical operators we’ll write a small application that will help a manager decide whether a person should be given a job or not.
Figure 17.4 Example worksheet.
The user types in data in cells B1:B3, and then runs the macro given below.
Nested If…End If statements
A nested If statement is an If statement that is used inside another If statement, i.e. one or more of the statements between If…Then and End If may be other If…End If statements. Here is an example on how to calculate the price of spaying or neutering a pet:
If sex = “M” Then
price = 100
Else
price = 200
End If
ElseIf pet = “cat” Then
If sex = “M” Then
price = 40
Else
price = 60
End If
End If
If we analyse this a little more closely we can write the outer If statement as:
If pet = “dog” Then
statement1;
ElseIf pet = “cat” Then
statement2;
End If
Then we replace statement1 and statement2 with the inner If statements and get the result shown above. Remember to close each If statement with an End If clause.
When we’re using nested If…End If statements it is very important to use indentation. This makes it very easy to see where an If…End If statement begins and ends. To show you how important this is, here is the same example with no indentation.
If pet = “dog” Then
If sex = “M” Then
price = 100
Else
price = 200
End If
ElseIf pet = “cat” Then
If sex = “M” Then
price = 40
Else
price = 60
End If
End If
Clearly, this is much harder to read and understand.
Select Case
The Select Case statement is an alternative to If…ElseIf…End If statements when you have to choose one of several alternatives. There is nothing you can do with a Select…Case sentence that cannot be done with a lot of If…End If statements, but in many cases a Select Case gives a more readable and neater solution. The general syntax of the Select Case statement is:
Select Case testexpression
Case expressionlist1
statement
statement
statement
…
Case expressionlist2
statement
statement
…
…
Case expressionlistN
statement
statement
statement
…
[Case Else
statement
statement
statement
…]
End Select
The testexpression is a numeric expression (for example a variable) or a string expression. The value of the testexpression is compared to the values in the first expressionlist (expressionlist1). If it is found the statements following Case expressionlist1 are executed. If it is not found the value is compared to the values in expressionlist2. If it is found there then the statements after Case expressionlist2 are executed. If it is not found the search continues with expressionlist3 and so on until a match is found. After a match is found and the following statements are executed, control passes to the statement following End Select.
Sometimes there is a Case Else clause in the Select…Case statement. The statements following this clause are executed only if the value of the testexpression is not found in one of the expression lists. The Case Else clause is optional. See figure 17.5 for a visualization flowchart.
What exactly do we mean by a testexpression and an expression list? A few examples will hopefully shed some light on the matter. Suppose we want to check the content of a worksheet cell and then do different things depending on the value.
Select Case Cells(1, 1).Value
Case 1
Cells(1, 2).Value = 20
Case 2
Cells(1, 2).Value = 40
Case 3
Cells(1, 2).Value = 50
End Select
If the value in cell A1 is 1 then put 20 in cell B1, if it is 2 put 40 in B2, if it is 3 put 50 in B2. See figure 17.5.
In this example the expression list was a constant. You can also use multiple expressions or ranges in every Case clause. The next examples will show a few possibilities. It is also a good habit to use a Case Else clause in the Select…Case statement to guard against unforeseen testexpression values.
Figure 17.5 The Select Case Statement.
To VBA an uppercase letter in a string is different from a lowercase letter. We therefore need to specify two ranges in the expression list (each separated with a comma). There are two ways around this:
• add an Option Compare Text directive at the top of the module. This will cause VBA to use a case-insensitive text sort order
• use the UCase or LCase functions to convert the text to uppercase or lowercase respectively.
Select Case LCase(Range(“A1”).Value)
Case “a” To “h” ' Value must begin with a letter A to H
Range(“A2”).Value = 1
Case “i” To “p” ' Value must begin with a letter I to P
Range(“A2”).Value = 2
Case Else ' All other values
Range(“A2”).Value = 0
End Select
17.3 Control structures for repetition
The Do While…Loop sentence
Control structures for repetition, or loops as they are commonly called, are used if we want to repeat the execution of a group of statements more than once. We’ll begin our discussion of loops with the Do While…Loop statement.
We use this sentence when statements need to be executed repeatedly until a condition is satisfied, i.e. is True. The general syntax of the Do While…Loop sentence is:
Do While condition
' place statements that you want to repeat here
' (also called loop body)
Loop
The condition is tested when execution arrives at the Do While…Loop sentence. If it is True the statements written between Do…While and Loop (the loop body) are executed. This is repeated until condition is evaluated to False. In this case the loop body is not executed and the flow of control continues with the next statement after Loop. The whole thing is visualized in figure 17.6.
Figure 17.6 The Do While…Loop Statement.
Let’s use a loop to sum all integers between 1 and 100 and store the result in the variable sum. Here’s how it’s done.
Dim number As Integer
Dim sum As Integer
number = 1
' Repeat as long as number is less than or equal to 100
Do While number <= 100
' Add the number to the sum
sum = sum + number
' Increment number by 1
number = number + 1
Loop
Take great care to get the counting right. A very common error is to make the loop repeat one too many times or one too few times or to create an infinite loop (a loop that never ends because the condition never becomes False). If we forget the line number = number + 1 in the loop body in the example above we have just made an infinite loop (number will always remain 1).*
*To stop an endless loop, press ESC or CTRL+BREAK.
In mathematics the Fibonacci numbers or Fibonacci sequence are a series of positive integer numbers. The two first numbers in the sequence are 0 and 1, and each subsequent number is equal to the sum of the previous two. The sequence begins with:
0 1 1 2 3 5 8 13 21 34
The nth number in the sequence fn is defined as:
We want to make a program that calculates fn for a chosen value of n. To formulate an algorithm we will use pseudocode, i.e. an outline of the program written in a form that can easily be converted into real programming statements.
Read n
If n = 0
Set fn = 0
Else If n = 1
Set fn = 1
Else
Set fn-2 = 0
Set fn-1 = 1
Set count = 2
Do While count <= n
Set fn = fn-1 + fn-2
Set fn-2 = fn-1
Set fn-1 = fn
Increment count by 1
Loop
End If
Print fn
Let’s take a closer look at the loop body. For each new repetition we calculate fn as the sum of the previous two numbers. The trick is then to set fn–1 = fn and fn–2 = fn–1 because on the next repetition fn–1 will be what is now fn, and fn–2 will be fn–1. It is very important that a new value is assigned to fn–2 before fn–1 is changed. Can you figure out why?* A variable (count) is used to count the number of repetitions. Remember to increment by 1 for each repetition.
*Because we do not want to assign the value of fn to fn–2 as we would do if a new value was assigned to fn–1 first.
This pseudocode should now be fairly easy to convert to a complete program.
Sub Fibonacci()
Dim fn As Long, fn1 As Long, fn2 As Long, i As Long, _ n As Long
Dim count As Integer
' Read value for n
n = InputBox(“Type in a value for n (>= 0)”, _ “Fibonacci number calculation”, 0)
If n = 0 Then
fn = 0
ElseIf n = 1 Then
fn = 1
ElseIf n > 1 Then
fn2 = 0
fn1 = 1
count = 2
Do While count <= n
fn = fn1 + fn2
fn2 = fn1
fn1 = fn
count = count + 1
Loop
End If
MsgBox “The” & n & ”. Fibonacci-number is ” & fn
End Sub
We have used the Long data type here for storing the nth Fibonacci number. The maximum value is then 2,147,483,647. The 47th Fibonacci number is equal to 2,971,215,073. Hence the largest value we can use for n is 46. If n > 46 we will get an error message (overflow) when we run the program. We could of course have prevented this situation from happening by checking that n is between 0 and 46 before any calculation is done.
Do Until…Loop is a variation of the Do While…Loop. In contrast to a Do While…Loop, the statements in a Do Until…Loop are repeated until the condition is True. The syntax is:
Do Until condition
' place statements to be repeated here
Loop
Here is the calculation of the sum of the integers from 1 to 100 done by the help of a Do Until…Loop.
Dim number As Integer
Dim sum As Integer
number = 1
' Repeat until number is greater than 100
Do Until number > 100
' Add the number to the sum
sum = sum + number
' Increment number by 1
number = number + 1
Loop
In this example we will use some of the string functions available in VBA to format the name of a person. For example, James Paul McCartney will be transformed to McCartney, James P.
We will be using a Do Until…Loop, and the logic can hopefully be understood by reading the comments.
' This subroutine reads a name from an Input box on the form
' forename [middle name(s)] surname
' The name is then formatted to surname, forename [middle initial.]
Sub FormatName()
Const SEP=““
Const SEP2 = “, “
Const SEP3 = “. “
Dim name As String ' Contains the name to be formatted
Dim rname As String ' Used to store the formatted name
Dim fnameend As Integer ' Points to the end of the forename
Dim snamestart As Integer ' Points to the start of the surname
Dim pos As Integer ' A counter used for finding middle names
' Read the name from the user
' A better solution would be to transfer the name as a parameter,
' but we have not learned how to do that yet.
name = InputBox(“What’s your name?”,, ““)
' Remove whitespace from the beginning and end of the name
name = Trim(name)
' Find the position where the first name ends, and the position
' where the lastname starts
' Actually, we’re finding the position to spaces before and after
' the names
fnameend = InStr(name, SEP)
snamestart = InStrRev(name, SEP)
If fnameend = 0 Then
' If fnameend is 0 there is only one name
MsgBox name
ElseIf fnameend = snamestart Then
' There are no middle names
MsgBox Right(name, Len(name) – snamestart) & SEP2 & _ Left(name, fnameend)
Else
' If we come here then snamestart must be greater than fnameend
' Format the name as surname, forename
rname = Right(name, Len(name) – snamestart) & SEP2 & _ Left(name, fnameend) & SEP
' Try to find a character between the first name and the surname
' that has a space in front of it. If this is the case, then there
' must be the start of a middle name. We will then print this
' character followed by a period and a space.
pos = fnameend + 1
Do Until pos = snamestart
If Mid(name, pos, 1) <> SEP And Mid(name, _ pos – 1, 1) = SEP Then
rname = rname & Mid(name, pos, 1) & SEP3
End If
pos = pos + 1
Loop
MsgBox rname
End If
End Sub
Do…Loop While and Do…Loop Until
If we use Do While…Loop the condition is always checked before the loop body is entered. By using the Do…Loop While sentence the test is carried out at the end of each repetition. This means that the statements in the loop body are always carried out at least once. The syntax is
Do
' place statements to be repeated here
Loop While condition
When the last of the statements in the loop body has executed, the condition is evaluated. If it is True the loop is repeated again. If it is False the loop ends.
Here is example 17.1 with a Do…Loop While sentence.
Dim number As Integer
Dim sum As Integer
number = 1
Do
' Add the number to the sum
sum = sum + number
' Increment number by 1
number = number + 1
Loop While number <= 100 ' Repeat as long as number is less ' than or equal to 100
Do…Loop Until is a variation of Do…Loop While. The only difference is that
Do…Loop Until keeps repeating until the condition is satisfied (True).
Do
' place statements to be repeated here
Loop Until condition
For…Next
The For…Next loop is an alternative to the Do While…Loop statement when the number of iterations, i.e. the number of repetitions, is known beforehand. This is because the For…Next loop has a counter variable bundled up in the header of the loop. The general syntax is:
For counter = start To end [ Step step ]
' place statements to be repeated here
Next counter
counter is the name of the variable that is used for counting the number of repetitions. Any name may be used as long as it is an accepted variable name in VBA. The first step is called initialization. This takes place before the loop begins. Here we set the starting value of the counter variable. Before the loop executes the value of the counter variable is checked against the end value. If it is less than or equal to the end value the body of the loop is executed. Next the counter variable is automatically incremented by 1 unless the loop has a Step clause with a step value. In this case the counter variable is incremented by the step value. After incrementing the counter it is again checked against the end value. Provided it is still less than or equal to the end value the body of the loop is again executed. This goes on until the counter has reached a value that is greater than the end value. The loop then ends. See figure 17.7 for an illustration.
Figure 17.7 The For…Next Statement.
The counter does not have to be incremented after each repetition; it can also be decremented. We achieve this by using a Step clause with a negative step value. The counter is decremented by the step value on each repetition. This goes on until the counter has reached a value that is less than the end value.
Here are some examples.
' Sum the numbers from 1 to 100
For number = 1 To 100
sum = sum + number
Next number
' Sum the even numbers from 1 to 100
For number = 2 To 100 Step 2
sum = sum + number
Next number
' Reverse a string by using a For…Next loop
Sub ReverseString()
Dim str As String
Dim rstr As String
Dim i As Integer
' Get the string from cell A1
str = Range(“A1”).Value
' Create a reverse string
For i = Len(str) To 1 Step –1
rstr = rstr + Mid(str, i, 1)
Next i
' Write the reversed string in cell A2
End Sub
Suppose you’re planning to buy a new home and decide to borrow money from the bank. There are two main types of mortgage, annuity loan and serial loan, and you decide to go with the latter. A serial loan is characterized by the repayment being the same throughout the entire repayment period. Since the outstanding amount decreases for each period, the interest amount is also decreasing.
Figure 17.8 Repayment plan for serial loan.
Assume you have created a worksheet as shown in figure 17.8. The user fills in the data in cells B2:B5. Then the macro shown below is run to display the amortization plan.
Sub RepaymentPlan()
Dim annual_interest_loan_rate As Single
Dim interest_loan_rate As Single
Dim payment_frequency As Integer
Dim number_of_repayments As Integer
Dim num_years As Integer
Dim amount_borrowed As Single
Dim i As Integer
Dim rest_loan As Single
Dim payment_to_interest As Single
Dim payment_to_principal As Single
Dim amount As Single
Worksheets(“Sheet1”).Activate
' Fetch data from the worksheet
amount_borrowed = Cells(2, 2).Value
num_years = Cells(3, 2).Value
payment_frequency = Cells(4, 2).Value
annual_interest_loan_rate = Cells(5, 2).Value
rest_loan = amount_borrowed
' Calculate the total number of payments the interest
' loan rate
number_of_repayments = num_years * payment_frequency interest_loan_rate = annual_interest_loan_rate / _ payment_frequency
amount = amount_borrowed / number_of_repayments
' Clear output from a previous run of this subroutine Range(“A9:E309”).Clear
' Generate repayment plan
' Using a loop that repeats number_of_repayments times
With Range(“A8”)
For i = 1 To number _of_repayments
payment_to_interest = rest_loan / _ 100 * interest_loan_rate
payment_to_principal = payment_to_interest
rest_loan = amount_borrowed – amount * i
' Print values
.Offset(i, 0).Value = i
.Offset(i, 1).Value = amount
.Offset(i, 2).Value = payment_to_interest
.Offset(i, 3).Value = amount + payment_to_principal
.Offset(i, 4).Value = rest_loan
Next i
' Format numbers
Range(.Offset(1, 0), .Offset(number_of_repayments, 4)) _ .Select
Selection.NumberFormat = “0”
End With
End Sub
For Each…Next
The For Each…Next loop is well suited if you want to iterate over (i.e. do something with) each object in a collection. You do not need to know how many objects there are in the collection. The syntax is:
For Each obj In Collection
' Do something with the object here
Next
obj is a variable that is assigned a new object from the collection each time the loop repeats.
' Do something with each cell in a Range
Dim area As Range, cell As Range
Set area = Range(“A1:A10”)
' Colour each cell in the range (A1:A10) yellow
For Each cell In area
cell.Interior.ColorIndex = 6' 6=yellow
Next
In this example the For Each…Next loop is used to iterate over every worksheet in a workbook.
' Replace “Sheet” in the name of the worksheet with “Year”
Dim sheet As Worksheet
For Each sheet In ActiveWorkbook.Worksheets
sheet.name = Replace(sheet.name, “Sheet”, “Year”)
Next
Terminating loops
In some cases you may want to end a loop before all repetitions are done. For this purpose VBA provides the Exit For and Exit Do statements.
Exit For
Exit For provides a way to exit a For…Next or For Each…Next loop. Exit For transfers control to the next statement following the Next statement.
Exit Do
Provides a way to exit a Do…Loop statement. It can only be used in Do While…Loop, Do Until…Loop, Do…Loop While and Do…Loop Until statements. Control is transferred to the next statement following Loop.
Nested loops
A nested loop is a loop within a loop – an inner loop within the body of an outer one. As a small example we will use two For…Next loops to print a multiplication table in the upper left cells of a worksheet.
Sub MultiplicationTable()
' Print a multiplication table
Dim row As Integer
Dim col As Integer
For row = 1 To 10
For col = 1 To 10
Cells(row, col) = row * col
Next col
Next row
End Sub
The inner loop does all of its repetitions each time the outer loop repeats once. In this example the inner loop was the same type as the outer loop, but that’s no requirement. For example, it’s perfectly OK to use a Do While…Loop within a For…Next loop.
Choosing the right loop
It is always possible to use a Do While…Loop where you use a For…Next loop but some loops are better suited in some cases. Here are a few rules of thumb.
• You know in advance how many times the statements have to be run. A For…Next is the best choice.
• You want to iterate over a collection. A For Each…Next loop is best suited.
• You want to make sure that the loop is executed at least once. A Do…Loop While or a Do…Loop Until is best.
• You want to make sure that the loop is not executed if the condition is not satisfied in the beginning. A Do While…Loop or Do Until…Loop is best.
• You’re not sure how many times the statements will be repeated. One of the Do…Loops will probably do.
Problems
17-1. Write a macro that reads a positive integer N from cell A1 and then calculates the sum of all integers in the interval 1 to N. The sum is written into cell B1.
17-2. Imagine a stock market where all the stocks have a 12 per cent standard deviation on the expected return. The correlation coefficient for the pairs of stocks is 0.2. Create a macro that reads these values from a worksheet and calculates the standard deviation for a portfolio consisting of a number of different stocks (which is also read from the worksheet).
17-3. A worksheet has three columns and ten rows with data. Column A contains stock tickers, column B contains the initial purchase price of the stocks, and column C contains the current price. Create a macro that writes “Sell” in column D if the loss is more than 8 per cent or if the profit is more than 25 per cent. Otherwise you should write “Hold” in the column.
17-4. For an annuity loan the regular payments are the same amount throughout the repayment period. As the loan is repaid, the repayment portion goes up and the interest portion goes down. Create a macro that writes a repayment plan for an annuity loan (see also example 17.5).
17-5. Create a macro that prints a table showing how much €5,000 invested under various interest rates will increase. See figure below.
