🚀The world's best VBA AI has evolved. ExcelMaster is now an autonomous Agent.Read more →
Back to Blog

VBA With Statement in Excel — Write the Object Once (and the Leading Dot That Makes or Breaks It)

|

VBA With Statement in Excel — Write the Object Once (and the Leading Dot That Makes or Breaks It)

TL;DR — With object … End With lets you name an object once and then reach its members with a bare leading dot: .Value, .Font.Bold, .Interior.Color. It's less typing, it reads cleaner, and it's genuinely faster because VBA resolves the object reference a single time instead of on every line. The entire trick lives in that leading dot: .Font binds to your With object; plain Font does not — it falls back to an implicit object (usually the active sheet) and silently changes the wrong thing.

With Sheets("Report").Range("A1")
    .Value = "Total"          ' each dot means: Sheets("Report").Range("A1") . ...
    .Font.Bold = True
    .Font.Size = 14
    .Interior.Color = vbYellow
End With

Deep object paths are everywhere in Excel VBA: ThisWorkbook.Worksheets("Data").Range("A1").Font.…. Typing that prefix on ten consecutive lines is tedious, slow to run, and hard to read — and if you ever need to retarget it to a different cell, you're editing ten places. With fixes all three at once: state the object on the first line, then let every line inside the block hang off it with a dot. It's one of the simplest constructs in the language and one of the easiest to get subtly wrong.

What you'll learn

  • The mental model — With factors out a repeated prefix, like 2(a+b) in algebra
  • Why it's not just tidier: With resolves the object reference once
  • The missing-dot trap — Font vs .Font — and why it fails silently
  • How nested With blocks resolve the dot, and why you should stop at two
  • The With + For Each idiom that ties this cluster together

The mental model: factor out the repeated prefix

Think of With as factoring, the way you rewrite 2*a + 2*b + 2*c as 2*(a + b + c). The repeated thing — here the object path Sheets("Report").Range("A1") — gets stated once, and everything inside the parentheses is understood to be multiplied by it. In VBA the "parentheses" are With … End With, and the "multiply by it" is the leading dot.

' Without With — the prefix repeats on every line
Sheets("Report").Range("A1").Value = "Total"
Sheets("Report").Range("A1").Font.Bold = True
Sheets("Report").Range("A1").Font.Size = 14

' With With — state it once, dot into it
With Sheets("Report").Range("A1")
    .Value = "Total"
    .Font.Bold = True
    .Font.Size = 14
End With

Both do exactly the same thing. The second is shorter, retargets in one edit, and — as the next section shows — actually runs faster. Everything inside the block is implicitly prefixed by the object on the With line.

The rule that makes it more than cosmetic: the object resolves once

With isn't only about typing less. When VBA sees Sheets("Report").Range("A1").Font on ten separate lines, it re-walks that whole chain ten times — look up the sheet, resolve the range, fetch the font — every single line. Inside a With block, VBA resolves the object once, on entry, and reuses that resolved reference for every dotted member.

On a couple of lines the difference is invisible. Inside a loop over thousands of cells, it's real:

Dim cell As Range
For Each cell In Range("A1:A10000")
    With cell
        .Value = .Value * 1.1       ' resolve 'cell' once per iteration...
        .NumberFormat = "0.00"      ' ...not three times
        .Font.Color = vbBlue
    End With
Next cell

Without the With, each cell. re-dereferences the range object on every line; with it, that dereference happens once per iteration. The rule: when you touch three or more members of the same object — especially inside a loop — wrap them in With. It's the rare readability improvement that also buys you speed.

The trap that changes the wrong object: the missing leading dot

This is the bug that makes With dangerous rather than merely helpful. The leading dot is what binds a member to the With object. Leave it off, and the member doesn't error — it binds to something else:

With Sheets("Report").Range("A1")
    .Value = "Total"
    Font.Bold = True        ' BUG: no leading dot — this is NOT the With object's font
End With

.Font.Bold means Sheets("Report").Range("A1").Font.Bold. But Font.Bold — no dot — is an unqualified reference. VBA resolves it against the active sheet's implicit context, so you either get a compile error or, worse, you silently format a cell on whatever sheet happens to be active. Nothing warns you; the With object is simply untouched while some other object changes.

