For IndividualsFor Analysts & FinanceFor Students & LearnersFor Teams & Automation
MENU
=ARTICLES

VBA editor basics: modules, Subs, and the Immediate window

VBA.A2 ALT + F11 · VBE + IMMEDIATE
VBA editor basics: modules, Subs, and the Immediate window — Excel example preview

Navigating the Visual Basic Editor (Alt+F11): Project Explorer, Standard Modules, Code Window, and the Ctrl+G Immediate Window.

=YOUTUBE

Watch alongside this article

Public YouTube resources matched to this article's topic, functions, and practice goal.

video / Advanced

The Ultimate Excel Advanced Filters In VBA [Full Training Course]

Excel For Freelancers

Open on YouTube (opens in a new tab)
Excel For Freelancers

Loading preview…

Once you’ve recorded a macro or two, the next step is understanding the editor where that code actually lives. It looks intimidating at first — a grey window full of panes — but there are really only a handful of things you need to know to get around.

Opening the editor

Press Alt+F11 from anywhere in Excel. This opens the Visual Basic Editor (VBE) in its own window, separate from the worksheet. Alt+F11 again (or clicking back into the Excel window) switches you back.

The Project Explorer

If you don’t see it, press Ctrl+R. This is the tree view on the left showing every open workbook as a “VBAProject,” and inside each one:

  • Microsoft Excel Objects — ThisWorkbook and one entry per worksheet. Code placed here is tied to that workbook or sheet.
  • Modules — plain code containers you add yourself. Most regular macros live here.
  • Class Modules and Forms — more advanced containers you’ll meet later, for custom objects and dialog boxes.

Inserting a Module

Right-click the VBAProject for your workbook in the Project Explorer, choose Insert → Module, and a blank code window opens. This is where you’ll write most general-purpose macros — anything that isn’t reacting to a specific workbook or worksheet event.

Sub vs Function

Both are procedures — named blocks of code — but they behave differently:

Sub SayHello()
    MsgBox "Hello from VBA"
End Sub

A Sub runs a set of actions and doesn’t hand back a value. It’s what recorded macros always generate, and what you run directly from the Developer tab or the editor.

Function DoubleIt(n As Double) As Double
    DoubleIt = n * 2
End Function

A Function returns a value to whatever called it. Write one in a standard Module and, as long as it’s not marked Private, it can also be called directly from a worksheet cell: =DoubleIt(A1).

Running a Sub from the editor

Click anywhere inside a Sub procedure and press F5 (or Run → Run Sub/UserForm). If your cursor isn’t inside any procedure, VBA asks which one to run. Subs that require arguments can’t be run this way — F5 only works for parameterless Subs, which covers the vast majority of everyday macros.

The Immediate window

Press Ctrl+G (or View → Immediate Window) to open it. It’s a scratchpad for testing things without running a full macro. Type a line and press Enter to execute it immediately:

?Range("A1").Value

The leading ? is shorthand for Print. From inside your code, send values there with Debug.Print:

Debug.Print "Total is: " & total

This is the single most useful debugging habit in VBA — print values as you go instead of guessing what a variable holds.

Declaring variables

Use Dim to declare a variable before using it, with a type:

Dim total As Double
Dim customerName As String
Dim rowCount As Long
Dim isValid As Integer
Dim dataRange As Range
Dim ws As Worksheet

The types you’ll reach for constantly: String for text, Long for whole numbers (prefer it over Integer, which has a much smaller range and no real speed advantage on modern Excel), Double for decimals, and Range and Worksheet for referring to parts of the workbook itself.

Turn on Option Explicit

At the very top of a module, above any Sub, add:

Option Explicit

This forces every variable to be declared with Dim before it’s used. Without it, VBA silently creates a new variable the moment you type a misspelled name — toatl = toatl + 1 runs without complaint, quietly producing the wrong answer, because toatl and total are two different undeclared variables to VBA.

Turn this on permanently: Tools → Options → Editor tab → check “Require Variable Declaration”. Every new module you insert from then on will have Option Explicit added automatically. It won’t retrofit modules you’ve already created, so add it by hand to older code.

Where to put code, and why it matters

The commonest beginner problem is not a syntax error — it is code in the wrong container, which produces the baffling symptom of code that is visibly there and simply never runs.

Container What belongs there
Module General macros, reusable functions, anything you run on demand
ThisWorkbook Workbook_Open, Workbook_BeforeSave — workbook events only
Sheet1, Sheet2… Worksheet_Change and other events for that sheet
Class Module Custom objects — later
UserForm A dialog box and the code behind it

The rule: event procedures must live in the object they belong to. A perfectly written Worksheet_Change sitting in a Module is dead code — VBA never wires it up and gives you no warning. See workbook and worksheet events.

