If-Then is VBA’s most important control structure. You’ll probably use this command on a daily basis.
As in many other aspects of life, effective decision-making is the key to success in writing CAD or any other macros.
If-Then structure has this basic syntax:
If condition Then statements [Else elsestatements]
Use the If-Then structure when you want to execute one or more statements conditionally.
The optional Else clause, if included, lets you execute one or more statements if the condition you’re testing is not true.
Sound confusing? Don’t worry; a few examples make this crystal clear.
The following routine demonstrates the
If-Then structure without the optional Else clause:
Sub GoodMorning() If Time < 0.5 Then MsgBox “Good Morning.” End Sub
GoodMorning procedure uses VBA’s
Time function to get the system time.
If the current system time is less than .5 (in other words, before noon), the routine displays a friendly greeting.
If Time is greater than or equal to .5, the routine ends and nothing happens.
To display a different greeting if Time is greater than or equal to .5, add another If-Then statement after the first one:
Sub GoodMorning2() If Time < 0.5 Then MsgBox “Good Morning.” If Time >= 0.5 Then MsgBox “Good Afternoon.” End Sub
Notice that I used
>= (greater than or equal to) for the second If-Then statement.
This ensures that the entire day is covered. Had I used
> (greater than), then no message would appear if this procedure were executed at precisely 12:00 noon.
Another approach to the preceding problem uses the
Here’s the same routine recoded to use the
Sub GoodMorning3() If Time < 0.5 Then MsgBox “Good Morning.” Else _ MsgBox “Good Afternoon.” End Sub
Notice that I use the line continuation character (underscore) in the preceding example.
The If-Then-Else statement is actually a single statement.
VBA provides a slightly different way of coding If-Then-Else constructs that use an End-If statement.
GoodMorning procedure can be rewritten as:
Sub GoodMorning4() If Time < 0.5 Then MsgBox “Good Morning.” Else MsgBox “Good Afternoon.” End If End Sub
In fact, you can insert any number of statements under the
If part, and any number of statements under the
I prefer to use this syntax because it’s easier to read and makes the statements shorter.
What if you need to expand the
GoodMorning routine to handle three conditions: morning, afternoon, and evening?
You have two options: Use three
If-Then statements or use a nested
Nesting means placing an If-Then-Else structure within another If-Then-Else structure.
The first approach, the three statements, is simplest:
Sub GoodMorning5() If Time < 0.5 Then Msg = “Morning.” If Time >= 0.5 And Time < 0.75 Then Msg = “Afternoon.” If Time >= 0.75 Then Msg = “Evening.” MsgBox “Good” & Msg End Sub
Msg variable gets a different text value, depending on the time of day.
MsgBox statement displays the greeting: Good Morning, Good Afternoon, or Good Evening.
The following routine performs the same action but uses an If-Then-End If structure:
Sub GoodMorning6() Dim Msg As String If Time < 0.5 Then Msg = “Morning.” If Time >= 0.5 And Time < 0.75 Then Msg = “Afternoon.” If Time >= 0.75 Then Msg = “Evening.” End If MsgBox “Good” & Msg End Sub
In the previous examples, every statement in the routine is executed — even in the morning.
A more efficient structure would exit the routine as soon as a condition is found to be true.
In the morning, for example, the procedure should display the Good Morning message and then exit — without evaluating the other superfluous conditions.
With a tiny routine like this, you don’t have to worry about execution speed.
But for larger applications in which speed is important, you should know about another syntax for the If-Then structure.
ElseIf syntax follows:
If condition Then [statements] [Else condition-n Then [elseifstatements]] [Else [elsestatements]]
Here’s how you can rewrite the
GreetMe routine by using this syntax:
Sub GoodMorning7() Dim Msg As String If Time < 0.5 Then Msg = “Morning.” ElseIf Time >= 0.5 And Time < 0.75 Then Msg = “Afternoon.” Else Msg = “Evening.” End If MsgBox “Good” & Msg End Sub
When a condition is
true, VBA executes the conditional statements and the If structure ends.
In other words, VBA doesn’t waste time evaluating the extraneous conditions, which makes this procedure a bit more efficient than the previous examples.
The trade-off (there are always trade-offs) is that the code is more difficult to understand. (Of course, you already knew that.)
Select Case structure
Select Case structure is useful for decisions involving three or more options (although it also works with two options, but using If-Then-Else structure is more efficient for that).
The syntax for the
Select Case structure follows:
Select Case testexpression [Case expressionlist-n [statements-n]] [Case Else [elsestatements]] End Select
Don’t be scared off by this official syntax. Using the Select Case structure is quite easy.
Select Case example
The following example shows how to use the Select Case structure.
This also shows another way to code the examples presented in the previous section:
Sub SelectPartLength() Dim PartNumber As Integer Dim PartLength As Integer PartNumber = InputBox(“Please Enter part number:”) Select Case PartNumber Case Part001 PartLength = 1 Case Part002 PartLength = 2 Case Part003 PartLength = 3 End Select MsgBox “Part Length for this” & PartNumber & “is” & PartLength End Sub
In this example, the
PartNumber variable is being evaluated.
The routine is checking for three different cases.
Next post will be about VBA Looping.