How to Declare Variables in VBA

Reading time: 3 minutes

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 Sub

VBA 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 Sub

To declare a variable, you write the keyword Dim, then the name of the variable, then As and its type:

Dim MyTotal As Long

Dim 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:

FamilyTypeExample
Whole numbersLonga row number, a quantity: 1, 25, 1000
Decimal numbersDoublea price, a rate: 12.5, 0.2
TextStringa name, a city: “Paris”
DatesDatea delivery date: 11/08/2026
Dim MyRow As Long
Dim MyPrice As Double
Dim MyCity As String
Dim MyDate As Date

This 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 Sub

You 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 Long

5. 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.

Practice with exercises

Related articles

Leave a Reply

Your email address will not be published. Required fields are marked *

How to Declare Variables in VBA

Reading time: 3 minutes

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 Sub

VBA 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 Sub

To declare a variable, you write the keyword Dim, then the name of the variable, then As and its type:

Dim MyTotal As Long

Dim 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:

FamilyTypeExample
Whole numbersLonga row number, a quantity: 1, 25, 1000
Decimal numbersDoublea price, a rate: 12.5, 0.2
TextStringa name, a city: “Paris”
DatesDatea delivery date: 11/08/2026
Dim MyRow As Long
Dim MyPrice As Double
Dim MyCity As String
Dim MyDate As Date

This 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 Sub

You 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 Long

5. 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.

Practice with exercises

Related articles

Leave a Reply

Your email address will not be published. Required fields are marked *