Programming Fundamentals Using VBA - The ElseIf Structure
(Page 6 of 9 )
You can combine several If structures using ElseIf. As an example:
Sub ifTest()
Dim intNum as Integer
intNum = 12
If intNum = 1 Then
Debug.Print “This is the lowest number”
ElseIf intNum = 15 Then
Debug.Print “This is the highest number”
Else
Debug.Print “The number is between 1 and 15”
End If
End Sub
You can use as many ElseIf statements as necessary to perform as many conditional tests as necessary.
Select Case Structure If you find yourself using a lot of ElseIf structures, you may want to consider the Select Case structure. It will result in easier-to-read code. Using the ElseIf example in the previous section, you would use Select Case as follows:
Sub ifTest()
Dim intNum as Integer
intNum = 2
Select Case intNum
Case 1
Debug.Print “This is the lowest number”
Case 15
Debug.Print “This is the highest number”
Case Else
Debug.Print “The number is between 1 and 15”
End Select
End Sub
Of course you could add a case to match any situation. VBA will keep going through the structure until it finds a match with the value of intNum and then carry out the instructions. If it can’t make a match, it defaults to Case Else. The Case Else statement is optional, but I strongly recommend that you always use it to have all your bases covered.
If you have multiple cases in which you want to carry out the same set of instructions, you can use the syntax:
Case 1, 2, 3…
Or you could use
Case 1 To 4
IIF
There is one other structure that we will take a quick look at: IIF. This is referred to as the Immediate If. This is handy if you want to assign the final value to a variable because its syntax is self-contained. The correct syntax is
IIF(conditional test, value for True, value for False)
So, working code might look something like this:
strMessage = IIF(intNum > 10, “Number is greater than 10”, _
“Number is less than 10”)
The performance of the IIF structure is somewhat slow and rarely used by programmers in a larger programming project.
Now let’s turn our attention to the second type of control structure—looping.
Loops You use loops when you need to have a block of code repeated a certain number of times or until an event of some sort happens. The number of times the code repeats can be controlled by a counter. This is called counter-controlled repetition. The second type is called sentinel-controlled repetition. We will look at both types.
For…Next Loop The For…Next loop is an example of a counter-controlled repetition. Using either actual numbers, or variables, you can set the following components for the loop:
- Counter This is the heart of the loop. It tracks the number of times the loop has repeated.
- Start This is the starting number for the counter. It is rare, if ever, to set this to anything else but 1. (Occasionally you may use a different start number for mathematical calculations.)
- End This number marks the end of the loop, where you want the counter to stop.
- Step You can specify a number for the counter to increment with each loop. This part is optional.
Here is an example of the syntax for a For…Next loop:
Dim intCounter As Integer
For intCounter = 1 To 25
…….
Next
To repeat, either the start or end numbers can be variables. Notice that the final line of the loop, unlike previous structures we have worked with, is not End, but Next. This instructs the counter to advance to the next number.
We also could have used the following syntax to declare the loop:
For intCounter = 1 To 25 Step 5
This forces the counter, in this case intCounter, to increment by five on every loop. As a result, the For loop will run five times.
It is not unusual to include If…Then structures within a loop. Let’s try the one that follows:
Sub forTest()
Dim intCounter As Integer
For intCounter = 1 To 10
If (intCounter Mod 2) = 0 Then
Debug.Print intCounter & " is an even number"
Else
Debug.Print intCounter & " is an odd number"
End If
Next
End Sub
All right! I can hear my editor yelling at me that I snuck a couple of extra things in here. Let’s take a closer look at a few things in this code.
Here we see a For…Next loop that is instructed to make 10 passes. Within that, I wanted to test to see if a particular iteration was odd or even. I used an If…Then…Else structure to do that. Think about this: we have an If…Then…Else structure within a For…Next structure. When you have one structure within another, it is called a nested structure, which is very common in programming.
However, I asked the conditional statement to perform a calculation—specifically, division. I used the keyword Mod, which is short for modulus. Mod returns the remainder of the division (what is to the right of the decimal place). If you divide an even number by 2, the remainder is 0. So, if the counter is divided by 2 and there is a 0 remainder, it triggers the If condition. If the remainder is other than 0, the Else condition is triggered.
The Next just increments the counter.
If you run this subroutine in the Immediate window, you should see the result shown here:
Let’s now turn our attention to the other type of loop.
Next: Do Loop >>
More Visual Basic.NET Articles
More By McGraw-Hill/Osborne
|
 This article is excerpted from chapter six of the book Access VBA Programming, written by Charles E. Brown and Ron Petusha (McGraw-Hill/Osborne, 2004; ISBN: 0072231971). Check it out at your favorite bookstore today. Buy this book now.
|
|
