1800-222-WTF

Excel & VBA

24 Feb 2018

How to Use Conditional Statements

/
Posted By
/
Comments0

One of the most fundamental aspects in programming (not just VBA) is the ability to enable your code to make decisions. These decisions are made by conditional statements, and can be used to trigger different actions as a result. There are 2 basic conditional statements available in VBA: If StatementsΒ and Select Case.

If Statements

Single Condition

The basic structure of an IF statement is shown below.

This has a single condition that can result in 2 outcomes. If the condition is evaluated to be true, then the enclosed code will be executed. If, the result is false then no action is taken. Below is an example, can you figure out what the code does?

It’s a pretty simple sub-routine, but the CONDITION is to check if Today’s date matches the date stored in the variable MyBirthday (Learn More about Variables Here). If the condition evaluates to true, then the user is wished a Happy Birthday! If the condition evaluates to false (no birthday cake for you!), nothing will happen.

If… Then… Else

But what if we want to take a different action if the condition is not true? That’s where the If… Then… Else… structure comes into play.

Let’s break down how this condition is evaluated. Line 1 has a condition you wish to check, if the result is true, Code A (Line 2) will be executed. If the result is false, then Code B (Line 5) will be executed. Here’s an example, can you guess what the result will be?

That’s right! The student would have passed his test! The condition being checked is on Line 6, seeing as 78 is greater than 71 the first statement would have been executed.

ElseIf

So lets consider the example above, the result can either be a Pass or a Fail, but what if you wanted to award a range of grades (A*, A, B, C etc). It is possible to evaluate multiple conditions through use of ElseIf statements.

Can you tell which grade would have been awarded to the student? In this instance they would have got a C!

Select Case Blocks

Where the same variable is compared against a range of different criteria (or Cases), a more elegant solution if often the Select Case statement.

Here, VBA checks the value of the variable against each Case expression in turn and executes the statements that follow the first expression that matches. If no match is found, then the statements following the Case Else keyword is executed. It should be noted that the Case Else condition is optional.

Each Case expression may be a single value (such as Case 100) or a range of values (Case 90 to 100). You can get fancy and also incorporate Comparison Operators such as (Is > 50) or any combination separated by commas (Case 92, 50 to 65).

The code above achieves the same result as that shown with the If.. Then… Else block above.

 

That awkward moment when you're wearing Nike's...and you can't do it!

Leave a Reply