Advanced VBA

Learning Objectives:

  • Conditional Statements
  • Iterative Statements

Conditionals

There are 4 main types of conditionals that we will use in VBA:

  1. If,
  2. If-else,
  3. If-ElseIf-else, and
  4. 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
Previous
Next

Last updated on May 4, 2019

Edit this page