This page has 0 threads | Add post

Wise Owl guides and tutorials

Using Conditions in VBA - IF and SELECT / CASE

Written by Andy Brown

In this tutorial

What are Conditions?
IF Statements - Single Conditions
Single Condition, Multiple Statements
Including an ELSE Statement - Two-way Conditions
Multiple Conditions - using ELSEIF
SELECT CASE Statements

What are Conditions?

At the heart of programming is the ability to test a condition. You can do this in Excel with a simple IF function:

A simple IF function, testing whether Olly the Owl is underpaid or not

In Excel VBA you also write conditions using the word IF, but the syntax is more flexible and is - usually - split over different lines. This tutorial shows how to use the IF / ELSE / END IF syntax followed by the alternative SELECT CASE syntax.

For all but the simplest conditions the SELECT CASE format will give code which is easier to write, comment and understand.

Let's start, then, with the simple IF condition!

IF Statements - Single Conditions

The IF statement tests whether something is true or not, and has various different forms according to how many alternatives there are.

All of the examples below show how to display different messages according to the day of the week, using the Weekday function to return a number from 1 (Sunday) through to 7 (Saturday).

If we want to test if today is Friday, you can use the simplest possible IF statement:

Sub TestDay()

'if it's Friday, display message

If Weekday(Date) = vbFriday Then MsgBox "Hooray - it's Friday!"

End Sub

This form only works when you are only doing one thing if the condition is true.

Single Condition, Multiple Statements

You might be better advised to ignore the syntax above (even though it does work for single statements) and use the more flexible structure below:

Sub TestDay()

'if it's Friday, display message

If Weekday(Date) = vbFriday Then

'although there's only one statement here, we could put others

MsgBox "Hooray - it's Friday!"

End If

'anything you put here will run whether the condition was true or not

End Sub

The advantage of the above syntax is that it's easier to read, and easier to add more statements within the condition.

You don't have to use the indentation shown, but if you do it will make your code much easier to read!

Including an ELSE Statement - Two-way Conditions

If you want to test two possible conditions, use an ELSE statement:

Sub TestDay()

'if it's Friday, display message

If Weekday(Date) = vbFriday Then

'VBA will run these statements if the condition is true

MsgBox "Hooray - it's Friday!"

Else

'VBA will run these statements if the condition is false

MsgBox "Sorry - still not Friday"

End If

End Sub

Multiple Conditions - using ELSEIF

You can test as many conditons as you like by adding ELSEIF clauses - here's one for the various parts of the week:

Sub TestDay()

'if it's Friday, display message

If Weekday(Date) = vbFriday Then

'it's the end of the week

MsgBox "Hooray - it's Friday!"

ElseIf Weekday(Date) = vbSunday Or _

Weekday(Date) = vbSaturday Then

'it's the weekend

MsgBox "Yeah! It's the weekend"

ElseIf Weekday(Date) = vbMonday Then

'it's Monday ...

MsgBox "Sorry - it's Monday"

Else

'it must be mid-week

MsgBox "Not long till Friday"

End If

End Sub

Note that you don't have to have an ELSE clause (although it's good programming practice to do so).

SELECT CASE Statements

The conditions above can get quite hard to read. Rather than using IF statements, it's often clearer to use SELECT CASE instead (called SWITCH in some other languages). This tests the value of a variable or expression, then reacts differently for different values of it. For example:

Sub TestDay()

'display different messages for different days of the week

Select Case Weekday(Date)

Case vbFriday

'it's the end of the week

MsgBox "Hooray - it's Friday!"

Case vbSunday, vbSaturday

'it's the weekend

MsgBox "Yeah! It's the weekend"

Case vbMonday

'it's Monday ...

MsgBox "Sorry - it's Monday"

Case Else

'it must be mid-week

MsgBox "Not long till Friday"

End Select

End Sub

In the above macro, we evaluate the day of the week using Weekday(Date), then test this against various possible day numbers.

You don't need a Case Else statement, although it's good practice always to include one. Often it's a good idea to report an error in the Case Else clause, since if your code reaches it you must have failed to include all possible eventualities.

Previous
tutorial

Object-oriented programming

Previous
part

Top of page

Next
part

Next
tutorial

Loops

You can learn more about this topic on the following Wise Owl courses:

This page has 0 threads

Add a new post

Online tutorials

Tutorials on VBA

Testing conditions

Using Conditions in VBA - IF and SELECT / CASE

What are Conditions?

IF Statements - Single Conditions

Single Condition, Multiple Statements

Including an ELSE Statement - Two-way Conditions

Multiple Conditions - using ELSEIF

SELECT CASE Statements

Head office

London

Manchester