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

VBA Dim in Excel — Declare Variables, Option Explicit & the Typo That Becomes an Empty Box

|

VBA Dim in Excel — Declare Variables, Option Explicit & the Typo That Becomes an Empty Box

TL;DR — Dim name As Type reserves a named box and fixes its shape before you use it: Dim total As Long. Declaring is optional in VBA — and that is exactly the problem. Without it, one misspelled variable name silently becomes a brand-new empty Variant, your total stays 0, and no error ever fires. Put Option Explicit at the very top of every module. It forces you to declare every variable, so a typo becomes a compile error ("Variable not defined") instead of a wrong answer. It is the highest-leverage line in the language.

Option Explicit          ' <- put this at the top of EVERY module

Sub Demo()
    Dim total As Long    ' reserve a whole-number box named "total"
    Dim name As String   ' reserve a text box named "name"

    total = 10 + 5
    name = "Invoice"
    Debug.Print name, total   ' -> Invoice      15
End Sub

Every macro starts by naming the things it will work with — a row counter, a worksheet, a running sum. Dim is how you do that. It looks like paperwork, but skipping it (or doing it loosely) is behind a whole category of bugs that produce no error at all — the worst kind, because the macro runs to the end and hands you a confident wrong number.

What you'll learn

  • What Dim actually does — reserve a named box and fix its shape
  • Why Option Explicit turns silent typo bugs into loud compile errors
  • The Dim a, b As Long trap that makes a a Variant, not a Long
  • When a plain = isn't enough and you need Set (objects)
  • Where you declare a variable decides who can see it and how long it lives

The mental model: Dim reserves a labeled box before you fill it

Think of a variable as a labeled box in memory. Dim total As Long does two things at once: it reserves a box and gives it the label total, and it fixes the box's shape to Long (a whole number). From then on, total = 15 puts a value in that box and the name total reads it back.

The label matters more than beginners expect, because of what VBA does when it sees a name it doesn't recognise. By default — with no Option Explicit — VBA's reaction to an unfamiliar name is not "error." It is "oh, a new variable," and it quietly creates one for you, empty. That single design decision is the source of the most confusing bug in VBA, and the next section is the fix.

The rule that saves you from the worst VBA bug: Option Explicit

Here is the bug, in three lines:

Sub AddThemUp()
    total = 0
    total = totl + 10      ' typo: "totl", not "total"
    Debug.Print total      ' -> 10, forever — the typo made a NEW empty box
End Sub

You meant total, you typed totl. Without Option Explicit, VBA doesn't flag it. It invents a fresh, empty Variant called totl (value 0 / Empty), adds 10 to nothing, and your real total never accumulates. No red text, no message box — just a wrong result you might not notice until it's in a report.

Option Explicit ends this entire class of bug. Placed as the first line of a module, it makes declaration mandatory: every variable must appear in a Dim (or Private / Public) before use. Now the typo can't create a phantom box — it's an undeclared name, so VBA stops at compile time:

Option Explicit

Sub AddThemUp()
    Dim total As Long
    total = 0
    total = totl + 10      ' Compile error: Variable not defined  (highlights "totl")
End Sub

The error points straight at the misspelling before the macro ever runs. Turn it on everywhere by default: Tools → Options → Editor → "Require Variable Declaration" adds Option Explicit to every new module automatically (you still add it by hand to existing ones). Treat "Variable not defined" as a gift — it just caught a bug for free.

The rule that ties each Dim to one type: the Dim a, b As Long trap

This is the declaration mistake almost everyone makes once. You want three Long counters, so you write:

Dim i, j, k As Long

You would reasonably expect i, j, and k to all be Long. They are not. Only k is Long. i and j — which have no As clause of their own — are Variant. In VBA the type applies only to the variable it's directly attached to; there is no "spread the type across the list" rule.

' WRONG — i and j are Variant, only k is Long
Dim i, j, k As Long

' RIGHT — every variable gets its own type
Dim i As Long, j As Long, k As Long

