Variables, data types, Option Explicit

Concept

1. What is a variable?

A named box that holds a value while the macro runs.

Dim price As Double
price = 18000
MsgBox price * 1.18

Dim declares it, As gives its type.

2. Common data types

Type	Holds	Example
`Long`	whole numbers up to about ±2.1 billion	row numbers, quantities
`Double`	decimals	prices, rates
`String`	text	names, file paths
`Boolean`	True / False	flags
`Date`	dates and times	`#10/1/2026#` or `DateSerial(2026, 10, 1)`
`Variant`	anything (default if no type given)	values read from cells

Use Long, not Integer, for row numbers. Integer stops at 32,767, so a sheet with 40,000 rows causes an "Overflow" error.

Date literals with # are always month/day/year. To avoid confusion, use DateSerial(year, month, day).

3. Object variables need Set

Sheets, ranges and workbooks are objects. Assign them with Set:

Dim ws As Worksheet
Dim rng As Range

Set ws = ThisWorkbook.Worksheets("Data")
Set rng = ws.Range("A1:E100")

rng.Font.Name = "Calibri"

Forget Set and you get error 91 ("Object variable not set").

ThisWorkbook = the file containing the code. ActiveWorkbook = whichever file is in front. Prefer ThisWorkbook unless you mean another file.

4. Option Explicit

Without it, a typo silently creates a new empty variable:

total = 500
MsgBox totl      ' shows blank — no error!

Put this at the very top of every module:

Option Explicit

Now VBA refuses to run until every variable is declared, and points to the typo.

Make it automatic: in the VBA Editor, Tools → Options → tick Require Variable Declaration. Every new module will start with Option Explicit.

5. Constants

Values that never change:

Const GST_RATE As Double = 0.18
Const DATA_SHEET As String = "Data"

Change it in one place instead of hunting through the code.

6. Scope — where a variable lives

Declared inside a Sub with Dim: exists only while that Sub runs.
Declared at the top of a module (after Option Explicit) with Private: shared by all Subs in that module.
Public at the top: available in every module. Use sparingly.

7. Putting it together

Option Explicit

Const GST_RATE As Double = 0.18

Sub AddGST()
    Dim ws As Worksheet
    Dim amount As Double
    Dim gst As Double

    Set ws = ThisWorkbook.Worksheets("Data")
    amount = ws.Range("B2").Value
    gst = amount * GST_RATE

    ws.Range("C2").Value = gst
    ws.Range("D2").Value = amount + gst
End Sub

Common mistakes

Using Integer for rows. Forgetting Set for objects. Declaring several variables as Dim a, b, c As Long — only c is Long; a and b become Variant. Write Dim a As Long, b As Long, c As Long.

What you'll learn