The IF statement
We can check if a condition is met and then do
something about it with the following expression:
IF condition THEN something
The condition in the IF statement is determined
with the following relational operators: equal to (=), different (<>),
greater than (>), less than (<), greater or equal (>=), less or equal
(<=). More than one condition can be added to the statement with either of
the following logical operators: AND, OR, NOT
Let’s see how that would work for the car object
example we used earlier in this training. We may want to fill the gas tank only
when is almost empty (in this example 5% or less).
If
Car.TankCapacity <= 5% Then Car.FillTank
We can apply conditions to the state and
properties of objects, to variables, etc. This is no different to what we have
already done with the Excel formula “=IF(condition, Yes, No)” in a worksheet.
Let’s see some examples.
The example below checks if the user is under 18,
and if so it displays a warning message. The variable userAge would be
previously declared (Dim userAge As Integer) and given a value.
If userAge<18 Then MsgBox("Not allowed")
This other example has two conditions and uses
the AND operator so that both need to be met in order to display the message.
If userName = ”John” And userSurname = "Smith" Then
MsgBox("Welcome
John Smith")
End If
We use ELSE within the IF statement structure to
do something when the condition is not met. The example below assigns a string
value to the variable userGroup depending on the age of the user (userAge
variable). Users younger than 18 are minors, the rest are considered adults.
If userAge < 18 Then
userGroup = "Minor"
Else
userGroup = "Adult"
End If
And this other example adds yet another
condition using ELSEIF. We can add as many ELSEIF statements as needed. In this
case only the users between 18 and 64 are considered adults, while those older
than 64 belong to “Retired”.
If userAge < 18 Then
userGroup = "Minor"
ElseIf userAge >= 65 Then
userGroup = "Retired"
Else
userGroup = "Adult"
End If
The Select Case statement
The Select Case structure is useful when there
are three or more conditions that apply to the same expression. It is a good or
cleaner alternative to using multiple ELSEIF statements. The syntax for the
Select Case structure is the following:
Select Case expression
Case expressionitem1
something1
Case expressionitem2
something2
Case Else
something3
End Select
Let’s see an example of the Select Case
structure used to assign one of the four groups to the userGroup variable
depending on the age of the user.
Select Case userAge
Case 0 To 8
userGroup = "Infant"
Case 9 To 17
userGroup = "Minor"
Case 18 To 64
userGroup = "Adult"
Case Else
userGroup = "Retired"
End Select
Loops
Loops are also essential to all programing languages
and allow to repeat tasks a number of times. We may want to loop throughout a
data set within multiple rows, columns, or cells of a worksheet, or even
throughout different worksheets or workbooks.
The For/Next Loop
It allows to loop a defined number of times and
execute any code between the two statements. For requires a numerical variable
as a counter of loops. This simple example loops from 1 to 10 and displays a
message with the number each time.
Dim num As Integer
For num = 1 To 10
MsgBox (num)
Next num
This other example also loops from 1 to 10 but
it assigns those numbers one by one to each cell in column A.
Dim r As Integer
For r = 1 To 10
ActiveSheet.Cells(r, 1).Value = r
Next r
This other example uses an IF statement within
the loop to check if a condition is met (score=50) to display a message.
Dim score As Long
For score = 0 To 100
If score = 50 Then
MsgBox ("50% COMPLETE")
End
If
Next score
For Each/Next Loop
This loop works similarly to the For/Next loop
but instead of looping through numbers it loops through a collection of objects
(or an Array – covered in the next lesson). Therefore, it requires an object
variable or a Variant instead of a number. The first example below loops
through all worksheets in a workbook, looking for a sheet with a particular
name with an IF statement.
Dim ws As Worksheet
For Each ws In Worksheets
If ws.Name = "TargetSheet" Then
MsgBox ("I found my sheet")
End
If
Next ws
This other example loops through all cells
within a range and displays a message with the value of the cell each time.
For Each rngCell In Range("A1:A10")
MsgBox (rngCell.Value)
Next rngCell
Do While and Do Until Loops
Do loops work while or until a condition is met.
We can also omit the While or Until statements and add a condition to quit the
loop with an Exit Do statement to avoid an infinite loop.
The While or Until statements can be placed
either at the top (Do While or Do Until) or at the bottom of the loop (Loop
While or Loop Until), depending on where we need to check the condition and
exit the loop. We can see that in the two examples below that loop through 10
numbers and display a message each time.
Dim items As Integer
Do While items < 10
MsgBox ("Item " & items + 1
& " completed")
items
= items + 1
Loop
Dim items As Integer
Do
items = items + 1
MsgBox
("Item " & items & " completed")
Loop Until items = 10
In this other example we loop through all values
in column A (until the cell is empty) to find the last row with content.
Dim r As Integer
Do Until Range("A" & r +
1).Value = ""
r
= r + 1
Loop
MsgBox ("Number of rows with
content=" & r)
No comments:
Post a Comment