' Clearest of all — one per line
Dim i As Long
Dim j As Long
Dim k As Long

The failure mode is subtle: the code still runs, because a Variant will hold a number just fine. You lose the type safety and speed you thought you had, and the bug only surfaces later when one of those "Long" variables silently accepts text or a giant value it shouldn't. Give every variable its own As Type.

The rule for objects: a plain = isn't enough, you need Set

Variables that hold a value — a number, some text, a date — are assigned with =. Variables that hold an object — a Worksheet, a Range, a Workbook — must be assigned with Set. Forgetting Set is the other classic beginner error.

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Data")   ' Set is required for objects
Debug.Print ws.Range("A1").Value

Dim rng As Range
Set rng = ws.Range("A1:A10")               ' again, Set

Leave off Set and VBA doesn't assign the object — it tries to read the object's default value instead, and you get "Object variable or With block variable not set" (error 91) or "Object required" (error 424). The rule is mechanical and worth memorising: value types use =, object types use Set. If a type name refers to a thing in Excel's object model (Worksheet, Range, Chart, Workbook), it needs Set.

Where you Dim decides scope and lifetime

The place you write Dim is not cosmetic — it decides who can see the variable and how long it survives:

  • Inside a Sub or Function → the variable is local. It's born when the procedure starts and gone when it ends. Nothing outside can see it.
  • At the top of a module, above the first procedure → it's visible to every procedure in that module. Use Private there to keep it module-scoped.
  • Public at the top of a module → it's a global, visible to the whole project. Powerful, but easy to abuse; a value that anything can change from anywhere is hard to reason about.

As a rule, declare variables as locally as you can get away with — right inside the procedure that uses them. Reach for module-level or Public only when a value genuinely must be shared. Scope is really a topic of its own; if you're passing values between procedures, see how arguments flow in VBA Sub and VBA ByRef vs ByVal.

How ExcelMaster helps

Dim, Option Explicit, and disciplined scoping are what separate a macro that survives a year of edits from one that rots. They're also friction — the careful declarations you write before you get to the part you actually care about.

ExcelMaster lets you skip straight to the outcome. Describe the task in plain English — "in the Orders sheet, sum column D where the status is Paid, grouped by month" — and it writes and runs the logic for you, declaring the right variables and types the robust way rather than leaving you to remember Option Explicit. When you do need a hand-written macro on a schedule, you'll still Dim things yourself. For the everyday "just get this done on today's data" task, describing the result beats getting every declaration right by hand.

Frequently asked questions

Do I have to declare variables in VBA?

Technically no — VBA will let you use a variable you never declared, treating it as a Variant. In practice you should declare everything, because undeclared variables let a single typo silently create a new empty box and produce a wrong answer with no error. Put Option Explicit at the top of every module to make declaration mandatory.

What does Dim mean in VBA?

Dim is short for "dimension" — it dates back to reserving the dimensions of an array in early BASIC. Today it simply means "declare a variable": reserve a named place in memory and, with As Type, fix what kind of value it holds, e.g. Dim count As Long.

Does Dim a, b As Long make both a and b Long?

No. Only b is Long; a is a Variant, because the As clause applies only to the variable it's directly attached to. To make both Long, write Dim a As Long, b As Long — give each variable its own As Type.

What is Option Explicit and where do I put it?

Option Explicit forces every variable to be declared before use. Put it as the first line of every module, above all procedures. It converts silent "undeclared typo" bugs into a clear "Variable not defined" compile error. Enable Tools → Options → Editor → Require Variable Declaration to add it to new modules automatically.

When do I use Set instead of = in a Dim assignment?

Use Set when the variable holds an object from Excel's object model — a Worksheet, Range, Workbook, Chart, and so on: Set ws = ActiveSheet. Use a plain = for values — numbers, text, dates, Booleans. Forgetting Set on an object gives "Object variable or With block variable not set" (error 91).

Tested in

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

Related guides: VBA Data Types · VBA Const · VBA Sub · VBA ByRef vs ByVal · VBA Range