• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • SALE 20% Off All Courses
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Logout
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

VBA IF THEN ELSE

You are here: Home / Excel VBA / VBA IF THEN ELSE
if then else
August 3, 2017 by Philip Treacy

The VBA IF THEN ELSE statement lets you test a condition, and then carry out other commands if the test result is true (or equivalent to true - more on that later).

Excel has an IF function that you use in the worksheet, but this is a different beast and does not work the same way as the VBA IF THEN ELSE, although the idea is the same.

An example:

  If Profit > 100 Then

    Debug.Print "Good job everyone"

  End If

Or put more abstractly:

  If Condition_1 is True Then

    Carry out these commands

  End If

There are several forms the IF statement can take, and we'll look at each of these, but at its most basic it can simply be one line:

Sub if_then()

    If 1 > 0 Then Debug.Print "1 is greater than 0"
    
End Sub

You can use this form if you only have one line of code to carry out if the test is true.

If you have more then one line of code (a block), you need to use End If to indicate where the block of code finishes. You can also use this form with a single line of code in the block.

  If Profit > 100 Then

    Debug.Print "Good"
    Debug.Print "job"
    Debug.Print "everyone"

  End If

If the test condition evaluates to false then you can carry out other commands by specifying an Else clause:

  If Profit > 150 Then

    Debug.Print "Excellent, bonuses all round"

  Else

    Debug.Print "We need to increase sales"

  End If

If you have more than one test you want to carry out, you can test these one after the other using an ElseIf clause:

  If Profit > 150 Then

    Debug.Print "Excellent, bonuses all round"

  ElseIf Profit > 100 Then

    Debug.Print "Good job everyone"

  Else

    Debug.Print "We need to increase sales"

  End If

Only one block of code is executed here. If Profit is 200, then the first test Profit > 150 is true and we see the message "Excellent, bonuses all round", and code execution then jumps to the End If and stops.

If Profit is 120, the second test is true Profit > 100 and we only see the message "Good job everyone".

If you have multiple Else If clauses then only one should be executed.

When you start doing this kind of thing, the order of the tests is critical to get the code to work correctly. For example if I wrote this:

  If Profit > 100 Then

    Debug.Print "Good job everyone"

  ElseIf Profit > 150 Then

    Debug.Print "Excellent, bonuses all round"

  Else

    Debug.Print "We need to increase sales"

  End If

and Profit has a value of 200, then the first test is true Profit > 100 and I get the message, Good job everyone being printed. Really what I want is to see Excellent, bonuses all round.

You must make sure when writing these statements, that the tests will evaluate in the order that you want.

You can nest your IF statements in any way you want. You can have a straightforward IF, with another IF or an IF ELSE, IF ELSEIF ELSE etc inside each block.

Sub if_then_else_nested()

    Dim mynum As Long

    ' Change the value of mynum
    mynum = -16

    If mynum > 0 Then
    
        Debug.Print "Positive"
        
        If mynum > 5 Then
        
            Debug.Print "Greater than 5"
            
        Else
        
            Debug.Print "Between 0 and 5"
            
        End If
        
    Else
    
        Debug.Print "Negative"
        
        If mynum < -10 Then
        
            Debug.Print "Less than -10"
            
        ElseIf mynum < -5 Then
        
            Debug.Print "Between -10 and -5"
        
        Else
        
            Debug.Print "Between -5 and 0"
        
        End If
   
    End If
    
End Sub

Implicit Test

I keep writing that tests must evaluate to true, and by that I mean the result of the test must either be a numerical equivalent of true, or the actual Boolean value True.

In VBA, any number that is not zero is deemed to be the equivalent of True, 0 is deemed to be False.

Numbers

We may want to check if a variable has a specific value e.g.

  If Total > 150 Then

    [  Do Something ]

  End If

But let's say we just care about whether Total is greater than 0, and we know that Total can't be a negative value, we could just write

  If Total Then

    [  Do Something ]

  End If

The test is implied. We don't need to write

If Total > 0
. Aas long as Total contains a number, the result of the test is deemed to be true.

Booleans

It's the same for Boolean variables. If we have a Boolean variable, Status, we can test if it is True like this:

  If Status Then

    [  Do Something ]

  End If

Strings

