If you want your procedure to behave differently depending on the conditions it meets you have to use one of the conditional statements. In this lesson, I will show you how to write such statements.
If Then Statement
If you use the IF..THEN statement, the code will be executed if the condition is met. Look at the following example.
Example 1:
1 2 3 4 5 6 |
Dim myTime As Date Dim timeToDouble As Double myTime = #11:45:12 AM# timeToDouble = TimeValue(myTime) If myTime < 0.5 Then MsgBox ("Good Morning!") |
Line 1, 2: Here, you have two variables: one declared as Date and the other one as Double.
Line 3: Time is assigned to the variable.
Line 5: The myTime value is converted to Double and assigned to the timeToDouble variable.
Line 6: The statement If myTime < 0.5 checks whether it is morning (more about storing dates in the lesson How Excel stores dates and times). If the value is lower than 0.5 then Excel displays „Good morning!”. If the value is larger or equal to 0.5 then Excels does nothing.
Example 2:
1 2 3 4 5 6 7 8 |
Dim myTime As Date Dim timeToDouble As Double myTime = #11:45:12 AM# timeToDouble = TimeValue(myTime) If myTime < 0.5 Then MsgBox ("Good Morning!") End If |
In the second example the IF..THEN statement occupies two lines instead of one as it did in the previous example. In this case, you have to add End If in line 8, at the end of the statement.
NOTICE
If Then Else Statement
With IF..THEN..ELSE structure Excel will execute the block of code if the condition is met and another block of code if the condition is not met. Look at the following example.
Example 3:
1 2 3 4 5 6 7 8 9 10 11 |
Dim myTime As Date Dim timeToDouble As Double myTime = #12:45:12 PM# timeToDouble = TimeValue(myTime) If myTime < 0.5 Then MsgBox ("Good Morning!") Else MsgBox ("Good Afternoon!") End If |
Be cautious when you use the IF..THEN..ELSE statements, because it is very easy to make an error. For example, you cannot write:
1 |
If myTime < 0.5 Then MsgBox ("Good Morning!") |
in one line, but you have to write this statement in two lines instead.
1 2 |
If myTime < 0.5 Then MsgBox ("Good Morning!") |
Otherwise, VBA will display the following error:
If Then ElseIf Else Statement
In the previous lessons, we used an example to display one of the two messages: „Good Morning!” for the times before noon or „Good Afternoon!” for the times after noon. But what if we want to add the third condition to display „Good evening”. In this case, we can use the ELSEIF statement.
Look at the following example:
Example 4:
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Dim myTime As Date Dim timeToDouble As Double myTime = #6:45:12 PM# timeToDouble = TimeValue(myTime) If myTime > 0 And myTime < 0.5 Then MsgBox ("Good Morning!") ElseIf myTime >= 0.5 And myTime < 0.75 Then MsgBox ("Good Afternoon!") Else MsgBox ("Good Evening!") End If |
In the above example, we created three conditions:
- „Good Morning!” for times between 12:00:00 AM and 11:59:59 AM.
- „Good Afternoon!” for times between 12:00:00 PM and 06:00:00 PM.
- „Good Evening!” for times between 06:00:01 PM and 11:59:59 PM.
Select Case Statement
If you have to create a lot of conditional statements, using IF statements is not the best idea. In such a situation, the best way to write a conditional statement will be using the SELECT..CASE construct.
Example 5:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
Dim dayOfWeek As Byte dayOfWeek = Weekday("12/22/2014") Select Case dayOfWeek Case 1 MsgBox ("Sunday") Case 2 MsgBox ("Monday") Case 3 MsgBox ("Tuesday") Case 4 MsgBox ("Wednesday") Case 5 MsgBox ("Thursday") Case 6 MsgBox ("Friday") Case 7 MsgBox ("Saturday") End Select |
dayOfWeek returns the number of days for the selected date. In our case, it will be Monday.
Example 6:
In this example, I used the SELECT..CASE statement to specify the condition for the range between 2 and 6 and for the rest.
1 2 3 4 5 6 7 8 9 10 |
Dim dayOfWeek As Byte dayOfWeek = Weekday("12/22/2014") Select Case dayOfWeek Case 2 To 6 MsgBox ("Workday") Case Else MsgBox ("Weekend") End Select |
If the returned day is between Monday and Friday the macro returns „Workday”, otherwise it returns „Weekend”.