Conditions and Loops

Conditions are essential to any programing language and behave similarly to all of them. In Excel VBA there are two main types of conditions: The IF statement, and the Select Case statement.

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

Popular Posts