Blog

In the previous section, we became familiar with different parts of the VBA Editor. In this section of our free VBA tutorials, we’ll get to know one of the most fundamental programming concepts: “variables” – boxes in memory that temporarily hold data so we can use them during program execution.

Why Should We Understand Variables?

In any programming language, variables are the heart of program logic. If you don’t know how to properly declare and use variables, your calculation results might be incorrect or your program might crash with errors. Proper understanding of variables is the first step in writing reliable and understandable code.

You’ll use several variables in almost every VBA macro you write; without them, no calculations are possible.

Definition

According to the VBE Glossary, a variable in VBA is a space in memory that has a symbolic name or identifier and is reserved for storing data. A variable is a temporary location in program memory that holds data.

Variables are used to store calculation results and values you assign to form controls. Variables in VBA Excel are one of the most fundamental concepts in writing advanced macros.

The value of a variable can change because a variable is a space in memory that can hold a specific value at any moment. Unlike control properties that have predefined names, variables have no names until you name them.

Before you can use a variable, you must declare it, meaning you inform VBA of the variable’s name and the data type it will hold. Variables can only store data that matches the data type you’ve defined for them.

For example, a variable defined as Byte type cannot hold a String value (though the Variant data type is an exception to this rule).

Simply put: Variables are like “boxes” you create in memory to temporarily store information so you can use them later in your code.

Prerequisite for this section:
Data Types in VBA

Declaring Variables in VBA

The most common command in VBA for declaring variables is the Dim statement. How to use the Dim statement is explained in the example below.

Dim x As Integer 'Variable declaration

In the example above, variable x is declared to the VBA compiler as Integer data type. If you’ve paid attention to the data types page, Integer data is numeric and occupies 2 bytes of space. Therefore, by declaring variable x, a space of 2 bytes named x is allocated.

Variable Usage in Real Excel Macro

Sub ExampleVariable()
    Dim total As Double
    total = Range("B2").Value + Range("B3").Value
    MsgBox "The sum of the two numbers is " & total
End Sub

Rules and Tips for Naming Variables in VBA

Below you can see the rules for naming variables in VBA Excel. VBA follows these rules in variable naming:

• Variable names must start with a letter.
• You can use letters and numbers in variable names.
• Variable names can have between 1 to 255 characters.
• You cannot use spaces, periods (.), exclamation marks (!), or the characters @, &, $, and # in variable names.
• Generally, you should not use names that are identical to built-in Visual Basic functions, commands, methods, and constants or those of the host application. In other words, you cannot use keywords used in VBA architecture.
• You cannot use duplicate names in the same scope. For example, you cannot declare two variables named age in the same procedure.

Correct and Incorrect Examples:

The following code example will help you better understand the difference between valid and invalid names:

'Valid names
Dim studentName As String
Dim total_Amount As Double

'Invalid names
Dim student name As String   'Due to space between words
Dim #total As Integer        'Due to use of invalid character #
Dim 1total As Integer        'Due to variable name starting with number

Practical Tips for Declaring Variables

Here are some important tips to avoid common errors when declaring variables:

• 📌
  • As you know, VBA codes are written in English, so to prevent compilation errors, always use English names when choosing variable names.
  • Place a prefix at the beginning of the variable name that indicates the variable’s data type. This way, you don’t have to refer to the declaration section to find the variable’s data type. To see the list of suggested prefixes, you can view the Data Types in VBA page.
  • The Dim statement can be used both at the module level and within procedures. A variable declared at module level is called a module variable and is accessible to all procedures within the module. (You can find more information about this in the Variable Scope in VBA tutorial.)
• 📌
  • Use meaningful names (like curHighTotal) instead of ambiguous names (like curX1). These meaningful names help document the code.
  • You can use a limited number of special characters in variable names. For safety, only use the underscore character (_) in variable names.
  • VBA is not case-sensitive. So there’s no difference between Age, age, and aGe. However, note that the VBE Editor preserves the case from the first declaration.

Summary of Important Variable Naming Tips:

• ✅ Use English letters for naming.
• ✅ Include data type prefix in variable names (e.g., strName for string).
• ✅ Use meaningful names to increase code readability.
• ✅ Only use underscore character (_) to combine words.
• ✅ Avoid defining duplicate variables in the same scope.

For more information about the Dim command, you can view this page.

