How to Use If-Then in Excel 2016 VBA
If-Then is VBA’s most important control structure. You’ll probably use this command on a daily basis. 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. Here’s a simple CheckUser procedure, recoded to use the If-Then-Else structure:
Sub CheckUser2() UserName = InputBox(“Enter Your Name: “) If UserName = “Satya Nadella” Then MsgBox (“Welcome Satya…”) ‘ …[More code here] … Else MsgBox “Sorry. Only Satya Nadella can run this.” End If End Sub
The following routine demonstrates the If-Then structure without the optional Else clause:
Sub GreetMe() If Time < 0.5 Then MsgBox “Good Morning” End Sub
The GreetMe procedure uses VBA’s Time function to get the system time. If the current time is less than .5, 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, you can add another If-Then statement after the first one:
Sub GreetMe2() If Time < 0.5 Then MsgBox “Good Morning” If Time >= 0.5 Then MsgBox “Good Afternoon” End Sub
Notice that >= (greater than or equal to) is used for the second If-Then statement. This ensures that the entire day is covered. Had > (greater than) been used, no message would appear if this procedure were executed at precisely 12:00 noon. That’s pretty unlikely, but with an important program like this, you don’t want to take any chances.
An If-Then-Else example
Another approach to the preceding problem uses the Else clause. Here’s the same routine recoded to use the If-Then-Else structure:
Sub GreetMe3() If Time < 0.5 Then MsgBox “Good Morning” Else _ MsgBox “Good Afternoon” End Sub
Notice that the line continuation character (underscore) is used 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. Therefore, the GreetMe procedure can be rewritten as
Sub GreetMe4() 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 Else part. This syntax is easier to read and makes the statements shorter.
What if you need to expand the GreetMe routine to handle three conditions: morning, afternoon, and evening? You have two options: Use three If-Then statements or use a nested If-Then-Else structure. Nesting means placing an If-Then-Else structure within another If-Then-Else structure. The first approach, using three If-Then statements, is simpler:
Sub GreetMe5() 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” MsgBox “Good “ & Msg End Sub
A new twist was added with the use of a variable. The Msg variable gets a different text value, depending on the time of day. The 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 GreetMe6() Dim Msg As String If Time < 0.5 Then Msg = “Morning” End If If Time >= 0.5 And Time < 0.75 Then Msg = “Afternoon” End If 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. A slightly 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 critical, you should know about another syntax for the If-Then structure.
Here’s how you can rewrite the GreetMe routine by using this syntax:
Sub GreetMe7() 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, this procedure is a bit more efficient than the previous examples. The trade-off is that the code is more difficult to understand.
Another If-Then example
Here’s another example that uses the simple form of the If-Then structure. This procedure prompts the user for a quantity and then displays the appropriate discount, based on the quantity the user enters:
Sub ShowDiscount() Dim Quantity As Long Dim Discount As Double Quantity = InputBox(“Enter Quantity:”) If Quantity > 0 Then Discount = 0.1 If Quantity >= 25 Then Discount = 0.15 If Quantity >= 50 Then Discount = 0.2 If Quantity >= 75 Then Discount = 0.25 MsgBox “Discount: “ & Discount End Sub
Notice that each If-Then statement in this routine is executed, and the value for Discount can change as the statements are executed. However, the routine ultimately displays the correct value for Discount because the If-Then statements are in order of ascending Discount values.
The following procedure performs the same tasks by using the alternative ElseIf syntax. In this case, the routine ends immediately after executing the statements for a true condition:
Sub ShowDiscount2() Dim Quantity As Long Dim Discount As Double Quantity = InputBox(“Enter Quantity: “) If Quantity > 0 And Quantity < 25 Then Discount = 0.1 ElseIf Quantity >= 25 And Quantity < 50 Then Discount = 0.15 ElseIf Quantity >= 50 And Quantity < 75 Then Discount = 0.2 ElseIf Quantity >= 75 Then Discount = 0.25 End If MsgBox “Discount: “ & Discount End Sub
These multiple If-Then structures are rather cumbersome. You may want to use the If-Then structure only for simple binary decisions.