This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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. Using Conditions in VBA - IF and SELECT / CASE Writing IF Conditions in Visual Basic for Applications (this blog) 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?

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.

Using Conditions in VBA - IF and SELECT / CASE
Writing IF Conditions in Visual Basic for Applications (this blog)
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 07 July 2023

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.

Parts of this blog
Using Conditions in VBA - IF and SELECT / CASE Writing IF Conditions in Visual Basic for Applications (this blog) Using SELECT CASE to Test Conditions in VBA macros

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

Writing IF Conditions

The Simplest form - a Single Condition with a Single Statement

Single Condition, Multiple Statementts

Including and ELSE Statement - Two-way Conditions

Multiple Conditions - using ELSEIF

Head office

London

Manchester