Just like other programming Languages, Excel VBA also uses different types of Variables. This Article teaches you following Topics:
1. What is Variable ?
2. Importance of Variable in Excel Programming
3. Important Rules of Variables, you must know, before declaring a Variable
4. Different Types of Variables
5. How to Declare / Initialize and Assign value to a variable
5. Scope and Lifetime of an Excel Variable
What is Variables ?
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:
1) A Variable name must NOT Start with a Number. Numbers can be within the name, but not as the first character.
2) A Variable name can have maximum of 250 characters.
3) A Variable Name can not be same as an Existing KeyWords available in Excel VBA. For Example: For, Next, while, loop etc.
4) Variable Names can not have SPACE between two words, if you want to Name a variable with two or more words. You Can connect two or more words by UnderScore ( _ ) but can not have SPACE.
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:
Dim variable Name As Data type
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 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
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.
To Check out more Excel Macro Tutorials, visit Excel Macro Tutorial