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

VBA Const in Excel — Name Your Magic Numbers (and Why the Value Can't Drift)

|

VBA Const in Excel — Name Your Magic Numbers (and Why the Value Can't Drift)

TL;DR — Const VAT_RATE As Double = 0.19 gives a fixed value a name that's locked at compile time. Where a variable is a box you can refill, a constant is a label painted on — readable everywhere, repaintable nowhere while the macro runs. That immutability is the whole point: it turns a magic number scattered across eight formulas into one authoritative source. Trying to reassign a Const is a compile error ("Assignment to constant not permitted") — and that error is the feature, not a limitation. For a family of related fixed values (statuses, column numbers), use an Enum instead of many separate Consts.

Const VAT_RATE   As Double = 0.19          ' one place, one truth
Const MAX_ROWS   As Long = 10000
Const REPORT_TAB As String = "Summary"

Sub ShowRate()
    Debug.Print "VAT is " & VAT_RATE * 100 & "%"   ' -> VAT is 19%
End Sub

Every macro is full of literal values: a tax rate, a header row number, a sheet name, a threshold. Typed inline, each one is a magic number — a bare 0.19 or 7 whose meaning you have to guess and whose value you have to hunt down when it changes. Const is the cure, and it's one of the cheapest quality upgrades you can make to a macro.

What you'll learn

  • Why magic numbers are a maintenance trap, and how Const fixes it in one place
  • Why reassigning a Const is a compile error — and why that's exactly what you want
  • The compile-time rule: a Const can be a literal, not a function call or a cell value
  • Public Const for values your whole project shares
  • When to graduate from many Consts to a single Enum

The mental model: a constant is a label painted on, not a box you refill

A variable and a constant look similar, but they behave in opposite ways. A variable is a box: you put a value in, and you can swap it for another value later. A constant is a label painted onto a value at the moment you compile. Everyone can read the label from anywhere it's in scope — but nothing can repaint it while the code is running. The value is frozen.

That frozenness is not a restriction to work around; it's the guarantee you're buying. When you write Const VAT_RATE = 0.19, you're telling both the compiler and the next reader: this number has one meaning, it lives in one place, and it will not change out from under you. Everything good about constants flows from that promise.

The rule that earns its keep: magic numbers drift, constants don't

Here's the problem Const solves. Suppose 0.2 — a VAT rate — is typed directly into eight different formulas across your macros:

' Magic number 0.2 scattered everywhere — a rate change means hunting all eight
gross = net * 1.2
vat   = net * 0.2
' ...six more sites, each with a bare 0.2...

Now the rate changes to 0.19. You have to find and edit every one. Miss a single site and you get a silent wrong total — no error, just numbers that don't add up, in one report and not another. And a reader six months later has no idea whether that 0.2 is VAT, a discount, or a commission.

One constant fixes all of it:

Const VAT_RATE As Double = 0.19    ' change it here, once, and every site updates

gross = net * (1 + VAT_RATE)
vat   = net * VAT_RATE

The value lives in exactly one place, the name documents its meaning, and a rate change is a one-line edit that can't be half-done. Any literal that appears more than once, or whose meaning isn't obvious from context, should be a Const.

The rule that is really a feature: you can't assign to a Const

Try to change a constant and VBA refuses — at compile time, before the macro even runs:

Const TAX As Double = 0.2

Sub Oops()
    TAX = 0.25       ' Compile error: Assignment to constant not permitted
End Sub

Beginners sometimes read that error as VBA being difficult. It's the opposite: it's the compiler guaranteeing the value never changes, which is the entire reason you declared a constant. If you find yourself wanting to reassign it, then it was never really a constant — it's a variable that changes at run time, so declare it with Dim. The distinction is the whole decision: does this value change while the macro runs? If yes, Dim. If no, Const.

The rule that surprises people: Const is evaluated at compile time

Because the label is painted on when you compile, a constant can only be set to something the compiler already knows: a literal, or an expression built from other constants. It cannot be a function call, a worksheet value, or anything computed while the macro runs.

' WORKS — literal, or expression of literals/other constants
Const PI       As Double = 3.14159
Const TWO_PI   As Double = 2 * PI
Const HEADER   As String = "Report — " & "2026"

' FAILS — needs run-time evaluation, so it must be a variable
Const TODAY    As Date = Date          ' Compile error: not a constant
Const RATE     As Double = Range("A1").Value   ' Compile error

If the value can only be known while the macro runs — today's date, a cell's current contents, the result of a function — it belongs in a Dim variable that you assign at run time, not a Const.

Scope: local Const vs Public Const

A Const follows the same scope rules as a Dim. Written inside a Sub, it's local to that procedure. Written at the top of a module with Public, it becomes a project-wide constant every module can read:

' In a module called "Config", above any procedure:
Public Const APP_NAME    As String = "Monthly Close"
Public Const DATA_SHEET  As String = "Raw"
Public Const HEADER_ROW  As Long = 1

The idiom that scales is a single Config module holding every app-wide constant — sheet names, thresholds, the app title — so there's exactly one place to look when something needs changing. Keep procedure-specific constants local; promote only the ones that are genuinely shared.

When one value isn't enough: use Enum for a family

When you have a group of related constants — order statuses, column positions, report types — a pile of separate Consts works but reads poorly. An Enum names them as a set, groups them under one type, and gives you IntelliSense and light type-checking:

Enum OrderStatus
    Pending = 1
    Paid = 2
    Shipped = 3
    Cancelled = 4
End Enum

Sub Demo()
    Dim s As OrderStatus
    s = Paid
    If s = Cancelled Then Exit Sub
End Sub

The rule of thumb: Const for one standalone value; Enum for a family of related values that belong together. An Enum also signals intent — a parameter typed As OrderStatus tells the reader (and the editor) exactly which values are legal.

How ExcelMaster helps

Constants, scope discipline, and Enums are the habits that keep a growing macro maintainable — and they're overhead you carry on every project, long before you get to the logic you actually care about.

ExcelMaster lets you state the rule instead of wiring the plumbing: "apply 19% VAT to column D, flag anything in the Cancelled status, write the total to the Summary sheet." It turns that into working logic with the rate and the status values in one place, so a change is a change to the description, not a hunt through eight formulas. You'll still declare Consts in hand-written macros where you want the control; for the everyday task, describing the rule beats maintaining the magic numbers behind it.

Frequently asked questions

What is a constant in VBA?

A constant is a named value that's fixed at compile time and can't change while the macro runs. You declare one with Const, e.g. Const VAT_RATE As Double = 0.19. It replaces a scattered "magic number" with a single named source, so the value lives in one place and its meaning is documented by its name.

What's the difference between Const and Dim?

Dim declares a variable — a box whose value you can change at run time. Const declares a constant — a value locked at compile time that can't be reassigned. Use Const for anything that never changes while the macro runs (a rate, a sheet name, a threshold) and Dim for anything that does.

Can I change a Const at run time?

No. Assigning a new value to a constant is a compile error ("Assignment to constant not permitted"), and that guarantee is the reason to use one. If a value needs to change while the macro runs, it isn't a constant — declare it with Dim as a variable instead.

Why does Const x = Range("A1").Value fail?

Because a Const is evaluated at compile time, it can only be set to a literal or an expression of other constants — never to something computed at run time like a cell value, a function result, or Date. For those, use a Dim variable that you assign while the macro runs.

What is Enum in VBA and when should I use it instead of Const?

An Enum defines a family of related named constants under one type, e.g. order statuses or column numbers. Use several Consts for standalone values, but reach for an Enum when the values belong together as a set — it groups them, enables IntelliSense, and lets you type a parameter As that enum to document which values are valid.

Tested in

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

Related guides: VBA Dim · VBA Data Types · VBA Sub · VBA Function · VBA If Then Else