In What Is a Variable in VBA and What Is a Loop in VBA, we used the variable i without declaring it. VBA accepts this: it creates the variable automatically the first time you use it. It is convenient, but it is not safe.
In this article, you will see why it is better to declare your variables, and how to give them a clear name.
1. VBA doesn’t force you to declare your variables
Look at this procedure. There is a typing mistake in the third line: MyTotl instead of MyTotal.
Sub CalculateTotal()
MyTotal = 10
MyTotal = MyTotl + 5
MsgBox MyTotal
End SubVBA does not display any error. It creates a new empty variable MyTotl, and the result is 5 (0 + 5) instead of 15 (10 + 5). In a long program, this kind of mistake can take hours to find.
To avoid it, write Option Explicit at the top of the module. With this option, VBA refuses any variable that is not declared, and it shows the typing mistake immediately.
Option Explicit
Sub CalculateTotal()
Dim MyTotal As Long
MyTotal = 10
MyTotal = MyTotal + 5
MsgBox MyTotal
End SubTo declare a variable, you write the keyword Dim, then the name of the variable, then As and its type:
Dim MyTotal As LongDim is short for Dimension. With this line, you tell VBA to reserve a place for the variable before you use it, and to accept only one type of data in it (here, a whole number). That is why Dim starts all the declarations in this article.
💡 To add Option Explicit automatically in every new module: in the VBA editor, Tools › Options, then check Require Variable Declaration.
2. Why declare a variable? It’s like putting each value in the right room 😉
Think of a house. Each object has its place: the plates in the kitchen, the towels in the bathroom, the clothes in the bedroom, the tools in the garden. If you put everything anywhere, the house becomes a big mess, and you never find what you need. All your items are there, but not in the right place.
A program works the same way. When you declare a variable, you tell VBA in which family of data it belongs. There are four main families:
| Family | Type | Example |
|---|---|---|
| Whole numbers | Long | a row number, a quantity: 1, 25, 1000 |
| Decimal numbers | Double | a price, a rate: 12.5, 0.2 |
| Text | String | a name, a city: “Paris” |
| Dates | Date | a delivery date: 11/08/2026 |
Dim MyRow As Long
Dim MyPrice As Double
Dim MyCity As String
Dim MyDate As DateThis classification makes the rest of the program safer. Each variable can only contain values of its family.
3. Real situation: you can’t select row 1.5
Imagine that you store a row number in a variable declared as a decimal number, and that the program receives the value 1.5:
Sub WriteInRow()
Dim MyRow As Double
MyRow = 1.5
Cells(MyRow, 1) = "Hello"
End SubYou ask Excel to write in row 1.5. But no row 1.5 exists in a worksheet: rows are always whole numbers. Excel doesn’t understand your command and crashes 🤯
A row number belongs to the family of the whole numbers, obviously. Declare your row number variable as a Long (no decimals) to ensure your program always has a whole number.
4. Give your variables a clear name
A variable name must say what it contains. When you read the code later, you understand it immediately:
- MyRow for a row number
- MyColumn for a column number
- MyWorkbook for a workbook
There is one exception: the letter i, for index. It is the usual letter for a loop counter, in all programming languages.
Short names like i, x, or n come from the first years of computing. At that time, memory was very limited, and programmers had to save every character. Today, that limit no longer exists. A long name takes a little more time to write, but the code is much easier to read.
But inside a loop, the letter i is still used today by all programmers. So a one-letter name is acceptable for a loop counter, and only for a loop counter. You can also declare it:
Dim i As Long5. The shortcut Ctrl + Space
A long name isn’t a problem when you declare the variable. In the VBA editor, type the first letters of the name, then press Ctrl + Space: VBA shows the list of the names that start with these letters, or completes the name directly.
So you write long, clear names without typing them again each time.