Excel: Conditional statements

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:

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 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:

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

NOTICE:

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.

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:

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

CAUTION

Be cautious when you use the IF..THEN..ELSE statements, because there is very easy to make an error. For example, you cannot write

If myTime < 0.5 Then MsgBox ("Good Morning!")

in one line, but you have to write this statement in two lines instead. 

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:

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:

  1. „Good Morning!” for times between 12:00:00 AM and 11:59:59 AM.
  2. „Good Afternoon!” for times between 12:00:00 PM and 06:00:00 PM.
  3. „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 situation, the best way to write a conditional statement will be using the SELECT..CASE construct. 

Example 5:

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 the day 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.

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”.