Everything else goes in a Module. If you are unsure, it is a Module.

Note also that sheet modules have two names: the code name (Sheet1) and the tab name (Jan Data), shown as Sheet1 (Jan Data) in the tree. The code name is stable even when a user renames the tab, which makes Sheet1.Range("A1") more robust than Worksheets("Jan Data").Range("A1").

Where macros go to live

A macro in a workbook only exists in that workbook. For something you want available everywhere — a personal utility — use PERSONAL.XLSB: record any macro with “Store macro in: Personal Macro Workbook”, and Excel creates a hidden workbook that opens with every session. It then appears in the Project Explorer alongside your file, and anything you put in it is available all the time.

Also: a workbook containing code must be saved as .xlsm. Saving as .xlsx discards every module silently, with one warning dialog people routinely click through. If your code has vanished, this is almost always why.

Four editor settings worth changing now

Tools → Options:

  1. Editor → Require Variable Declaration. Adds Option Explicit to every new module automatically. Discussed below; do this first.
  2. Editor → Auto Syntax Check: off. Leave the red-highlighting on, but turn off the popup dialog that interrupts you mid-line every time a statement is temporarily incomplete.
  3. Editor Format → a monospaced font at a readable size. The default is small and dated.
  4. General → Error Trapping: “Break on Unhandled Errors”. The default. If it is set to “Break on All Errors”, your On Error handlers are ignored while debugging, which makes error handling look broken when it is not.

Shortcuts that pay for themselves immediately

Key Does
Alt + F11 Toggle between Excel and the editor
F5 Run the current Sub
F8 Step one line at a time
F9 Toggle a breakpoint on this line
Ctrl + G Immediate window
Ctrl + R Project Explorer
F4 Properties window
F2 Object Browser — every object, method and property, searchable
Ctrl + Space Autocomplete the name you are typing
Shift + F2 Jump to the definition of the procedure under the cursor
Ctrl + Shift + F2 Jump back

Ctrl + Space is the one that changes how it feels to write VBA. Type Appl, press it, and you get Application — no typos, and the member list that appears after the dot is a usable reference in itself.

Reading the Locals window

View → Locals Window shows every variable in the running procedure with its current value and type, updating as you step with F8. For anything involving objects it beats hovering: expand a Range variable and you can see its Address, Value, Row and Count all at once.

Pair it with Debug.Print for things you want a record of rather than a snapshot, and you have most of VBA’s debugging story.

Comments, and what to say in them

An apostrophe starts a comment to end of line.

' Called monthly by ProcessAll. Expects the raw export on "Data"
' with headers in row 1. Writes the summary to "Report" (overwrites).
Sub BuildSummary()

The useful comment is not ' loop through rows on a line that obviously loops through rows. It is the context that is not recoverable from the code: assumptions, what the procedure expects, what it overwrites, and why an odd line is the way it is. Six months later that is the only documentation this code will have.

Next

Once the editor is familiar, the sequence that follows is loops and conditions for control flow, snippets you’ll reuse constantly for the patterns, and error handling and debugging for making it reliable. If you have not recorded a macro yet, do that first — reading the code the recorder generates is the fastest way to learn the object model, and the VBA macros course path sequences the whole thing.

FREE EXCEL UPDATES

Get practical Excel tips in your inbox

New tutorials, tools, templates, and worked examples a few times a month. Unsubscribe anytime.

=PRACTICE

Go deeper with this skill

Turn the idea into a repeatable Excel workflow you can explain, rebuild, and review later. For this article, the goal is to practice: A tour of the VBA editor for anyone past recorded macros — Project Explorer, modules, Sub vs Function, running code, and Option Explicit.

Practice workbook setup

Create a small practice workbook with one raw-data sheet, one working sheet, and one final output sheet.

Practice workflow

  1. Rebuild the example once exactly as described, then repeat it with different labels, dates, or amounts.
  2. Write a short note beside the result explaining what each step is doing and why it matters.
  3. Change one input value and confirm the output updates in the way you expected.
  4. Save a clean copy of the workbook before experimenting further.

Quality checks

  • Inputs, calculations, and final outputs are separated clearly.
  • Headings describe the data without relying on memory or hidden context.
  • The final result can be understood by someone who did not build the workbook.

Common mistakes

  • Mixing raw data and manual adjustments in the same cells.
  • Skipping a quick review after the result looks correct.
  • Building a one-off fix instead of a repeatable workflow.

Next actions

  • Apply the same pattern to a real workbook with 20 to 50 rows of sample data.
  • Add one note that explains when you would not use this approach.

Formula focus: even if this workflow is not formula-heavy, add one check cell that confirms the final output still matches the source data.