Conditions in Visual Basic for Applications - IF and SELECT CASE
Part two of a three-part series of blogs

This part of the Excel VBA training series of blogs shows how to use IF / ELSE / END IF within VBA, and also how to use the alternative SELECT / CASE syntax within macros.

  1. Using Conditions in VBA - IF and SELECT / CASE
  2. Writing IF Conditions in Visual Basic for Applications (this blog)
  3. Using SELECT CASE to Test Conditions in VBA macros

This series of blogs is part of our Excel VBA online tutorial.  Alternatively, why not have a look at our Excel courses or VBA training?

Posted by Andy Brown on 15 November 2011

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Writing IF 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).

The Simplest form - a Single Condition with a Single Statement

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 Statementts

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 and 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).

The conditions above can get quite hard to read, particularly when you've got one condition nested within another - it's often easier to use a SELECT CASE statement, as described in the final part of this blog.

 

  1. Using Conditions in VBA - IF and SELECT / CASE
  2. Writing IF Conditions in Visual Basic for Applications (this blog)
  3. Using SELECT CASE to Test Conditions in VBA macros
This blog has 0 threads Add post