The Excel VBA If Then statement lets you run code only when a condition is true, with optional ElseIf and Else branches for other cases. Its core syntax is If condition Then statement for a single line, or a multi-line block ending with End If.
What Is the If Then Statement in VBA?
VBA (Visual Basic for Applications) is the programming language built into Excel. The If Then statement is a conditional branch: it checks a logical expression (something that evaluates to True or False) and decides which code to run next. It is the most basic decision-making tool in VBA, used in nearly every macro that needs to react to cell values, user input, or calculated results.
Unlike Excel’s worksheet IF() function, which returns a value into a cell, VBA’s If Then controls the flow of execution—it can run whole blocks of code, call other procedures, or skip steps entirely.
Single-Line vs. Block Syntax
You can write an If in two ways. The single-line form is compact for one action:
If Range("A1").Value > 10 Then MsgBox "Over 10"
If you need more than one statement, or want an Else branch, use the block form:
If Range("A1").Value > 10 Then
MsgBox "Over 10"
Range("B1").Value = "High"
Else
MsgBox "10 or less"
End If
The block form always ends with End If. The single-line form does not need End If. For readability, even a single action is often written as a block, especially inside longer procedures.
Using ElseIf for Multiple Conditions
When you have more than two possible outcomes, chain ElseIf between If and Else. VBA evaluates the conditions top to bottom and stops at the first True one:
Dim score As Integer
score = Range("A1").Value
If score >= 90 Then
Range("B1").Value = "A"
ElseIf score >= 80 Then
Range("B1").Value = "B"
ElseIf score >= 70 Then
Range("B1").Value = "C"
Else
Range("B1").Value = "F"
End If
This example assumes the score is in A1 and writes the letter grade to B1. The order matters: if you put score >= 80 first, a score of 95 would match that branch and never reach the score >= 90 line.
Comparing Values and Using Logical Operators
Conditions can compare numbers, strings, dates, or object properties. Common comparison operators are =, <> (not equal), >, <, >=, and <=. For text, VBA is case-insensitive by default, so "apple" = "APPLE" is True unless you use Option Compare Binary at the top of the module.
Combine multiple conditions with And, Or, and Not:
If Range("A1").Value > 0 And Range("A1").Value <= 100 Then
MsgBox "Between 1 and 100"
End If
For checking if a cell is empty, use IsEmpty(Range("A1")) or compare to an empty string: Range("A1").Value = "". To check if a cell contains an error, use IsError(Range("A1")).
Common Mistakes and How to Avoid Them
Beginners often trip on a few points. First, forgetting End If in a block causes a compile error. Second, using = in a condition when you meant = for assignment is fine, but mixing them can confuse—inside an If, = is comparison, not assignment. Third, comparing a range object directly instead of its .Value property: If Range("A1") = 5 actually works because VBA implicitly reads the default property, but it is clearer to write Range("A1").Value.
Another frequent issue is using ElseIf as two words. It must be one word. Also, in a single-line If, you can only have one statement after Then; to run multiple, use colons (rarely recommended) or switch to a block.
Practical Examples: Loops and Cell Formatting
The If Then statement becomes powerful inside a For Each loop. For instance, to highlight all cells in column A that are negative:
Dim cell As Range
For Each cell In Range("A1:A10")
If cell.Value < 0 Then
cell.Interior.Color = RGB(255, 0, 0) ' red background
End If
Next cell
This loops through each cell in A1:A10, checks its value, and colors it red if negative. Note that macros cannot be undone with Ctrl+Z, so save a copy of the workbook before running this.
Another common use is validating input before proceeding:
If IsNumeric(Range("B2").Value) And Range("B2").Value > 0 Then
MsgBox "Valid number"
Else
MsgBox "Enter a positive number"
End If
If Then vs. Select Case
For many conditions, Select Case is cleaner than a long chain of ElseIf. Use Select Case when you are testing the same expression against several possible values or ranges:
Select Case Range("A1").Value
Case 1 To 10
MsgBox "Low"
Case 11 To 20
MsgBox "Medium"
Case Else
MsgBox "High or invalid"
End Select
Select Case evaluates the expression once, which can be slightly faster, and is easier to read. However, If Then is more flexible for complex conditions that mix different variables or use And/Or across different expressions.
Frequently Asked Questions
Can I use Excel worksheet functions like IF inside VBA? Yes, you can call worksheet functions via Application.WorksheetFunction.IF but it is awkward. Native VBA If Then is the standard way.
How do I write an If statement that checks if a cell contains text? Use If TypeName(Range("A1").Value) = "String" Then or check IsNumeric for the opposite. Simpler: If Range("A1").Value <> "" Then checks not empty.
What is the difference between If and IIf? IIf is a function that returns a value, like IIf(condition, truePart, falsePart). It always evaluates both parts, so it can cause errors if one part is invalid. If Then only runs the selected branch.
Can I use multiple lines after Then in a single-line If? No, a single-line If allows only one statement (or several separated by colons). Use a block with End If for multiple lines.
Why does my If statement not work when comparing dates? Dates are stored as numbers in VBA. Compare date values directly, but be careful with time components. Use DateValue() to strip times: If DateValue(Range("A1").Value) = Date Then checks if the cell is today.
How do I exit a procedure early based on an If condition? Use Exit Sub inside the If block:
If Range("A1").Value = "" Then
MsgBox "No data"
Exit Sub
End If
This stops the macro immediately, which is useful for validation.
Related
- Excel Spreadsheet to Track Job Applications? Yes, Here’s How
- Excel Spreadsheet for Inventory Tracking: How to Build One
- Employee Schedule Spreadsheet: How to Build One
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel Macro Recorder: View Its Code in the VBA EditorYes. In Excel for Microsoft 365 on Windows, choose Developer > Macros, select the recorded macro, and click…
- VBA Pivot Table References: Objects, Fields, and ExamplesIn Excel VBA, reference a PivotTable through its worksheet, using Worksheets("SheetName").PivotTables("PivotTableName"); then use that PivotTable object to access…
- Excel VBA Get Last Row with Data in ColumnThe fastest way to get the last row with data in a specific column using Excel VBA is…
- Excel VBA UserForm Activate vs InitializeIn Excel VBA, use UserForm_Initialize to set up a form when it is loaded, and use UserForm_Activate for…
- Excel VBA Uppercase: UCase, Macros, and Cell OptionsYes. In Excel VBA, use UCase$ to return a text string in uppercase; to change worksheet cells, assign…
- Excel VBA Dim: How to Declare VariablesIn Excel VBA, Dim declares a variable: a named place for a value your code can use, such…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.