An Excel macro uses VBA’s If...Then...Else statement to make a decision and run different instructions depending on whether a condition is true or false. The basic pattern is If condition Then, followed by the true-case instructions, optionally Else and the false-case instructions, and finally End If.
How the VBA If statement works
A macro is a sequence of instructions, and an If statement lets that sequence branch. VBA evaluates a condition as either True or False, then runs the matching block. For example, a condition might compare a number with a threshold, check whether a cell is empty, or test whether a text value matches a particular word.
The standard multiline structure is:
If condition Then
' Instructions when the condition is True
Else
' Instructions when the condition is False
End If
Then marks the start of the true branch, while End If closes the block. The Else branch is optional. If it is omitted and the condition is False, VBA skips the instructions inside the If block and continues after End If. In VBA, comparison operators include =, <> (not equal), >, <, >=, and <=.
A basic Excel macro example
Suppose a worksheet has a score in cell B2 and you want the macro to write Pass or Review in C2. The following example assumes the worksheet is named Sheet1 and B2 contains a number. It declares its variables, refers to the worksheet explicitly, and handles both possible outcomes:
Option Explicit
Sub SetPassStatus()
Dim score As Double
Dim statusCell As Range
' Read the score and select the destination cell.
score = ThisWorkbook.Worksheets("Sheet1").Range("B2").Value
Set statusCell = ThisWorkbook.Worksheets("Sheet1").Range("C2")
If score >= 70 Then
statusCell.Value = "Pass"
Else
statusCell.Value = "Review"
End If
End Sub
If B2 contains 82, the condition score >= 70 is True and C2 receives Pass. If B2 contains 65, the condition is False and C2 receives Review. The value 70 itself passes because the comparison uses greater than or equal to, rather than just greater than.
This example expects B2 to hold a numeric value. If it is blank or contains text, assigning it to a Double variable may cause a type mismatch. For a macro that processes user-entered data, validate the value first, as shown in the section on common problems.
Use ElseIf for multiple outcomes
When a decision has more than two outcomes, use ElseIf to check additional conditions in order. VBA runs the instructions belonging to the first condition that evaluates to True, then skips the remaining branches. A final Else can provide a default outcome when none of the tests match.
For example, with a score in B2 and a result in C2 on Sheet1, this macro assigns one of three labels. It assumes B2 contains a number:
Option Explicit
Sub SetGradeLabel()
Dim score As Double
Dim resultCell As Range
' Read the score and set the output location.
score = ThisWorkbook.Worksheets("Sheet1").Range("B2").Value
Set resultCell = ThisWorkbook.Worksheets("Sheet1").Range("C2")
If score >= 90 Then
resultCell.Value = "Excellent"
ElseIf score >= 70 Then
resultCell.Value = "Pass"
Else
resultCell.Value = "Review"
End If
End Sub
The order matters. A score of 95 also satisfies score >= 70, but VBA reaches that test only if the first condition was False. Since 95 meets the first test, it receives Excellent. Put the most specific or highest-priority condition first, and check that the ranges cover the outcomes you intend. If no final Else is present, values that meet none of the conditions simply receive no new assignment from the block.
Combine conditions with And and Or
Use And when all the stated requirements must be true, and Or when any one of them is enough. For example, if B2 contains a score and C2 contains an attendance percentage on Sheet1, the following macro assigns a result only when both thresholds are met:
Option Explicit
Sub CheckEligibility()
Dim score As Double
Dim attendance As Double
Dim resultCell As Range
' Read both inputs and choose the result cell.
score = ThisWorkbook.Worksheets("Sheet1").Range("B2").Value
attendance = ThisWorkbook.Worksheets("Sheet1").Range("C2").Value
Set resultCell = ThisWorkbook.Worksheets("Sheet1").Range("D2")
If score >= 70 And attendance >= 80 Then
resultCell.Value = "Eligible"
Else
resultCell.Value = "Not eligible"
End If
End Sub
The example assumes both input cells contain numbers. Parentheses can make compound tests easier to read, especially when mixing And and Or. VBA evaluates logical expressions with both operators; unlike some programming languages, And and Or do not short-circuit. That means you should not rely on the first part of a condition to prevent an invalid operation in its second part. Use a separate nested If when the second test is safe only after the first one succeeds.
Check cell contents before using them
Macros often encounter blank cells, text in a numeric column, or other unexpected input. A simple validation check can stop the macro from treating unsuitable content as a number. With a value in B2 and a message in C2 on Sheet1, this example tests the input before converting it:
Option Explicit
Sub CheckAmountInput()
Dim amountValue As Variant
Dim statusCell As Range
' Read the input without assuming it is numeric.
amountValue = ThisWorkbook.Worksheets("Sheet1").Range("B2").Value
Set statusCell = ThisWorkbook.Worksheets("Sheet1").Range("C2")
If IsNumeric(amountValue) Then
If CDbl(amountValue) >= 1000 Then
statusCell.Value = "At least 1000"
Else
statusCell.Value = "Below 1000"
End If
Else
statusCell.Value = "Enter a number"
End If
End Sub
The outer If checks whether the value is numeric; the inner If compares the converted number with 1000. This structure avoids putting the conversion and comparison into a single And expression, which would not safely guard the conversion in VBA. In a real workbook, consider whether values such as dates or numeric-looking text should count as valid input; IsNumeric answers whether VBA can interpret a value as numeric, not whether it meets every business rule.
Add and run a macro in Excel
In Excel for Microsoft 365 on Windows, show the Developer tab if it is not already visible by selecting File > Options > Customize Ribbon and checking Developer in the main tabs list. Then use Developer > Visual Basic to open the VBA editor. In the editor, select Insert > Module, paste a complete macro into the module, and save the workbook as an Excel Macro-Enabled Workbook (.xlsm).
- Save a copy of the workbook before running a macro. VBA actions cannot be reversed with Ctrl+Z.
- In the VBA editor, check that the worksheet name in the code matches the tab name exactly, including spaces.
- Return to Excel and choose Developer > Macros, select the macro name, and click Run. Alt+F8 opens the Macro dialog on Windows.
- Check the output cells and any assumptions about input values before using the macro on a larger dataset.
On Excel for Mac, use Tools > Macro > Visual Basic Editor to open the VBA editor; the exact menu presentation can vary slightly by release. The Option+F11 shortcut may open the editor on a Mac, sometimes with the Fn key depending on keyboard settings. Excel for the web does not run VBA macros, so open the workbook in the desktop app to use this code. Do not enable macros in a file unless you trust its source; macros can make changes to a workbook and may contain harmful instructions.
VBA If statements versus worksheet IF
VBA’s If statement and Excel’s worksheet IF function serve related but different purposes. A worksheet formula calculates a result in a cell and updates when its inputs change. A VBA If statement controls which instructions a macro executes when the macro runs. Use a formula when the desired result belongs in a cell and should recalculate automatically; use VBA when you need a macro to perform an action, such as copying values, changing several cells, or choosing which operation to run.
A common misconception is that typing If... into a worksheet cell creates a macro. It does not: worksheet cells use formulas, while VBA code belongs in a VBA module or another appropriate code location. Another practical difference is that a macro generally runs only when triggered or called by other VBA code; it does not automatically rerun whenever a referenced cell changes unless you deliberately use an event procedure.
Common mistakes and safer habits
- Missing End If: Every multiline If block needs a matching
End If. Nested If statements each need their own closing line. - Using the wrong worksheet: An unqualified
Range("B2")can refer to the active sheet. Qualify the range with a workbook and worksheet when the macro must use a specific location. - Unexpected text or blanks: Match variable types to the data and validate input before converting it to a number. A blank or text value may not fit a numeric variable.
- Overlapping ElseIf conditions: Since the first true branch wins, arrange tests in the intended priority order and test boundary values such as 69, 70, and 90.
- Running on production data too soon: Save a separate copy first, then test representative inputs. Ctrl+Z does not undo changes made by a macro.
Using Option Explicit at the top of a module requires variables to be declared and helps catch misspelled variable names. It does not make a macro risk-free, but it makes some coding mistakes easier to identify. For larger procedures, keep each decision focused and give variables descriptive names so the conditions remain understandable later.
Frequently Asked Questions
Can an Excel macro have an If statement without Else?
Yes. Use If condition Then and close the block with End If. If the condition is False, VBA skips the block and continues with the next instruction.
What does ElseIf do in VBA?
ElseIf adds another condition to an If structure. VBA checks it only after earlier conditions are False, and runs the first matching branch.
Can I put an If statement on one line?
Yes. VBA supports a single-line form such as If score >= 70 Then resultCell.Value = "Pass". For multiple instructions or a more complex decision, the multiline form is easier to read and maintain.
Why does my macro say Type mismatch?
The code may be assigning text, a blank, or another incompatible value to a variable declared with a numeric type. Check the cell contents and validate them before conversion, or use a suitable type such as Variant while checking the input.
Can Excel for the web run a VBA If macro?
No. Excel for the web does not run VBA macros. Open the workbook in Excel for Windows or Mac to run VBA; the If statement itself is part of VBA, not a worksheet formula.
Related
- Remove Duplicates in Power Query: A Practical Guide
- Power Query Basics: Import, Clean, and Transform Data
- Append Queries in Power Query: How to Combine Tables
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Close MsgBox VBA: Reliable Ways to Dismiss a DialogTo close a VBA MsgBox, click one of its buttons or use a keyboard action the dialog supports;…
- Excel VBA: Match a Trendline Label to Its Line ColorYes. In Excel VBA, set the trendline label’s font color to the trendline’s line color: use Trendline.DataLabel.Font.Color and…
- VBA Function Statement: Syntax, Scope, and ExamplesThe VBA Function statement declares a procedure that performs a task and returns a value. You can call…
- Excel VBA Input Box Date Picker: What Actually WorksExcel’s VBA InputBox does not include a calendar or date-picker control: it asks the user to type a…
- VBA Find the Maximum Value in an Excel Column: How-ToTo find the largest numeric value in an Excel column with VBA, use the worksheet function Max on…
- Excel VBA Sub vs Function: Key Differences and ExamplesIn Excel VBA, a Sub performs actions but does not return a value as its result; a Function…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.