Excel VBA Variables and Data Types: A Practical Guide

📎 This article includes 1 downloadable practice file ↓

Every macro you will ever write stores things for a moment: a row number, a customer name, a running total, today’s date. Those little storage boxes are variables. Get them right at the start and most of the strange errors beginners hit simply never happen. This first lesson sets up the editor, writes a real macro, and explains variables properly.

Open the VBA editor

1. Show the Developer tab: File › Options › Customize Ribbon, tick Developer.
2. Press Alt+F11 to open the Visual Basic Editor.
3. Insert › Module. The white area on the right is where code goes.
4. Save your workbook as .xlsm or .xlsb — a plain .xlsx throws the code away when it closes.

Your first macro

Option Explicit

Sub HelloTotal()
    Dim total As Double
    total = Application.WorksheetFunction.Sum(Range("B2:B10"))
    MsgBox "The total is " & Format(total, "#,##0.00")
End Sub

Put the cursor anywhere inside it and press F5. Three ideas are already here: a variable (total), a data type (Double), and joining text with &.

Declaring variables with Dim

Dim means “make a box with this name and this type”. You can declare several on one line, but each needs its own type:

Dim r As Long, lastRow As Long        ' both Long
Dim a, b As Long                      ' careful: a is Variant, only b is Long

That second line catches almost everyone once. Write the type after every name.

The data types you will actually use

Objects need Set

Dim ws As Worksheet, cell As Range
Set ws = ThisWorkbook.Worksheets("Sales")
Set cell = ws.Range("E2")
cell.Value = cell.Value * 1.18

Plain values (numbers, text, dates) are assigned with =. Objects (sheets, ranges, workbooks) need Set. Forgetting Set gives “Object variable or With block variable not set” (error 91).

Variable scope: where a variable lives

• Inside a Sub (Dim inside): exists only while that Sub runs. Use this 95% of the time.
• Top of a module (Private x As Long above all Subs): shared by every Sub in that module.
• Public (Public x As Long at the top of a module): visible everywhere. Use sparingly — values hang around and cause surprises.

Const is a variable that never changes. Put fixed values like GST rates at the top so you only change them in one place:

Const GST_RATE As Double = 0.18
Const SHEET_NAME As String = "Sales"

Reading and writing cells with variables

Sub AddGST()
    Dim ws As Worksheet, r As Long, lastRow As Long
    Dim amount As Double
    Set ws = ThisWorkbook.Worksheets("Sales")
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    For r = 2 To lastRow
        amount = ws.Cells(r, "B").Value
        ws.Cells(r, "C").Value = Round(amount * (1 + GST_RATE), 2)
    Next r
End Sub

Don’t worry about the loop yet — that’s lesson 6. Notice how each variable has one clear job.

Where people go wrong

Practice

The practice workbook has a Sales sheet and buttons for the macros above, plus three broken macros — one per common error — to fix yourself. Press F8 to step through line by line and watch variables in the Locals window (View › Locals Window).

Next: ranges — Cells, Offset, Resize and finding the last row properly.