Just like other programming Languages, Excel VBA also uses different types of Variables. This Article teaches you the following Topics:

What is Variable?

Variable is a Symbolic Names given to a Memory Location which contains a specific or non-specific Information, Data or Value.
Before we get into much details about Variables, I will tell you some Basic Rules about Variables. There are few important thing, you should know about before using Variables:

You can name anything to your Variable but it is always advised to provide a valid name to your variable. By “Valid Name” I mean, Variable Name should be based on What kind of data you want to Store in that variable. This is helpful when you have hundreds and thousands lines of VBA Codes.

For Example:

You want to Declare a Variable which holds a Boolean Flag where the WorkBook is open or not. You can give the variable name as wbIsOpen. Here wb = WorkBook. So by reading the variable name itself, it it will remind me that it will store Boolean value either Yes or No whether WorkBook is Open or Not.

Note:

This was just an example, you can give any understandable name, which help you in remembering this variable and referring it anywhere in the program.

How to Declare a Variable ?

By using a Keyword Dim, you can declare a variable. Dim is Short form of Dimension. Refer the below Syntax, how to declare a variable:

For example, i have a variable name as wbIsOpen of Boolean type.

Dim wbIsOpen As Boolean

In Excel VBA, as soon as you type “As” after variable name, list of all possible variable types available in Excel will be listed in a drop down as shown below. This is called Intellisense

How to Declare variables in VBA

How to declare multiple Variables:

You can repeat the above Syntax multiple times for multiple declaration. There is another way too, to declare multiple variables. Suppose you have 3 Variables of String Type then you can Declare all of them with single Dim Statement.

 Dim Str1 as String, Str2 as String, Str3 as String

You can declare as many variable you want in a single Dim Statement of same Data Type. If you want to declare many variables of different data types in a single Dim Statement, then follow the below Syntax:

Dim Var1 as String, Var2 as Boolean, Var3 as Integer

In Excel VBA, you can use a variable even without declaring it. An undeclared variable also can hold a value or object in Excel VBA But there is a difference between using a declared and undeclared Variable. If you are using variables which have not been declared will store them as the Variant data type. This means that Excel will need to decide each time, the variable is assigned a value, what data type is should be. This makes the processing slow. My advise is to always use declared variable, unless it is required to do so.

Dim Variable1

Dim Variable1 As Variant

Different Types of Variables:

There are many different Types of variables (Data Types). Here I am going to explain few of them.

Byte Data Type

A data type used to hold positive integer numbers ranging from 0 to 255. Byte variables are stored as single, unsigned 8-bit (1-byte) numbers.

Boolean Data Type

A data type with only two possible values, True (-1) or False (0). Boolean variables are stored as 16-bit (2-byte) numbers.

Integer Data Type

A data type that holds integer variables stored as 2-byte whole numbers in the range -32,768 to 32,767. The Integer data type is also used to represent enumerated values. The percent sign (%) type-declaration character represents an Integer in Visual Basic.

Long Data Type

A 4-byte integer ranging in value from -2,147,483,648 to 2,147,483,647. The ampersand (&) type-declaration character represents a Long in Visual Basic.

Single Data Type

A data type that stores single-precision floating-point variables as 32-bit (2-byte) floating-point numbers, ranging in value from -3.402823E38 to -1.401298E-45 for negative values, and 1.401298E-45 to 3.402823E38 for positive values.

Double Data Type

A data type that holds double-precision floating-point numbers as 64-bit numbers in the range -1.79769313486232E308 to -4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values. The number sign (#) type-declaration character represents the Double in Visual Basic.

Date Data Type

A data type used to store dates and times as a real number. Date variables are stored as 64-bit (8-byte) numbers. The value to the left of the decimal represents a date, and the value to the right of the decimal represents a time.

String Data Type

A data type consisting of a sequence of contiguous characters that represent the characters themselves rather than their numeric values. A String can include letters, numbers, spaces, and punctuation. The String data type can store fixed-length strings ranging in length from 0 to approximately 63K characters and dynamic strings ranging in length from 0 to approximately 2 billion characters. The dollar sign ($) type-declaration character represents a String in Visual Basic.

Object Data Type

A data type that represents any Object reference. Object variables are stored as 32-bit (4-byte) addresses that refer to objects. Variant data type A special data type that can contain numeric, string, or date data as well as the special values Empty and Null. The Variant data type has a numeric storage size of 16 bytes and can contain data up to the range of a Decimal, or a character storage size of 22 bytes (plus string length), and can store any character text. The VarType function defines how the data in a Variant is treated. All variables become Variant data types if not explicitly declared as some other data type.

Scope and Lifetime of an Excel Variable

As we know in Excel VBA programming we have Procedures (Functions), Modules and then we have complete workbook level. We can define a variable in all these three sections. variables declared in these 3 sections are having different Lifetime and Scope. I will explain you the Scope and Lifetime of the variables declared in these 3 sections below:

Procedure-Level (Function) Variables

All variables declared at this level are only available to the Procedure that they are within. As soon as the Procedure finishes, the variable is get reset.

Sub Macro ()

Dim MyName as String
	'Your Code Here
End Sub

Module-Level Variables

These are variables that are declared outside the Procedure itself at the very top of any Private or Public Module. See Example below:

Dim MyName as String

Sub Macro ()
    'Your Code Here
End Sub

All variables declared at this level are available to all Procedures that they are within the same Module the variable is declared in. Its value is retained unless the Workbook closes or the End Statement is used.

Workbook Level, or Public Module-Level

These variables are declared at the top of any standard public module, like shown below:

Public MyName as String

All variables dimensioned at this level are available to all Procedures in all Modules. Its value is retained unless the Workbook closes or the End Statement is used.