The rule is mechanical and worth burning in: inside a With block, every member that belongs to the object must start with a dot. When you review a With block, scan the left margin — anything not starting with . is either a deliberate reference to a different object or a bug. (This is the same family of "implicit ActiveSheet" mistake covered in VBA ActiveSheet — an unqualified Range or Cells quietly attaches to whatever sheet is active.)

The rule for nesting: the dot binds to the nearest With

You can nest With blocks, and it's genuinely useful for drilling into a sub-object. But the resolution rule catches people: a leading dot always binds to the innermost With.

With Sheets("Report")
    .Range("A1").Value = "Report"      ' one dot -> the sheet
    With .Range("A2").Font             ' open an inner With on the font
        .Bold = True                   ' .Bold -> the FONT (innermost), not the sheet
        .Size = 12
    End With
    .Range("A3").Value = "Done"        ' back to the sheet after End With
End With

Inside the inner block, .Bold is the font's, not the sheet's. That's correct and readable at two levels — but at three or four it becomes a guessing game about which object each dot means. The rule: nest at most two With blocks. Deeper than that, assign an intermediate object variable (Set fnt = .Range("A2").Font) and give the reader a name instead of a stack of dots.

The idiom that ties the cluster together: With + For Each

With and For Each are natural partners. For Each hands you one object at a time; With lets you work that object cleanly without repeating the loop variable:

Dim cell As Range
For Each cell In Sheets("Data").Range("B2:B100")
    With cell
        If .Value < 0 Then
            .Interior.Color = vbRed
            .Font.Bold = True
        End If
    End With
Next cell

Read it aloud and it's almost English: "for each cell, with that cell — if it's negative, colour it red and bold it." That pairing — For Each to walk the collection, With to act on each member — is the backbone of clean formatting and data-marking macros across a Range.

How ExcelMaster helps

With is a small thing done a thousand times: factor the prefix, remember the dot, don't nest too deep. It's exactly the sort of mechanical care that separates readable macros from brittle ones — and exactly the sort of thing you'd rather not think about when you just want a sheet formatted.

ExcelMaster lets you describe the result instead. Say "in the Data sheet, colour every negative value in column B red and bold it," and it writes and runs the logic — qualifying every object reference correctly, so the silent "wrong sheet" bug the missing dot causes simply can't happen. When you hand-write a scheduled macro you'll still reach for With yourself; for the everyday formatting-and-marking task, describing what you want beats getting every leading dot right by hand.

Frequently asked questions

What does the With statement do in VBA?

With object … End With names an object once so you can access its members with a bare leading dot (.Value, .Font.Bold) instead of repeating the full object path on every line. It makes code shorter, easier to retarget, and faster — because VBA resolves the object reference a single time on entry rather than on each line.

Why isn't my With block changing the right object?

Almost always a missing leading dot. Inside With obj, .Font refers to obj, but Font with no dot is an unqualified reference that binds to the active sheet's implicit context — so you silently change the wrong object. Every member that belongs to the With object must start with a dot.

Does the With statement actually make VBA faster?

Yes, modestly, and it matters inside loops. VBA resolves the object on the With line once and reuses that reference for every dotted member, instead of re-walking the full object chain on each line. On two or three lines you won't notice; over thousands of iterations the saved re-dereferences add up.

Can I nest With statements in VBA?

Yes. A leading dot always binds to the innermost active With, so With .Range("A2").Font inside With Sheets("Report") makes .Bold the font's. It's readable at two levels, but deeper nesting makes it hard to tell which object each dot refers to — assign an intermediate object variable instead of nesting three or more deep.

Can I change the With object itself inside the block?

No — the With reference is fixed when the block is entered. You can't reassign what With points to partway through; to work on a different object, close the block with End With and open a new one. You can read and modify the object's members freely, just not repoint the block.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-08-01.

Related guides: VBA For Each · VBA Collection · VBA Range · VBA ActiveSheet · VBA For Loop