|
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 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 < Less than > 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.
. Truth table for the AND operator
Condition1 Condition2 Condition1 AND Condition2
True True True True False False False True False False False False
Here both condition1 and condition2 has to be satisfied.
Truth table for the OR operator
Condition1 Condition2 Condition1 OR Condition2 True True True True False True False True True False False False
Here either condition1 or condition2 has to be satisfied.
Truth table for the NOT operator
Condition1 NOT Condition1True False False True
Here only one Condition has to fulfilled as wrong/false
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!”) MsgBox (“The answer is “ & age) End If
If (userGuess < age) Then MsgBox (“Too low!”) MsgBox (“The answer is “ & age) End If
If (userGuess = age) Then MsgBox (“You got it!”)
End Sub
You enter your age as 25. Then the program ask the user to enter a number using a input box.
The If/Then structures each use one condition that compares the values stored in the userGuess and age variables. For example, if the user guess correctly, i.e. when the condition age equal user guess is true, the message box in the If/Then structure with the true condition is executed.
You should use the If...Then...Else statement if you want to · execute some code if a condition is true · select one of two blocks of code to execute
If (condition) ‘This block of code executes if the condition is true
Else ‘This block of code executes if the condition is false. End If
The If/Then structures in the age guess procedure can also be written as follows, where <> is the “not equal” operator
If (userGuess <> answer) Then MsgBox (“Wrong! The answer is “ & answer) Else MsgBox (“You got it!”) End If
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) Or (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 < b) THEN ‘5 < 7
IF (a < c) THEN ‘5 < 9 Result = a ‘5 ELSE Result = c END IF
ELSE
IF (b < c) THEN Result = b ELSE Result = c END IF
END IF MsgBox (“The smallest is “ & Result)
End Sub
The above code segment uses variable Result to hold the smallest value. 3 variables are given a value and the program will find the smallest value and outputs it to the user. So the smallest here is 5.
Another option regarding If/Then/Else structures is the ElseIf clause. The ElseIf clause is used like the Else clause with a conditional expression. You can use the if...then...elseif statement if you want to select one of many blocks of code to execute: Look at the example below,
Private Sub Greeting() Dim theHour As Integer
theHour =hour(time)
If theHour = 9 then MsgBox("Just started...!")
elseif theHour = 11 then MsgBox ("Hungry!")
elseif theHour = 12 then MsgBox ("Ah, lunch-time!")
elseif theHour = 17 then MsgBox ("Time to go home!")
else MsgBox ("Unknown") end if
End Sub
The variable theHour holds an integer value between 0 and 23. The first If/Then/Else structure is checking if the time of the day is between 8:00 A.M. and 5:00 P.M.
If theHour = 9 expression is true then a message box is used to display the string “Just started…!”. (Remember that it had to be between 9:00 A.M. and 5:00 P.M. to get to this point.)
After that if theHour holds 11 then, the string “Hungry” is shown in a message box. And so on.
There is no limit to the number of ElseIf clauses that can be used and you can also nest more If/Then/Else structures inside an ElseIf clause.
b) Select/Case
You can use many ways to accomplish the same task with If/Then/Else and ElseIf code structures but sometimes using too many of the If/Then/Else and ElseIf statements can make it difficult to follow the logic of your program.
An easier more efficient way is to use the Select/Case code structure in situations where you find yourself using a large number of ElseIf statements. This is how it works: First we have a single expression (most often a variable), that is evaluated once. The value of the expression is then compared with the values for each Case in the structure. If there is a match, the block of code associated with that Case is executed.
Select Case expression Case condition1 ‘This block of code executes if condition1 is true.
Case condition2 ‘This block of code executes if condition2 is true. ‘There is no limit on the number of cases you can use
Case Else ‘This block of code executes if none of the other conditions were true. End Select
As you can see above the Select/Case structure must begin with Select Case and end with End Select. The expression immediately following Select Case is usually a variable of numerical or string data type.
As you can see above the Select/Case structure must begin with Select Case. Immediately after that is the expression. This expression is a variable of numerical or string data type. The Select Case structure end with End Select.
Next, a list of one or more code blocks is entered just below the keyword Case and a condition. The condition is a comparison to the expression in the opening line of the structure. VBA will run through the list until it finds a condition that evaluates as true, then executes the block of code within that case element. Any additional case elements following one that evaluates as true are ignored, even if their conditions are also true. The last case element should use Case Else. This ensures that at least one block of code executes if all other conditions are false.
The following example uses a Select/Case structure in a VBA function designed to work with an Excel spreadsheet. The input value should be numerical.
This value represents a sales volume and is passed into the function and stored in the variable salesVolume. The variable salesVolume is used as the test expression for the Select/Case structure.
Public Function SalesGrade(salesVolume As Single) As String
Select Case salesVolume
Case Is > 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 Harder1”.
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. 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
The 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:
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. |
