value in Row 1 of the active sheet:
Sub SelectNegative() Dim Cell As Range For Each Cell In Range("1:1") If Cell.Value < 0 Then Cell.Select Exit For End If Next Cell End Sub
This example uses an If-Then construct to check the value of each cell. If a cell is negative, it's selected, and then the loop ends when the Exit For statement is executed.
Controlling Code Execution
Some VBA procedures start at the top and progress line by line to the bottom. Macros that you record, for example, always work in this fashion. Often, however, you need to control the flow of your routines by skipping over some statements, executing some statements multiple times, and testing conditions to determine what the routine does next.
The preceding section describes the For Each-Next construct, which is a type of loop. This section discusses the additional ways of controlling the execution of your VBA procedures.
GoTo statements
If-Then constructs
Select Case constructs
For-Next loops
Do While loops
Do Until loops
GoTo statements
The most straightforward way to change the flow of a program is to use a GoTo statement. This statement simply transfers program execution to a new instruction, which must be preceded by a label (a text string followed by a colon, or a number with no colon). VBA procedures can contain any number of labels, but a GoTo statement can't branch outside a procedure.
The following procedure uses the VBA InputBox function to get the user's name. If the name is not Howard, the procedure branches to the WrongName label and ends. Otherwise, the procedure executes some additional code. The Exit Sub statement causes the procedure to end.
Sub GoToDemo() UserName = InputBox("Enter Your Name:") If UserName <> "Howard" Then GoTo WrongName MsgBox ("Welcome Howard…") ' -[More code here] - Exit Sub WrongName: MsgBox "Sorry. Only Howard can run this macro." End Sub
This simple procedure works, but it's not an example of good programming. In general, you should use the GoTo statement only when you have no other way to perform an action. In fact, the only time you really need to use a GoTo statement in VBA is for error handling (refer to Chapter 4, “Working with VBA Sub Procedures”).
Finally, it goes without saying that the preceding example is not intended to demonstrate an effective security technique!
If-Then constructs
Perhaps the most commonly used instruction grouping in VBA is the If-Then construct. This common instruction is one way to endow your applications with decision-making capability. Good decision-making is the key to writing successful programs.
The basic syntax of the If-Then construct is as follows:
If condition Then true_instructions [Else false_instructions]
The If-Then construct is used to execute one or more statements conditionally. The Else clause is optional. If included, the Else clause lets you execute one or more instructions when the condition that you're testing isn't True.
The following procedure demonstrates an If-Then structure without an Else clause. The example deals with time, and VBA uses a date-and-time serial number system similar to Excel's. The time of day is expressed as a fractional value—for example, noon is represented as .5. The VBA Time function returns a value that represents the time of day, as reported by the system clock.
In the following example, a message is displayed if the time is before noon. If the current system time is greater than or equal to .5, the procedure ends, and nothing happens.
Sub GreetMe1() If Time < 0.5 Then MsgBox "Good Morning" End Sub
Another way to code this routine is to use multiple statements, as follows:
Sub GreetMe1a() If Time < 0.5 Then MsgBox "Good Morning" End If End Sub
Note that the If statement has a corresponding End If statement. In this example, only one statement is executed if the condition is True. You can, however, place any number of statements between the If and End If statements.
If you want to display a different greeting when the time of day is after noon, add another If-Then statement, as follows:
Sub GreetMe2() If Time < 0.5 Then MsgBox "Good Morning" If Time >= 0.5 Then MsgBox "Good Afternoon" End Sub
Notice that we used >= (greater than or equal to) for the second If-Then statement. This covers the remote chance that the time is precisely 12 p.m.
Another approach is to use the Else clause of the If-Then construct. Here's an example:
Sub GreetMe3() If Time < 0.5 Then MsgBox "Good Morning" Else _ MsgBox "Good Afternoon" End Sub
Notice that we used the line continuation sequence; If-Then-Else is actually a single statement.
If you need to execute multiple statements based on the condition, use this form:
Sub GreetMe3a() If Time < 0.5 Then MsgBox "Good Morning" ' Other statements go here Else MsgBox "Good Afternoon" ' Other statements go here End If End Sub
If you need to expand a routine to handle three conditions (for example, morning, afternoon, and evening), you can use either three If-Then statements or a form that uses ElseIf. The first approach is simpler.
Sub GreetMe4() If Time < 0.5 Then MsgBox "Good Morning" If Time >= 0.5 And Time < 0.75 Then MsgBox "Good Afternoon" If Time >= 0.75 Then MsgBox "Good Evening" End Sub
The value 0.75 represents 6 p.m.—three-quarters of the way through the day and a good point at which to call it an evening.
In the preceding examples, every instruction in the procedure gets executed, even if the first condition is satisfied (that is, it's morning). A more efficient procedure would include a structure that ends the routine when a condition is found to be True. For example, it might display the “Good Morning” message in the morning and then exit without evaluating the other, superfluous conditions. True, the difference in speed is inconsequential when you design a procedure as small as this routine. For more complex applications, however, you need another syntax.
If condition Then [true_instructions] [ElseIf