Overflow Error in VBA (Error Number 6)

You’ve probably encountered situations where the program stops for no apparent reason and you see an Overflow message… This error occurs when the data value is larger than the variable’s capacity.

If you assign a value to your declared variable that is greater or less than the permitted range of its data type, you will encounter an Overflow error (Error Number 6) and your program will stop.

Therefore, you should always consider two fundamental aspects when determining a variable’s data type: on one hand, choosing a data type with more capacity than needed will unnecessarily occupy memory and slow down your program, and on the other hand, if the space you select is less than the value assigned to the variable, you will face a compiler error.

For example, in the following code, the second line will cause an Overflow error because the permitted range for Byte data type is between 0 and 255.

Dim i As Byte
i = 256

⚠️ Overflow error occurs when a variable’s value exceeds its data type range. To prevent it, use Long in loops.

💡 Reminder: Long data type can hold values between −2,147,483,648 to 2,147,483,647.

Methods of Declaring Variables to the Compiler

In VBA, you can declare variables in two ways:

• Explicit variable declaration
• Implicit variable declaration

In VBA, the declaration type is determined by the Option Explicit command.

Explicit Variable Declaration

To declare variables explicitly, you must use the Option Explicit command. If you use Option Explicit at the beginning of a module, the compiler will accept an identifier as a variable only if the identifier and its type have been declared to the compiler before being used in the VBA code.
The following example shows how to use the Option Explicit command:

Option Explicit 'Setting explicit variable declaration for compiler
Dim x As Byte 'Variable declaration
Private Sub MYsub()
    y = 10' Undeclared variable causes compile-time error
    x = 10' Declared variable won't cause error
End Sub

In line 1 of the above code, Option Explicit tells the compiler that variables must be explicitly declared beforehand. An important point about the Option Explicit command is that this command must appear in every module before any other code in the code window.
In line 2, variable x is declared as Byte type.
Line 3 declares the MYsub subroutine. We will fully discuss subroutines and their roles in later sections.
In line 4, not declaring variable y will cause a compile-time error. This is due to using Option Explicit in line 1. In this line, since variable y hasn’t been declared before use, the compiler shows Compile Error: Variable not defined.
In line 5, variable x, which was previously declared, is assigned a value.
Line 6 indicates the end of the MYsub subroutine that started in line 3.

For more information about the Option Explicit command, view this page.

Implicit Variable Declaration

To declare variables implicitly, simply don’t use the Option Explicit command at the beginning of the module. In this case, the compiler automatically creates variables when they are used in the code and determines their type. To clarify this, you can see the example below:

Private Sub MYsub()
    x = 10'The variable type is determined as numeric based on its value
     y = "Sadegh"'The compiler determines the variable type as non-numeric based on its value
End Sub

In line 2, identifier x is determined by the compiler as a variable of Integer numeric data type based on its value.
In line 3, identifier y is determined by the compiler as a variable of String non-numeric data type.

Note that implicit declaration is not recommended except for small projects and educational purposes, as it significantly reduces your code readability. The Option Explicit command protects your module from undeclared variables and accidental creation of new variables due to typos.
For example, suppose you’ve defined the identifier myVariable as a variable for a project, written about 1000 lines of code, and somewhere you mistakenly typed myVarable. In this case, the compiler identifies myVarable as a new variable and no error occurs.

💬 Tip: In real projects, it’s always recommended to use Option Explicit to prevent typographical errors.

💎 Golden Tips for Professionals

• In large projects, use three-letter prefixes for data types (like intCount, strUser).
• For better performance, choose data types precisely (e.g., use Long instead of Variant).
• If you need public variables between multiple modules, use Public at module level.
• Observing variable scope can save significant amounts of memory.
• In long projects, it’s better to set temporary variables to zero at the end of procedures to reduce memory usage.
• Use the Locals Window tool in VBE to view real-time variable values.

Summary and Next Tutorial

In this tutorial, we learned about the concept of variables, naming rules, declaration methods, and Overflow error. In the next section of VBA tutorials, we’ll learn how performing operations on data and creating expressions using operators affects program performance. In the next tutorial, you’ll learn how to perform your calculations in VBA using operators – an important step toward writing dynamic and professional code.

🚀 To continue your VBA learning journey, don’t miss the next tutorial!

If you found this tutorial helpful, share it with your friends 💬