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:

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:

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:

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

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

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:

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 a situation, the best way to write a conditional statement will be using the SELECT..CASE construct.

Example 5:

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.

If the returned day is between Monday and Friday the macro returns „Workday”, otherwise it returns „Weekend”.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.