WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
30 years in business
Wise Owl Training
30 years in business
See 479 reviews for our classroom and online training
Conditions in Visual Basic for Applications - IF and SELECT CASE
Part three 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
  3. Using SELECT CASE to Test Conditions in VBA macros (this blog)

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.

Using SELECT CASE to Test Conditions

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.

This blog has 0 threads Add post