📎 This article includes 1 downloadable practice file ↓

📘 Excel VBA Course · Lesson 1 of 15 — see all lessons

A variable is a named box where your macro keeps a value while it runs — a total, a customer name, a row number. Getting variables right is the difference between a macro that works once and one that keeps working.

Declare every variable with Dim

Dim customerName As String
Dim invoiceTotal As Double
Dim rowNum As Long
Dim isPaid As Boolean

Dim reserves the box and As says what may go in it. You can declare several on one line, but each needs its own type: Dim a As Long, b As Long. Writing Dim a, b As Long makes only b a Long — a becomes a Variant.

Turn on Option Explicit

Put Option Explicit at the very top of every module (or tick Tools → Options → Require Variable Declaration so the editor adds it for you). Without it, a typo like invoiceTotl silently creates a new empty variable and your total ends up as 0.

Which data type should I use?

Objects need Set

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sales")
ws.Range("A1").Value = "Updated " & Now

Forgetting Set gives “Object variable or With block variable not set”. It is one of the bugs in the VBA Bug Hunt.

Scope: where a variable lives

• Inside a Sub (Dim) — exists only while that procedure runs.
• Top of a module (Private) — shared by all procedures in that module.
• Top of a module (Public) — visible to the whole project. Use sparingly; global state makes bugs hard to trace.

Constants for values that never change

Const VAT_RATE As Double = 0.18
Const REPORT_SHEET As String = "Report"

Change a rule in one place instead of hunting for “0.18” across twenty procedures.

A worked example: swap two cells

Sub SwapAB()
    Dim temp As Variant
    temp = Range("A1").Value
    Range("A1").Value = Range("B1").Value
    Range("B1").Value = temp
End Sub

The temporary variable holds A1 so it is not lost when A1 is overwritten. Step through it line by line in the VBA Playground.

Naming tips

• Describe the content: lastRow, customerCount — not x or temp2.
• No spaces; start with a letter; avoid VBA keywords like Name or Date.

Try it yourself: step by step

1. Download vba-course-practice.xlsm and open it. Click Enable Content in the yellow bar.
2. Go to the Macros sheet — there is a button for every lesson. Press Alt+F11 any time to read the code.
3. Type 10 in A1 and 25 in B1 of any sheet, then run L01_SwapAB with Alt+F8. The values swap.
4. Put the cursor inside the macro and press F8 repeatedly to step through it; hover over temp to see its value.
5. Change Dim temp As Variant to Dim temp As Long and swap two text values — see the “Type mismatch” error, then change it back.