Skip to content
Tutorials Center
My learning

Topics

Lesson 6 of 37 ยท Language Basics

Option Explicit, Constants and Variable Scope

Use Option Explicit to catch typos, define constants and enums, and understand procedure, module and public scope plus Static variables in VBA.

Three habits separate tidy VBA from fragile VBA: always turning on Option Explicit, using constants instead of magic numbers, and keeping variables in the smallest scope that works. This lesson covers all three.

Option Explicit

Without it, VBA creates a variable the first time it sees a new name. A typo then silently makes a new empty variable:

VB
Sub Total()
    Dim totalPrice As Double
    totalPrice = 10
    totlPrice = totalPrice * 2     ' typo: new Variant variable
    MsgBox totalPrice              ' still shows 10
End Sub

Put Option Explicit as the very first line of the module (above all procedures). Now the typo is a compile error: Variable not defined, and the editor highlights it. Switch on Tools > Options > Require Variable Declaration so the editor adds it for you. In older files, add it by hand.

Constants

A constant is a name for a value that never changes. Use Const:

VB
Const TAX_RATE As Double = 0.2
Const APP_NAME As String = "Invoice Tool"
Const MAX_ROWS As Long = 1000

Benefits: the intention is clear (price * TAX_RATE beats price * 0.2), one place to change, and trying to assign to it is a compile error. By convention constant names are UPPER_SNAKE_CASE.

VBA and Office already define many: vbCrLf (a line break), vbTab, vbYes, xlUp, xlCenter, vbInformation. Colours also: vbRed, vbWhite.

Enumerations

An Enum groups related whole-number constants:

VB
Public Enum Priority
    Low = 1
    Normal = 2
    High = 3
End Enum

Sub ShowPriority()
    Dim p As Priority
    p = High
    Debug.Print p       ' 3
End Sub

Enums appear in autocomplete lists, which prevents invalid numbers. If you omit the values they count up from 0.

Scope: where a variable can be seen

Three levels of scope

Procedure Dim inside a Sub. Smallest, best.
Module Dim above all Subs. Visible in that module.
Project Public above all Subs. Visible everywhere.
1 / 2
VB
Option Explicit

Dim moduleCounter As Long          ' module level: lives while the workbook is open
Public appUser As String           ' project level: any module can use it
Private Const LIMIT As Long = 5    ' module level constant

Sub Demo()
    Dim localValue As Long         ' procedure level: gone when Demo ends
    localValue = 1
    moduleCounter = moduleCounter + 1
End Sub

Two procedures can each declare their own i without clashing. If a local variable has the same name as a module variable, the local one wins inside that procedure (this "shadowing" is a common source of confusion, so avoid it).

Module-level variables keep their value between calls until the project is reset (an unhandled error, End statement, or closing the workbook). Local variables are recreated each call.

Static variables

Static keeps a local variable's value between calls while limiting its visibility to one procedure:

VB
Sub CountCalls()
    Static calls As Long
    calls = calls + 1
    Debug.Print "Called " & calls & " times"
End Sub

Calling CountCalls three times prints Called 1 times, Called 2 times, Called 3 times.

Public, Private and Friend

Procedures have scope too. Public (the default for Subs) means other modules and the Macros dialog can see them. Private Sub Helper() hides it from other modules and from the Macros list, ideal for helpers. Add Option Private Module at the top to hide a whole module's procedures from the Macros dialog.

Stand-in: scope in JavaScript

JavaScript's closures behave like module-level and static variables. This stand-in (not VBA) reproduces the CountCalls example and shows a local variable resetting.

JS Stand-in: Static versus local

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Good habits

  1. Option Explicit in every module.
  2. Declare variables at the top of the procedure, one per line, with a type.
  3. Constants for tax rates, sheet names, column numbers and messages.
  4. Pass values between procedures as arguments instead of sharing public variables (Procedures and Functions).

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does Option Explicit do?

    Undeclared (often misspelled) names become compile errors.
  2. 2. Which line declares a constant?

    Const name As Type = value.
  3. 3. Where must a module-level variable be declared?

    The declarations section sits at the top of the module before the first procedure.
  4. 4. What does Static do to a local variable?

    A Static local variable is not reset when the procedure ends.

Summary

  • Always start modules with Option Explicit.
  • Use Const and Enum instead of magic numbers.
  • Prefer procedure-level variables; module level keeps values between calls; Public is visible everywhere.
  • Static keeps a local variable alive between calls.

Next: Operators and Expressions