Beyond Recording

Recording captures actions — clicking, typing, navigating menus. But some things don't exist in Excel at all, which means they can't be recorded. They have to be typed. This module covers the two most important concepts you'll add by hand: If statements that let the macro make decisions, and variables that let it work with values that change each time it runs.

The workflow from here forward is always the same: record the parts you can, open the code, and add the typed logic around it. You're not writing everything from scratch — you're extending what the recorder gives you.

If Statements

An If statement lets your macro check a condition and do different things depending on whether that condition is true or false. The recorder can capture individual actions but it can't decide between them — that decision has to be typed. The key words are If, Then, Else, and End If.

Before writing the code, think through the logic in plain English first — just like writing pseudocode for an Excel formula. If you get the logic right in words, translating it to VBA is straightforward.

IF condition is true THEN
    do this
ELSE
    do that
END IF

If condition Then
    ' code for true case
Else
    ' code for false case
End If

This example from the Macro Demo toggles notes on and off. The recorded code gave us both actions — turning notes on and turning them off. The If statement is the logic that decides which one to run:

Sub DisplayNotes()

    If ActiveWorkbook.ShowNotesIndicators = True Then
        ActiveWorkbook.ShowNotesIndicators = False
    Else
        ActiveWorkbook.ShowNotesIndicators = True
    End If

End Sub

The Guard Check Pattern

A guard check is an If statement placed at the top of a macro to verify that the data you need actually exists before the macro tries to do anything with it. If the table is empty, it shows a message and the macro stops naturally. If there is data, the macro continues through the Else branch and runs the rest of the code.

IF first data cell is empty THEN
    DISPLAY message — no data found
ELSE
    run the rest of the macro
END IF

Here is what the guard check looks like in VBA. Notice that no special stop command is needed — when the If branch runs, the Else branch is skipped automatically:

Sheets("Applicant Information").Select
Range("A2").Select

If ActiveCell = "" Then
    MsgBox "No applicants found. Please enter data first."
Else
    ' rest of macro goes here
End If

Variables

A variable stores a value that can change each time the macro runs. Instead of hardcoding a number into the code, you put the value in a variable — and the macro works correctly whether that number is 250 or 275 or anything else.

There are three steps every time you use a variable:

Step 1 — Declare it
    Dim VariableName As DataType

Step 2 — Populate it
    VariableName = InputBox("Prompt for user")

Step 3 — Use it
    Replace the hardcoded value with the variable name

This example from the Macro Demo shows all three steps. The recorder hardcoded 250 into the Goal Seek. The variable replaces that hardcoded value so the user can enter a different number each time:

Sub GoalSeek_Admit()

    ' Step 1 — Declare
    Dim NumberToAdmit As Integer

    ' Step 2 — Populate via InputBox
    NumberToAdmit = InputBox("How many students do you want to admit?")

    ' Step 3 — Use (replaces the hardcoded 250 the recorder gave us)
    Range("GPR_Accept").GoalSeek Goal:=NumberToAdmit, _
        ChangingCell:=Range("Criteria_GPR")

End Sub

InputBox

An InputBox pauses the macro and shows a dialog where the user can type something. Whatever they type is stored in the variable on the left side of the equals sign. This is how a macro becomes adaptable — you ask for the value at runtime instead of hardcoding it. InputBox is always typed, never recorded.

VariableName = InputBox("Message to display to the user")

This example asks how many students to admit and stores the answer:

Dim NumberToAdmit As Integer

NumberToAdmit = InputBox("How many students do you want to admit?")

Quick Check

If ActiveCell = "" Then
    MsgBox "No applicants found."
Else
    ' rest of macro
End If

Easy Wins

Exercise 1 — Build the Toggle Macro Guided

Build the DisplayNotes toggle macro from the video. Record both actions first, then add the If/Else around the recorded code.

' IF notes are currently on THEN
'     turn them off
' ELSE
'     turn them on
' END IF

Sub DisplayNotes()

    If ActiveWorkbook.ShowNotesIndicators = True Then
        ActiveWorkbook.ShowNotesIndicators = False
    Else
        ActiveWorkbook.ShowNotesIndicators = True
    End If

End Sub

Exercise 2 — Add a Variable to Goal Seek

Sub GoalSeek_Recorded()
    Range("B1").GoalSeek Goal:=250, ChangingCell:=Range("A1")
End Sub

Sub GoalSeek_WithVariable()

    Dim NumberToAdmit As Integer

    NumberToAdmit = InputBox("How many students do you want to admit?")

    Range("B1").GoalSeek Goal:=NumberToAdmit, ChangingCell:=Range("A1")

End Sub

Practice Problem

Add a Guard Check and InputBox

Using the Aggie Advisors data, write a macro that checks whether the Applicant Information sheet has any records before doing anything else. If there is data, ask the user for a group number and confirm it.

What your macro needs to do:

• Navigate to the Applicant Information sheet and select cell A2
• Check if A2 is empty using an If statement
• If empty: display "No applicants found." — the macro ends here naturally
• If not empty (Else branch): ask for a group number via InputBox, store it in an Integer variable, and display: "Group [X] is ready to process" where X is the number entered

Test it both ways:

1. Run it with data in A2 — you should get the InputBox and then the message
2. Clear cell A2, run it again — you should get "No applicants found."

Expected results:

• With data: InputBox appears, then "Group [X] is ready to process"
• Without data: "No applicants found." and macro ends

Challenge

These exercises are perfect preparation for the Sample Exam, which prepares you for the Macro Project Exam itself. Exam format may vary by semester. Note that the exam questions will be more comprehensive than this single Challenge; this is just the first step to help build your foundation.