Home / VBA Tutorial / Day 2

Day 2 — Variables, types and Option Explicit

A variable is a named box. Getting the name and the type right is most of what separates code that works from code that nearly works.

Declaring a variable

Dim creates the box. As says what kind of thing goes in it.

Dim variableName As Type

Sub Variables()
    Dim customer As String
    Dim units As Long
    Dim price As Double

    customer = "Anita"
    units = 3
    price = 52000

    Debug.Print customer, units, price
    Debug.Print "Order value:", units * price
End Sub

The types worth knowing

Type	Holds	Use it for
`String`	Text	Names, regions, product codes
`Long`	Whole numbers	Row numbers, counters, quantities
`Double`	Numbers with decimals	Money, percentages, averages
`Boolean`	True or False	Flags - found it, is it valid
`Date`	A date and time	Order dates, cut-offs
`Variant`	Anything	When you genuinely do not know

Long, not Integer You will see Integer in old code. It stops at 32,767 - smaller than one Excel column, which has 1,048,576 rows. Loop down a full column with an Integer and it overflows and crashes. Use Long for anything counting rows. There is no speed penalty.

Option Explicit - turn this on today

Put Option Explicit as the first line of every module and Excel refuses to run code containing a variable you never declared.

That sounds like extra work. It is the single biggest bug-saver in VBA. Without it, this runs happily and gives you nothing:

customerName = "Anita"
Debug.Print custmerName ' typo - prints an empty line, no error

VBA sees custmerName, decides you must want a brand new empty variable, and prints nothing. You then lose forty minutes. With Option Explicit on, it stops immediately and points at the typo.

In Excel, switch it on permanently: Tools → Options → Require Variable Declaration.

Joining text with &

& glues things into one string. It converts numbers to text for you.

Sub Joining()
    Dim rep As String
    Dim units As Long

    rep = "Anita"
    units = 3

    Debug.Print rep & " sold " & units & " units"
    Debug.Print "Line 1" & vbCrLf & "Line 2"
End Sub

vbCrLf is a line break. Inside a MsgBox it is how you get a message onto more than one line.

Use & for joining, never + + works on two strings, but the moment one side is a number VBA tries to add instead of join, and you get a type mismatch - or worse, a silently wrong answer. & always joins. Make it a habit.

Reading a value out of a cell into a variable

This is the shape of almost every real macro: pull values out, work on them, put a result back.

Sub FirstOrder()
    Dim rep As String
    Dim units As Long
    Dim price As Double
    Dim total As Double

    rep = Cells(2, 4).Value
    units = Cells(2, 6).Value
    price = Cells(2, 7).Value

    total = units * price

    Cells(2, 8).Value = total
    Debug.Print rep & " order total: " & total
End Sub

Look at the sheet under the output. H2 has a gold outline - that is the playground marking every cell your macro changed. Tomorrow you will do that for all 16 rows with three more lines of code.

Try these yourself

Declare a Date variable, put Cells(2, 2).Value in it, and print it.
Change FirstOrder to work on row 5 instead of row 2, without breaking anything else.
Write one Debug.Print that produces: Anita sold 3 Laptop on 05-Jan-2025
Explain why Dim rowNum As Integer is a bad idea in a macro that loops down a column.
What does Option Explicit protect you from? Give an example of a bug it catches.

← PreviousDay 1 — What VBA is, and your first macro Next →Day 3 — Cells, Range and Offset