VBA variables

A variable is a named box that holds a value or an object reference. With variables you can name things (total, ws, lastRow) instead of repeating expressions. You compute once and reuse the result, and your code reads like prose. You create one with Dim, short for dimension, and give it a name and a type:

Dim total As Double
Dim lastRow As Long
Dim region As String
Dim ws As Worksheet

Here are the core types and what each one is for:

• Long holds a whole number (an integer) up to about ±2.1 billion. Use it for counters, row numbers, and counts, as in For r = 2 To lastRow. It's the default integer type in modern VBA. Prefer it to the older Integer, which caps at 32,767 and can overflow on a big sheet.
• Double is a floating-point number with decimals. Use it for money, rates, measurements, any total (total = 4785000, rate = 0.18). It's the workhorse numeric type.
• String holds text (region = "North", a name, a label, a formula string).
• Range is a reference to one or more cells (Set target = ws.Range("B6")). It's an object type, so you assign it with Set.
• Worksheet / Workbook are references to a sheet or a file (Set ws = ThisWorkbook.Worksheets("Sales")). They're objects too, so they also take Set.
• Variant is the "anything" type. It can hold a number, text, an error, or an array, and it's what you get if you declare a variable with no type at all. It's convenient and occasionally necessary, since reading a whole range into an array yields a Variant. It's also memory-heavy and slow. And it's the type an undeclared variable turns into, without a word of warning.

Declaring the type isn't bureaucracy. It lets VBA store the value efficiently and catch type mismatches early. Best of all, it turns on IntelliSense, so the editor offers you the object's members as you type the dot.

What does Option Explicit do?

Which brings you to Option Explicit. Put these two words at the very top of every module, above every Sub:

Option Explicit

Option Explicit forbids any variable you didn't Dim. That one rule wipes out the most vicious silent bug in VBA. Here's what the bug looks like. Suppose you write this without Option Explicit:

Sub Buggy()
    total = 4785000
    Range("B6").Value = totla    ' typo: totla, not total
End Sub

With no Option Explicit, VBA doesn't object to totla. It just creates a brand-new, empty Variant called totla, whose value is zero or blank, and writes that into B6. Your report shows a blank where the total should be. No error appears, and you can stare at the code for an hour. Put Option Explicit at the top and VBA refuses to compile. It stops with "Variable not defined," and the cursor blinks on totla. You catch the typo in one second instead of finding it in production. To switch it on for every new module, go to Tools ▸ Options ▸ Editor ▸ Require Variable Declaration in the VBE, and the header goes in for you. There's no professional VBA without Option Explicit. It's the seatbelt, and you wear it every time.