Specifically, in this Lesson, I will discuss:
Conditional Logic
Conditional Statements
If/Then/Else and Select/Case Code Structures
Looping
Conditional Logic
Conditional Statements
If/Then/Else and Select/Case Code Structures
Looping
All programming languages contain logical constructions for controlling the sequence of statement through a program, and VBA is no exception. When we say A > B or A = B, we mean to say
* The value of A is greater than the value of B and,
* The value of A equals the value of B
The statements above will be evaluated as true or false by a computer program. Then, based on the result of the logic, the program selects a direction in which to proceed.
Apart from this, a programming languages also use the operators AND, OR, NOT. We will understand more on this logical operators when we look at example programs later.
Generally,
= Tests for equality
<> Tests for inequality
<> Greater than
<= Less than or equal to >= Greater than or equal to
As for the AND, OR, and NOT operators, the 3 tables below illustrates the rules.
* The value of A is greater than the value of B and,
* The value of A equals the value of B
The statements above will be evaluated as true or false by a computer program. Then, based on the result of the logic, the program selects a direction in which to proceed.
Apart from this, a programming languages also use the operators AND, OR, NOT. We will understand more on this logical operators when we look at example programs later.
Generally,
= Tests for equality
<> Tests for inequality
<> Greater than
<= Less than or equal to >= Greater than or equal to
As for the AND, OR, and NOT operators, the 3 tables below illustrates the rules.
Click for larger image
1) Conditionals and Branching
Let’s begin to examine a simple yet very useful VBA code structure. You will use it all the time in your program i.e. the If/Then/Else. This structure is known as both a conditional and branching structure because it uses conditional statements to change the flow or direction of program execution.
a) If / Then / Else
There are several ways to implement this code structure. The most basic uses just one line of code.
If (condition) Then Code statement
In this example, If a condition is evaluates as true the code statement following Then will be executed, otherwise code execution proceeds with the next statement. If you need more than one code statement executed, then you should use the block form of If / Then and end the code structure with End If
If (condition) Then
‘Block of code statements
End If
Again, the condition must be true or the block of code statements will not execute.
The following procedure is a simple age guessing game where the user has to guess the age entered. Three If/Then structures are used to determine what message is output to the user depending on their guess.
Private Sub GuessAge()
Dim userGuess As Integer
Dim age As Integer
age = 25
userGuess = Val(InputBox(“Guess a number between 20 and 30.”, “Guess Age”))
If (userGuess > age) Then
MsgBox (“Too high!”)
Let’s begin to examine a simple yet very useful VBA code structure. You will use it all the time in your program i.e. the If/Then/Else. This structure is known as both a conditional and branching structure because it uses conditional statements to change the flow or direction of program execution.
a) If / Then / Else
There are several ways to implement this code structure. The most basic uses just one line of code.
If (condition) Then Code statement
In this example, If a condition is evaluates as true the code statement following Then will be executed, otherwise code execution proceeds with the next statement. If you need more than one code statement executed, then you should use the block form of If / Then and end the code structure with End If
If (condition) Then
‘Block of code statements
End If
Again, the condition must be true or the block of code statements will not execute.
The following procedure is a simple age guessing game where the user has to guess the age entered. Three If/Then structures are used to determine what message is output to the user depending on their guess.
Private Sub GuessAge()
Dim userGuess As Integer
Dim age As Integer
age = 25
userGuess = Val(InputBox(“Guess a number between 20 and 30.”, “Guess Age”))
If (userGuess > age) Then
MsgBox (“Too high!”)
MsgBox (“The answer is “ & age)
End If
If (userGuess < userguess =" age)"> is the “not equal” operator
If (userGuess <> answer) Then
MsgBox (“Wrong! The answer is “ & answer)
Else
MsgBox (“You got it!”)
End If
End Sub
Here you see the keyword Else is used to direct the program to another block of code that is executed if the condition (userGuess <> age) evaluates to false.
There is no limit on the number of conditions you can use with an If/Then code structure.
The condition
If (userGuess <> age) Then
can also be written as
If (userGuess <> age) Then
When the logical operator Or is used only one of the condition evaluated as true, then the expression returns true and the logic is maintained.
There are numerous possibilities for achieving the same logic when using If/Then/Else and conditionals.
If x = 5 then
MsgBox “Hello”
Else
MsgBox “Goodbye”
End if
The first block of code will be executed if the condition is true, and the other block will be executed otherwise (if x is not equal to 5).
You can also use nested the If/Then/Else code structure if you want to. The procedure below outputs a short message to the user to show the smallest value.
Private Sub FindSmallest ()
Dim a as integer
Dim b as integer
Dim c as integer
Dim Result as integer
a = 5
b = 7
c = 9
IF (a < result =" a" result =" c" result =" b" result =" c" thehour ="hour(time)" thehour =" 9" thehour =" 11" thehour =" 12" thehour =" 17" thehour =" 9"> 100000
SalesGrade = “Excellent”
Case 80000 To 100000
SalesGrade = “Mediocre”
Case Else
SalesGrade = “Work Harder!”
End Select
End Function
If sales volume is more than 100000 then the function will assigned the sales as “Excellent. And sales is between 80000 to 100000 then it is “Mediocre”. Lastly if sales volume is less than 80000 then it will tell user to “Work Harder!”.
Here is another example demonstrates the "select case" statement.
Private Sub FindDay()
d =Weekday(Date)
Select Case d
Case 1
MsgBox "Sleepy Sunday"
Case 2
MsgBox "Monday again!"
Case 3
MsgBox "Just Tuesday!"
Case 4
MsgBox "Wednesday!"
Case 5
MsgBox "Thursday..."
Case 6
MsgBox "Finally Friday!"
Case else
MsgBox "Super Saturday!!!!"
End Select
End Sub
The first case element uses Case 1 that is d =1. If this condition is evaluates as true, the message box will show “Sleepy Sunday”
For Case 2 that is d = 2, then the message box will displays “Monday Again!” And so on. Note that the last case element should use Case Else. This ensures that at least one block of code executes if all other conditions are false.
2) Looping
Most of the time when you write code, you want to allow the same block of code to run a number of times. You can use looping statements in your code to do this.
You may know how many times your program needs to loop, or variables used in your program’s code may determine this.
a) For-Next loops
First you’ve the For-Next loop.You can use a For...Next statement to run a block of code, when you know how many repetitions you want. Here’s the syntax for this
structure:
For count = start To end [Step stepval]
[statements]
[Exit For]
[statements]
Next [counter]
A counter variable is used to control the looping, which starts at one value and stops at another value. The statements between the For statement and the Next statement are the statements that get repeated in the loop.
A For-Next example
This routine loops 10 times and add all the integer from 1 to 10.
Sub SumUp()
Dim n As Integer
Dim t As Integer
For n = 1 To 10
t = t + n
Range(“A1”).Value = t
Next n
MsgBox " The total is " & t
End Sub
In this example, n (the loop counter variable) starts with a value of 1 and increases by 1 each time through the loop. The first time through the loop, the procedure add 1 to the variable t. The second time through (n = 2), where t = 1 and n = 2. Add them and you get t = 3. The third loop i.e. n = 3 and t = 3. Add them, you get t = 6 and so on. In the end t will equal 55.
A For-Next example with a Step
Using the Step keyword, you can increase or decrease the counter variable by the value you specify. You can use a Step value to skip some values in a For-Next loop. Here’s the same procedure as in the preceding section, rewritten to insert random numbers into every other cell:
Sub SumUp()
Dim n As Integer
Dim t As Integer
For n = 1 To 10 Step 2
t = t + n
Range(“A1”).Value = t
Next n
MsgBox " The total is " & t
End Sub
This time, n starts out as 1 and then takes on a value of 3, 5, 7, and so on.
The final n value is 9. The Step value determines how the n counter is incremented.
A For-Next example with an Exit For statement
You can exit a For...Next statement with the Exit For keyword. When VBA encounters this statement, the loop terminates immediately.
The example below demonstrates the Exit For statement. This routine identifies which of the active worksheet’s cells in column B has the smallest value:
Sub ExitForHow()
Dim MinVal As Double
Dim Row As Long
MinVal = Application.WorksheetFunction. _
Min(Range(“B:B”))
For Row = 1 To Rows.Count
If Range(“B1”).Offset(Row - 1, 0).Value = MinVal
Then
Range(“B1”).Offset(Row - 1, 0).Activate
MsgBox “Min value is in Row “ & Row
Exit For
End If
Next Row
End Sub
I use the Excel’s MIN function to find the minimum value in Column B and assigns the result to the MinVal variable. After that, the For-Next loop then checks each cell in the column. If the cell being checked is equal to MinVal, the routine will stop and the Exit For statement will terminates the loop.
Before the loop is terminated, the procedure activates the cell with the minimum value and show the user of its location. Notice that I use Rows.Count in the For statement to assign the number of rows in the worksheet.
A nested For-Next example
The examples above use relatively simple loops. However, like the If/Then/Else structure you can have any number of statements in the loop and nest For-Next loops inside other For-Next loops.
The following example uses a nested For-Next loop to format a 10-row-x-5-column range of cells into font bold (see Figure 6.). Please note that the routine executes the inner loop (the loop with the Row counter) once for each iteration of the outer loop (the loop with the Col counter). In other words, the routine executes the Cells(Row, Col) to format the font bold. (Fig 6.1 is the result)
This routine has loop 50 times i.e 10 x 5.
Sub FormatBold()
Dim Col As Single
Dim Row As Single
For Col = 1 To 5
For Row = 1 To 10
Cells(Row, Col).Font.Bold = True
Next Row
Next Col
End Sub
End If
If (userGuess < userguess =" age)"> is the “not equal” operator
If (userGuess <> answer) Then
MsgBox (“Wrong! The answer is “ & answer)
Else
MsgBox (“You got it!”)
End If
End Sub
Here you see the keyword Else is used to direct the program to another block of code that is executed if the condition (userGuess <> age) evaluates to false.
There is no limit on the number of conditions you can use with an If/Then code structure.
The condition
If (userGuess <> age) Then
can also be written as
If (userGuess <> age) Then
When the logical operator Or is used only one of the condition evaluated as true, then the expression returns true and the logic is maintained.
There are numerous possibilities for achieving the same logic when using If/Then/Else and conditionals.
If x = 5 then
MsgBox “Hello”
Else
MsgBox “Goodbye”
End if
The first block of code will be executed if the condition is true, and the other block will be executed otherwise (if x is not equal to 5).
You can also use nested the If/Then/Else code structure if you want to. The procedure below outputs a short message to the user to show the smallest value.
Private Sub FindSmallest ()
Dim a as integer
Dim b as integer
Dim c as integer
Dim Result as integer
a = 5
b = 7
c = 9
IF (a < result =" a" result =" c" result =" b" result =" c" thehour ="hour(time)" thehour =" 9" thehour =" 11" thehour =" 12" thehour =" 17" thehour =" 9"> 100000
SalesGrade = “Excellent”
Case 80000 To 100000
SalesGrade = “Mediocre”
Case Else
SalesGrade = “Work Harder!”
End Select
End Function
If sales volume is more than 100000 then the function will assigned the sales as “Excellent. And sales is between 80000 to 100000 then it is “Mediocre”. Lastly if sales volume is less than 80000 then it will tell user to “Work Harder!”.
Here is another example demonstrates the "select case" statement.
Private Sub FindDay()
d =Weekday(Date)
Select Case d
Case 1
MsgBox "Sleepy Sunday"
Case 2
MsgBox "Monday again!"
Case 3
MsgBox "Just Tuesday!"
Case 4
MsgBox "Wednesday!"
Case 5
MsgBox "Thursday..."
Case 6
MsgBox "Finally Friday!"
Case else
MsgBox "Super Saturday!!!!"
End Select
End Sub
The first case element uses Case 1 that is d =1. If this condition is evaluates as true, the message box will show “Sleepy Sunday”
For Case 2 that is d = 2, then the message box will displays “Monday Again!” And so on. Note that the last case element should use Case Else. This ensures that at least one block of code executes if all other conditions are false.
2) Looping
Most of the time when you write code, you want to allow the same block of code to run a number of times. You can use looping statements in your code to do this.
You may know how many times your program needs to loop, or variables used in your program’s code may determine this.
a) For-Next loops
First you’ve the For-Next loop.You can use a For...Next statement to run a block of code, when you know how many repetitions you want. Here’s the syntax for this
structure:
For count = start To end [Step stepval]
[statements]
[Exit For]
[statements]
Next [counter]
A counter variable is used to control the looping, which starts at one value and stops at another value. The statements between the For statement and the Next statement are the statements that get repeated in the loop.
A For-Next example
This routine loops 10 times and add all the integer from 1 to 10.
Sub SumUp()
Dim n As Integer
Dim t As Integer
For n = 1 To 10
t = t + n
Range(“A1”).Value = t
Next n
MsgBox " The total is " & t
End Sub
In this example, n (the loop counter variable) starts with a value of 1 and increases by 1 each time through the loop. The first time through the loop, the procedure add 1 to the variable t. The second time through (n = 2), where t = 1 and n = 2. Add them and you get t = 3. The third loop i.e. n = 3 and t = 3. Add them, you get t = 6 and so on. In the end t will equal 55.
A For-Next example with a Step
Using the Step keyword, you can increase or decrease the counter variable by the value you specify. You can use a Step value to skip some values in a For-Next loop. Here’s the same procedure as in the preceding section, rewritten to insert random numbers into every other cell:
Sub SumUp()
Dim n As Integer
Dim t As Integer
For n = 1 To 10 Step 2
t = t + n
Range(“A1”).Value = t
Next n
MsgBox " The total is " & t
End Sub
This time, n starts out as 1 and then takes on a value of 3, 5, 7, and so on.
The final n value is 9. The Step value determines how the n counter is incremented.
A For-Next example with an Exit For statement
You can exit a For...Next statement with the Exit For keyword. When VBA encounters this statement, the loop terminates immediately.
The example below demonstrates the Exit For statement. This routine identifies which of the active worksheet’s cells in column B has the smallest value:
Sub ExitForHow()
Dim MinVal As Double
Dim Row As Long
MinVal = Application.WorksheetFunction. _
Min(Range(“B:B”))
For Row = 1 To Rows.Count
If Range(“B1”).Offset(Row - 1, 0).Value = MinVal
Then
Range(“B1”).Offset(Row - 1, 0).Activate
MsgBox “Min value is in Row “ & Row
Exit For
End If
Next Row
End Sub
I use the Excel’s MIN function to find the minimum value in Column B and assigns the result to the MinVal variable. After that, the For-Next loop then checks each cell in the column. If the cell being checked is equal to MinVal, the routine will stop and the Exit For statement will terminates the loop.
Before the loop is terminated, the procedure activates the cell with the minimum value and show the user of its location. Notice that I use Rows.Count in the For statement to assign the number of rows in the worksheet.
A nested For-Next example
The examples above use relatively simple loops. However, like the If/Then/Else structure you can have any number of statements in the loop and nest For-Next loops inside other For-Next loops.
The following example uses a nested For-Next loop to format a 10-row-x-5-column range of cells into font bold (see Figure 6.). Please note that the routine executes the inner loop (the loop with the Row counter) once for each iteration of the outer loop (the loop with the Col counter). In other words, the routine executes the Cells(Row, Col) to format the font bold. (Fig 6.1 is the result)
This routine has loop 50 times i.e 10 x 5.
Sub FormatBold()
Dim Col As Single
Dim Row As Single
For Col = 1 To 5
For Row = 1 To 10
Cells(Row, Col).Font.Bold = True
Next Row
Next Col
End Sub
Fig 6.1 - Click for larger imageThe next example uses nested For-Next loops to initialize a three-dimensional array with the value 1. This routine executes the statement in the middle of all the loops (the assignment statement) 1,000 times, each time with a different combination of values for a, b, and c:
Sub ArrayLoops()
Dim MyMatrix(10, 10, 10)
Dim a As Integer
Dim b As Integer
Dim c As Integer
For a = 1 To 10
For b = 1 To 10
For c = 1 To 10
MyMatrix(a, b, c) = 1
Next c
Next b
Next a
End Sub
b) Do-While loop
When you want to execute a task or a series of tasks as long as a specific condition is true, use the Do-While loop. Here’s the Do-While loop syntax:
Do [While condition]
[statements]
[Exit Do]
[statements]
Loop
The following example uses a Do-While loop. This routine uses the active cell as a starting point and then travels down the column, multiplying each cell’s value by 3. The loop will stop when the routine encounters an empty cell.
Sub DoWhileNotEmpty()
Do While ActiveCell.Value <> Empty
ActiveCell.Value = ActiveCell.Value * 2
ActiveCell.Offset(1, 0).Select
Loop
End Sub
In contrast, when you need the loop instructions to execute at least, use a Do-Loop While loop. This example performs exactly as the previous procedure but uses a different loop syntax:
Sub LoopWhileNotEmpty()
Do
ActiveCell.Value = ActiveCell.Value * 3
ActiveCell.Offset(1, 0).Select
Loop While ActiveCell.Value <> Empty
End Sub
Note the key difference between the Do-While and Do-Loop While loops:
* The Do-While loop always performs its conditional test first and will not execute the statements inside the lopp if the test is not true.
* Whereas the Do-Loop While loop always performs its conditional test after the instructions inside the loop are executed. It run at least once.
Thus, this difference can have an important effect on how your program functions.
c) Do-Until loop
The Do-Until loop structure is quite similar to the Do-While structure. The only different between the two structures is :
· In a Do-While loop, a routine continues to execute while the condition remains true.
· Whereas in a Do-Untilloop, the program continue to loop until the condition is true.
Here’s the Do-Until syntax:
Do [Until condition]
statements]
[Exit Do]
[statements]
Loop
The following example is the same one written for the Do-While loop but
recoded to use a Do-Until loop:
Sub DoUntilEmpty()
Do Until IsEmpty(ActiveCell.Value)
ActiveCell.Value = ActiveCell.Value * 3
ActiveCell.Offset(1, 0).Select
Loop
End Sub
Another loop structure is the Do-Loop Until loop. The following example, which has the
same effect as the preceding procedure, demonstrates an alternate syntax for
this type of loop:
Sub LoopUntilNot Empty()
Do
ActiveCell.Value = ActiveCell.Value * 3
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Value)
End Sub
Again the reason and the difference between the Do Until -Loop and the Do-Loop Until
is the same as the Do-While-Loop discussed above. In the former, the test is performed at the beginning of the loop and the latter the condition is tested at the end of the loop
c) For Each -Next loop (Looping through a Collection)
VBA has another important type of looping – the For Each -Next loop. When you use this loop, the statements you specify between the For and Next statements execute for each element in the specified array or collection. For example, each workbook has a collection of worksheets (the Worksheets collection), and a Range can consists of a few cells.
When you need to loop through each object in a collection, use the For Each-
Next structure. The syntax is
For Each element In collection
[statements]
[Exit For]
[statements]
Next [element]
The following example loops through each worksheet in the active workbook and put a random number, r, in the Range(“A1”) of each worksheet:
Sub FillRandomValue()
Dim ws As Worksheet
Dim r as Integer
For Each ws In ActiveWorkbook.Worksheets
r = Int(Rnd)
ws.Range(“A1”).Value = r
Next ws
End Sub
Note that the variable ws is an object variable that represents each worksheet in the workbook. Actually you can use any variable name that you like to represent the worksheet.
The example that follows loops through the each cells in a range and format it in bold if it is a positive number and left it unchange if it is negative.
Sub FormatBold()
Dim Cell As Range
For Each Cell In Range(“A1:E50”)
If Cell.Value => 0 Then
Cell.Font.Bold = True
End If
Next Cell
End Sub
You can also use the For Each-Next to loop through each chart on a worksheet.
Here’s how:
Sub FormatCharts()
Dim cht As ChartObject
For Each cht In Sheets(“Sheet1”).ChartObjects
cht.Chart.ChartType = xl3DColumn
Next cht
End Sub
This program will loop through each chart on Sheet1 and changes each chart to a line chart.
Note that cht is a variable that represents each ChartObject.
I suggest that you use the For Each-Next structure if suitable as it can run faster than the For-Next one.
3) Summary
In this Part, I've explain to you how conditional and logic structures are used by VBA to construct a program.
Sub ArrayLoops()
Dim MyMatrix(10, 10, 10)
Dim a As Integer
Dim b As Integer
Dim c As Integer
For a = 1 To 10
For b = 1 To 10
For c = 1 To 10
MyMatrix(a, b, c) = 1
Next c
Next b
Next a
End Sub
b) Do-While loop
When you want to execute a task or a series of tasks as long as a specific condition is true, use the Do-While loop. Here’s the Do-While loop syntax:
Do [While condition]
[statements]
[Exit Do]
[statements]
Loop
The following example uses a Do-While loop. This routine uses the active cell as a starting point and then travels down the column, multiplying each cell’s value by 3. The loop will stop when the routine encounters an empty cell.
Sub DoWhileNotEmpty()
Do While ActiveCell.Value <> Empty
ActiveCell.Value = ActiveCell.Value * 2
ActiveCell.Offset(1, 0).Select
Loop
End Sub
In contrast, when you need the loop instructions to execute at least, use a Do-Loop While loop. This example performs exactly as the previous procedure but uses a different loop syntax:
Sub LoopWhileNotEmpty()
Do
ActiveCell.Value = ActiveCell.Value * 3
ActiveCell.Offset(1, 0).Select
Loop While ActiveCell.Value <> Empty
End Sub
Note the key difference between the Do-While and Do-Loop While loops:
* The Do-While loop always performs its conditional test first and will not execute the statements inside the lopp if the test is not true.
* Whereas the Do-Loop While loop always performs its conditional test after the instructions inside the loop are executed. It run at least once.
Thus, this difference can have an important effect on how your program functions.
c) Do-Until loop
The Do-Until loop structure is quite similar to the Do-While structure. The only different between the two structures is :
· In a Do-While loop, a routine continues to execute while the condition remains true.
· Whereas in a Do-Untilloop, the program continue to loop until the condition is true.
Here’s the Do-Until syntax:
Do [Until condition]
statements]
[Exit Do]
[statements]
Loop
The following example is the same one written for the Do-While loop but
recoded to use a Do-Until loop:
Sub DoUntilEmpty()
Do Until IsEmpty(ActiveCell.Value)
ActiveCell.Value = ActiveCell.Value * 3
ActiveCell.Offset(1, 0).Select
Loop
End Sub
Another loop structure is the Do-Loop Until loop. The following example, which has the
same effect as the preceding procedure, demonstrates an alternate syntax for
this type of loop:
Sub LoopUntilNot Empty()
Do
ActiveCell.Value = ActiveCell.Value * 3
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Value)
End Sub
Again the reason and the difference between the Do Until -Loop and the Do-Loop Until
is the same as the Do-While-Loop discussed above. In the former, the test is performed at the beginning of the loop and the latter the condition is tested at the end of the loop
c) For Each -Next loop (Looping through a Collection)
VBA has another important type of looping – the For Each -Next loop. When you use this loop, the statements you specify between the For and Next statements execute for each element in the specified array or collection. For example, each workbook has a collection of worksheets (the Worksheets collection), and a Range can consists of a few cells.
When you need to loop through each object in a collection, use the For Each-
Next structure. The syntax is
For Each element In collection
[statements]
[Exit For]
[statements]
Next [element]
The following example loops through each worksheet in the active workbook and put a random number, r, in the Range(“A1”) of each worksheet:
Sub FillRandomValue()
Dim ws As Worksheet
Dim r as Integer
For Each ws In ActiveWorkbook.Worksheets
r = Int(Rnd)
ws.Range(“A1”).Value = r
Next ws
End Sub
Note that the variable ws is an object variable that represents each worksheet in the workbook. Actually you can use any variable name that you like to represent the worksheet.
The example that follows loops through the each cells in a range and format it in bold if it is a positive number and left it unchange if it is negative.
Sub FormatBold()
Dim Cell As Range
For Each Cell In Range(“A1:E50”)
If Cell.Value => 0 Then
Cell.Font.Bold = True
End If
Next Cell
End Sub
You can also use the For Each-Next to loop through each chart on a worksheet.
Here’s how:
Sub FormatCharts()
Dim cht As ChartObject
For Each cht In Sheets(“Sheet1”).ChartObjects
cht.Chart.ChartType = xl3DColumn
Next cht
End Sub
This program will loop through each chart on Sheet1 and changes each chart to a line chart.
Note that cht is a variable that represents each ChartObject.
I suggest that you use the For Each-Next structure if suitable as it can run faster than the For-Next one.
3) Summary
In this Part, I've explain to you how conditional and logic structures are used by VBA to construct a program.
No comments:
Post a Comment