You can't test a string in quite the same way because using an implicit test on an empty string generates an error.

However, you can still implicitly test if the string contains any characters by checking it's length. Essentially this is testing for a positive number, but is handy to know for when you are working with strings.

  If Len(Answer) Then

    [  Do Something ]

  End If

As long as Answer contains at least one character, the test evaluates to true.

IF GOTO

Another way to use IF is with a GoTo statement. GoTo tells the code to go to the named Label and continue executing code from there.

In this example I have a label called Handler. If my test evaluates to true then I want the code to continue running from Handler:

If the test is false, then code execution just continues on the line after the IF statement.

Because code is run sequentially, one line at a time, if we use a GoTo, we have to use an Exit Sub statement to terminate the code. If we don't, VBA will just keep going down the lines of code until it gets to Handler: and then run the code in there.

We don't want that of course so don't forget to use Exit Sub in situations like this.

    ' Change this test to evaluate to false
    ' e.g. 1 < 0
    If 1 > 0 Then GoTo Handler
    
    Debug.Print "Not in Handler code"
    
    ' Exit Sub is required to prevent 
    ' VBA executing the Handler code
    ' Comment out the next line
    ' to see what happens
    Exit Sub
    
Handler:
    Debug.Print ("In the handler")

Download Sample Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

You can download a sample workbook with lots of examples of using IF THEN ELSE.

if then else

More Excel VBA Posts

Display All Matches from Search in Userform ListBox

Display All Matches from Search in Userform ListBox

Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.
animating excel charts

Animating Excel Charts

Use animation correctly to enhance the story your data is telling. Don't animate your chart just for some eye candy. Sample code and workbook to download.
dynamic data validation lists in userforms

Dynamic Data Validation Lists in Userforms

Data validation lists using the same source that are dynamically modified to prevent the same choice being made in each list.
show report filter pages for power pivot pivottables

Show Report Filter Pages for Power Pivot PivotTables

PivotTables created from Power Pivot can't use the 'Show Report Filter Pages' option. But this piece of VBA allows you to do just that.
charting real time data in excel

Charting Real Time Data in Excel

Receive data in real time and chart the data as it arrives. Can be used to chart things like stock prices or sensor readings. Sample code and workbook
select multiple items from drop down data validation list

Select Multiple Items from Drop Down (Data Validation) List

Choose multiple items from a data validation (drop down) list and store them all in the same cell. Sample workbook with working VBA.
Excel Calendar (Date Picker) to Use in Worksheets and Userforms

Multi-Language Excel Calendar (Date Picker) for Worksheets and Userforms

Easy to use, highly customizable and multi-language. This date picker is implemented as a userform that is simple to integrate into your workbook.
automating and emailing pivot table reports

Automating and Emailing Pivot Table Reports

Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
search for data with userform

Searching for Data With a User Form

Search a list of records (like a table) using a user form, and then populate the fields of the search form when the record is found.
Checking values in range objects with vba

Checking Values in Range Objects With VBA

Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.




Category: Excel VBA
Previous Post:Excel Convert Dates to Fiscal Quarters and Years
Next Post:VBA SELECT CASEVBA Select Case

Reader Interactions

Comments

  1. Joe Meyer

    August 3, 2017 at 3:56 am

    I thought that VBA interprets any non-zero value as true, whether it’s positive or negative, and that only zero is interpreted as false.

    Reply
    • Philip Treacy

      August 3, 2017 at 10:22 am

      Hi Joe,

      Looks like you are right, this is the way the IF function works in the sheet too. That’s bizarre – when I was testing my code yesterday I’m sure negative numbers were giving me false, but today they aren’t. I don’t know what’s going on there!!

      Will amend accordingly.

      Thanks

      Phil

      Reply
  2. Martin Nolan

    August 3, 2017 at 2:26 am

    Hi Philip,

    There appears to be a misprint In your paragraph about implicit tests. In VBA a positive number is actually the equivalent of False.

    Reply
    • Philip Treacy

      August 3, 2017 at 9:26 am

      Hi Martin,

      A positive number is the equivalent of True in VBA. Try this

      Sub mytest()
      
        If 1 Then Debug.Print "Hello World"
      
      End Sub
      

      Regards

      Phil

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Course Sale

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
trustpilot excellent rating
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.