Learning Objectives:
- Conditional Statements
- Iterative Statements
Conditionals
There are 4 main types of conditionals that we will use in VBA:
- If,
- If-else,
- If-ElseIf-else, and
- Select Case.
Relational Operator
Conditional statement are executed based on logical statement (true/false). The most basic logical statement is by relational operators. VBA uses the same relational operator as those in Excel spreadsheet.
| Operator | Meaning |
|---|---|
> |
greater than |
< |
less than |
= |
equal to |
>= |
greater than or equal to |
<= |
less than or equal to |
If
How do conditionals work? They are usually put at the front of a condition that you want to be met. If that condition is met, then Excel will carry out the specified action or task. The syntax looks like this:
If (condition is True) Then
Do action/task
End if
Here is an example for if-statement using subroutine.
Sub SimpleIF()
Dim a as Integer
Dim b as Integer
a = 2
b = 1
If a > b Then
MsgBox( "I like this class!" )
End If
If a < b Then
MsgBox( "I hate this class!" )
End If
End Sub
Here is an example for if-statement using function.
For a list of scores: 85, 90, 72, 45…, we want to assign Pass if no less than 50, and Fail otherwise.
| A | B | C | |
|---|---|---|---|
| 1 | Student | Score | Pass? |
| 2 | Peter | 85 | =IF(B2>50,"PASS","FAIL") |
| 3 | John | 94 | |
| 4 | Mary | 72 | |
| 5 | Susuan | 45 |
We can write the following function.
Function Pass1(Mark As Integer)
If Mark >= 50 Then
Pass1 = "Pass"
End If
If Mark < 50 Then
Pass1 = "Fail"
End If
End Function
| A | B | C | |
|---|---|---|---|
| 1 | Student | Score | Pass? |
| 2 | Peter | 85 | =Pass1(B2) |
| 3 | John | 94 | |
| 4 | Mary | 72 | |
| 5 | Susuan | 45 |
If-else
We sometimes have a binary situation that we want to execute task A if condition is true and otherwise task B. We can use the following if-else statement.
If (condition is True) Then
Do action/task A
Else
Do action/task B
End if
In this example, if the 1st condition is met, task A will be carried out. Otherwise, we immediately carry out task B, because no other conditions are specified.
Here is an example of routine for if-statement.
Sub IFElse()
Dim a as Integer
Dim b as Integer
a = 2
b = 1
If a = b Then
MsgBox( "I like this module!" )
Else
MsgBox( "I hate this module!" )
End If
End Sub
Here, we are using 2 If conditions. Also note that the code only accounts for the case a > b, and the case a<b. If a=b, then you will not get any result. This is extremely important, because when coding people often tend to neglect one or two cases, and this causes problems in their code. Also note that every If chunk must be ended with the code End If, otherwise VBA will not know that you have ended the If code chunk.
Continue with our Pass1, we can use if-else statement.
Function Pass2(Mark As Integer)
If Mark >= 50 Then
Pass2 = "Pass"
Else
Pass2 = "Fail"
End If
End Function
| A | B | C | |
|---|---|---|---|
| 1 | Student | Score | Pass? |
| 2 | Peter | 85 | =Pass2(B2) |
| 3 | John | 94 | |
| 4 | Mary | 72 | |
| 5 | Susuan | 45 |
If-elseif-else
The next question would then be, what if we wanted to include another condition? We would then make use of the If-ElseIf-else conditional, as shown below.
If (condition1 is True) Then
Do action/task A
ElseIf (condition2 is True) Then
Do action/task B
Else
Do action/task C
End if
Note that If-ElseIf-Else conditionals are only evaluated once, in order. The topmost condition is checked, if that isn’t true, the next one is checked, and so on, until you find one that is true and perform that task. Whatever other conditions remaining below the true one are not gone through, because once that true condition’s task has been carried out, the checking stops. In other words, VBA will exit that If-ElseIf-Else chunk. For correctness, none of your conditions should have identical overlapping parts!
Here we use a If-Else condition, and now the a=b case is also accounted for, under the Else condition.
Sub IFElseIF()
Dim a as Integer
Dim b as Integer
a = 2
b = 1
If a = b Then
MsgBox( "I like this module!" )
ElseIf a < b Then
MsgBox( "I hate this module!" )
Else
MsgBox( "I am neutral towards this module!" )
End If
End Sub
Here, we have used an ElseIf condition, and this separates the previous a<b and a=b cases into 2 different cases now. Previously in confession1() they were lumped together under the Else condition.
Recall our grading function as follows:
| A | B | C | |
|---|---|---|---|
| 1 | Student | Score | Pass? |
| 2 | Peter | 85 | =IF(B2>80,"A",IF(B2>70,"B","C")) |
| 3 | John | 94 | |
| 4 | Mary | 72 | |
| 5 | Susuan | 45 |
Function Grade1(Mark As Integer)
If Mark >= 80 Then
Grade1 = "A"
Elseif Mark >= 70 Then
Grade1 = "B"
Else
Grade1 = "C"
End If
End Sub
| A | B | C | |
|---|---|---|---|
| 1 | Student | Score | Pass? |
| 2 | Peter | 85 | =Grade1(B2) |
| 3 | John | 94 | |
| 4 | Mary | 72 | |
| 5 | Susuan | 45 |
Select Case
How to handle if we have many different cases? We can use the Select function, to create cases that we want to choose between.
Sub SelectCase()
Dim a as Integer
a = 1
Select Case a
Case 1
MsgBox ("I like this module!")
Case 2
MsgBox ("I hate this module!")
Case 3
MsgBox ("I am neutral!")
End Select
End Sub
In this case, since you have set the value of a to 1, the output will be I like this module. If you were to change the value of a in the code, the corresponding case will then be selected. This is useful if you had a variable with multiple states or values that you might want to switch between.
Select case can also accommodate cases using relational operator. To do so, we have to use Is to represent the variable, and Else to represent all residual cases.
The following function returns the grade of a class based on the mark. A student will get an A if the mark is no less than 80, B if the mark is no less than 70 and C otherwise.
Function Grade2(Mark As Integer)
Select Case Mark
Case Is >= 80
Grade2 = "A"
Case Is >= 70
Grade2 = "B"
Case Else
Grade2 = "C"
End Select
End Function
| A | B | C | |
|---|---|---|---|
| 1 | Student | Score | Pass? |
| 2 | Peter | 85 | =Grade2(B2) |
| 3 | John | 94 | |
| 4 | Mary | 72 | |
| 5 | Susuan | 45 |
Logical Operators
Sometimes, we want to combine several logical statements. However, different from Excel spreadsheet, for and and or VBA uses logical operator instead of logical function.
| Operator | Meaning |
|---|---|
| logical1 AND logical2 | Returns TRUE if both logicals are TRUE |
| logical1 OR logical2 | Returns TRUE if any of the logicals are TRUE, and Returns FALSE only if all logicals are FALSE |
| NOT logical | Changes FALSE to TRUE, or TRUE to FALSE |
Recall our A+ grading example.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Student | Test | Exam | Grade |
| 2 | Peter | 30 | 55 | =IF(AND(B2+C2>80,C2>60),"A",IF(B2+C2>80,"A",IF(B2+C2>70,"B","C")) |
| 3 | John | 28 | 62 | |
| 4 | Mary | 22 | 40 | |
| 5 | Susuan | 45 | 30 |
Function AplusGrade(Test As Integer, Exam As Integer)
Dim Total As Integer
Total = Test + Exam
If Total >= 80 And Exam > 60 Then
AplusGrade = "A+"
Elseif Total >= 80 Then
AplusGrade = "A"
Elseif Total >= 70 Then
AplusGrade= "B"
Else
AplusGrade = "C"
End If
End Function
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Student | Test | Exam | Grade |
| 2 | Peter | 30 | 55 | =APlusGrade(B2,C2) |
| 3 | John | 28 | 62 | |
| 4 | Mary | 22 | 40 | |
| 5 | Susuan | 45 | 30 |
Example: SMA
Recall that how we generate SMA as below
| A | B | C | |
|---|---|---|---|
| 1 | n | 5 | |
| 2 | Date | Price | SMA(n) |
| 3 | 02-May-16 | 10 | =IF(COUNT($A$3:A3)>=$B$1,average(Offset(B3,0,0,-$B1$)), "") |
| 4 | 03-May-16 | 9 | |
| 5 | 04-May-16 | 8 |
Function SMA(Price As Range, n As Integer, Price0 As Range)
Dim day As Integer
day = WorksheetFunction.Count(Range(Price0,Price))
If day >= n Then
SMA = WorksheetFunction.Average(Range(Price.Offset(-n+1,0),Price))
Else
SMA = ""
End If
End Function
| A | B | C | |
|---|---|---|---|
| 1 | n | 5 | |
| 2 | Date | Price | SMA(n) |
| 3 | 02-May-16 | 10 | =SMA(B3,B$1,B$3) |
| 4 | 03-May-16 | 9 | |
| 5 | 04-May-16 | 8 |
Example: SMA Trading Rule
To generate a trading signal based on SMA, we can use the SMA function above.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Delta | 5 | ||||
| 2 | S | |||||
| 3 | L | |||||
| 4 | Date | Price | SMA(S) | SMA(L) | r | Trade |
| 5 | 02-May-16 | 10 | =SMA(B5,$B$2,$B$5) |
=SMA(B5,$B$3,$B$5) |
=C5/D5-1 |
=IF(E5>=$B1$,1,IF(E5<=-$B1$,-1,0)) |
| 6 | 03-May-16 | 9 | ||||
| 7 | 04-May-16 | 8 |
SMA(ST, LT, Delta, price, price0) where price is current price, price0 is initial price, ST is the length of short term SMA, LT is the length of long term SMA, and Delta is threshold parameter.
This function assumes the prices are sorted chronologically. Then, using the SMA function above and finally calculate the trading signal using the if condition.
Function SMATrade(ST As Integer, LT As Integer, Delta As Double, Price As Range, Price0 As Range)
Dim smas As Double
Dim smal As Double
Dim r As Double
Dim day As Integer
day = WorksheetFunction.Count(Range(Price0,Price))
If day >= LT Then
smaS = SMA(Price, ST, Price0)
smaL = SMA(Price, LT, Price0)
r = (smaS - smaL) / smaL
If r > Delta Then
SMATrade = 1
ElseIf r < -Delta Then
SMATrade = -1
Else
SMATrade= 0
End If
End If
End Function
End Function
| A | B | C | |
|---|---|---|---|
| 1 | Delta | 5 | |
| 2 | S | ||
| 3 | L | ||
| 4 | Date | Price | Trade |
| 5 | 02-May-16 | 10 | =SMATrade($B$2,$B$3,$B$1,B15,$B$5) |
| 6 | 03-May-16 | 9 | |
| 7 | 04-May-16 | 8 |
Iteration
Sometimes, when coding certain things we want to check the same conditions multiple times. One way would be to write multiple If-Else chunks, but that would be extremely inefficient and tedious. We can use loops, such that we only need to type the If-Else chunk once and the loop will repeat it multiple times. There are 2 types of loops, the while loop and the for loop. They work in a similar way, however you will use them under different circumstances, depending on what your code requires.
While Loop
We first look at an example of a while loop.
Sub WhileLoop()
Dim a As Integer
a = 0
Do While a < 3
MsgBox (a & ": I like this class! ")
a = a + 1
Loop
MsgBox ("It is important so I said this " & a & " times!")
End Sub
Here, a has the initial value of 0. By the time the first loop is finished, a now has the value 1, because of the line a = a + 1, and the message box has appeared once with the message I like this class!. VBA then goes back to the top of the while loop, and checks if a<3. Since a=1, this condition is satisfied, so the loop runs another iteration. Now a=2. Since the while condition is still satisfied, the loop runs a 3rd iteration. Now a=3. The condition a<3 is no longer true, so the loop does not continue running, and VBA exits the loop. We then expect to see a message box with the value a=3 inside.
For Loop
Another type of loop is the for loop. It works in a similar way to a while loop, but there will be occasions when it is more convenient to use a for loop instead of a while loop, and vice versa.
Sub ForLoop()
Dim a As Integer
For a = 1 to 5 Step 1
MsgBox (a & ": I like this class!")
Next a
MsgBox ("OK. I know. You have said this " & a-1 & " times!")
End Sub
Initially, a=1. Step 1 means that every time the loop completes one iteration, a will increase by 1. The increment happens when you reach the line Next a.
Once the for loop finishes running, a will have the value 5. Note that when the 5th iteration finishes running, a will have the value 6. Since 6 is not within the given range of the for loop, VBA exits the for loop. To get the correct number in the next message box, we use a-1 instead of a.
In most cases, the task can be coded using either a for loop or a while loop. It depends on the situation, but using either usually has no difference, more of a preference issue.
For each Loop
For collections, which are objects that contain a group of the same object, we can use the For Each method to iterate through the collection.
The following sub display the formula for each cells in the range A1:A3.
Sub ForEachLoop()
Dim myCell as Range
For Each myCell in Range("A1:A3")
MsgBox myCell.Formula
Next myCell
End Sub
The following functions returns the product of positive numbers within the range of cells. It will return 1 if none of the cells is positive.
Function ProdPositive(Cells As Range)
ProdPositive = 1
For Each cell In Cells
If cell.Value > 0 Then
ProdPositive = ProdPositive * cell.Value
End If
Next cell
End Function
Another example is to remove all the charts in the activesheet.
Sub RemoveAllChart()
Dim mycharts As ChartObject
For Each mycharts In ActiveSheet.ChartObjects
mycharts.Delete
Next
End Sub
Example: Relative Strength Index
Here is a spreadsheet to calculate RSI.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | n | 14 | ||||
| 2 | Date | Price | U | D | RS | RSI |
| 3 | 02-May-16 | 10 | =IF(B4>B3,1,0) |
=IF(B4<B3,1,0) |
=IF(COUNT($A$4:A4)>=$B$2,SUM(OFFSET(C4,0,0,-$B$1))/SUM(OFFSET(D4,0,0,-$B$1)),"") |
=100*E4/(1+E4) |
| 4 | 03-May-16 | 9 | ||||
| 5 | 04-May-16 | 8 |
To show a more elaborate example of iteration, we go through the code for the Relative Strength Index(RSI) indicator. We know that RSI only requires past price data.
Here we are setting Prices as an input, and telling VBA that this input should be a range. We then define up, down as long variables for up, down. We also define RS as double variable as relative strength. For calculation, we define count as a long variable and day as a range variable. After which, we assign the initial values of 0 to up_day, down and count.
Function RSI(Prices As Range)
Dim up, down, count As Long
Dim RS As Double
Dim day As Range
up = 0
down= 0
count = 1
For Each day In Prices
If count > 1 Then
If day.Value > day.Offset(-1, 0).Value Then
up = up+ 1
ElseIf day.Value < day.Offset(-1, 0).Value Then
down = down + 1
End If
End if
count = count + 1
Next day
If down <> 0 Then
RS = up / down
RSI = 100 * (RS / (1 + RS))
else
RSI = 100
End If
End Function
Here, the loop just runs through the data, and adds numbers to up and down accordingly. When the loop is done, we can then calculate RSI.
Now we see how this RSI function applies in spreadsheet as below.
| A | B | C | |
|---|---|---|---|
| 1 | n | 14 | |
| 2 | Date | Price | RSI |
| 3 | 02-May-16 | 10 | =IF(COUNT($A$3:A3)>=$B$1,RSI(OFFSET(B2,-$B$1-1,0))), "") |
| 4 | 03-May-16 | 9 | |
| 5 | 04-May-16 | 8 |