In VBA, a Variable is like a storage box that is itself stored in your system but it can store a value in it for you and you can use that value in your code and can change that value if you want (as the name suggests, “VARIABLE” is something whose value is not fixed).
To declare a variable you need to follow a simple procedure:
- Use the keyword “Dim” in the starting.
- Specify a name for the variable.
- Use the keyword “As” after the name.
- Specify the “Data Type” for the variable according to the value you want to assign to it.
Here we have used the name “startDate” and specified the data type “Date”. After declaring a variable you can assign a value to it.
Dim startDate As Date startDate = “11/10/2018”
Now, whenever you use the start date in your code Excel will use the date you have assigned to it.
In VBA, Data Types are basically the types of data that you need to specify while storing a value in a variable or a constant. For example, if you want to store a date in a variable, you must declare that variable date as its data type in the first place.
Data Types Explained
Following is the complete list of data types which you can use in VBA.
|Data Type||Bytes Used||Range of Values|
|Byte||1 byte||0 to 255|
|Boolean||2 bytes||True or False|
|Integer||2 bytes||-32,768 to 32,767|
|Long (long integer)||4 bytes||-2,147,483,648 to 2,147,483,647|
|Single||4 bytes||-3.402823E38 to -1.401298E-45 for negative values; 1.401298E-45 to 3.402823E38 for positive values|
|Double||8 bytes||-1.79769313486231E308 to-4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values|
|Currency||8 bytes||-922,337,203,685,477.5808 to 922,337,203,685,477.5807|
|Decimal||14 bytes||+/-79,228,162,514,264,337,593,543,950,335 with no decimal point;+/-7.9228162514264337593543950335 with 28 places to the right of the decimal|
|Date||8 bytes||January 1, 100 to December 31, 9999|
|Object||4 bytes||Any Object reference|
|String (variable-length)||10 bytes + string length||0 to approximately 2 billion|
|String (fixed length)||Length of string||1 to approximately 65,400|
|Variant (with numbers)||16 bytes||Any numeric value up to the range of a Double|
|Variant (with characters)||22 bytes + string length||Same range as for variable-length String|
|User-defined||Varies||The range of each element is the same as the range of its data type|
This is strongly recommended to declare the data type for variable and constants.
When you specify the data type for a variable or a constant it ensures the validity of your data and keeps you from entering an invalid type of data. If you omit the data type, VBA applies the Variant data type to your variable—it’s the most flexible and VBA won’t guess at what the data type should be.
Tip: The best way not to skip specifying data types is to use option explicit at the very top of the module before starting any code.
Explicitly or Implicitly
You can declare a variable either explicitly or implicitly. An explicit declaration means that before you assign a value to a variable you declare it and define its value. The BENEFIT of this is when you define it and its data type VBA always stores data into that format.
Here’s an example:
Dim myDate As Date
So when you store value in this VBA will always apply the date format to that. But with the implicit declaration, you don’t bother with that explicit declaration statement. Instead, you define the value of the variable and use it in the code statement. In the implicit declaration, VBA stores the data in a Variant variable type because you didn’t specify the type.
In other words, if you just use a variable in your code without declaring it, it’s implicit. Below is the example of implicit declaration:
myDate = “11/10/1990”
This is how you simply assign the value to a variable and use it in your code statements. If you ask me, it’s always, I mean always better to declare a variable before you use it. It’s not only good practice but also makes your code run faster and makes it easier to understand.
Well, I have told you that variable is where you can change the value but this is something different. With a normal variable when a procedure gets completed the value stored in the variable would be deleted from VBA’s memory, but, if you don’t want to lose that value, you can make that variable static.
To make a variable static so that it retains the value you just need to use the keyword “Static” instead of using “Dim”.
Naming a Variable
You can name a variable (these rules also applied to the constant and procedure name) as the way you want but there are a few rules you need to follow:
- You can use alphabets, numbers, and some punctuation characters but the first character of the name must be an alphabet.
- Spaces or periods are not valid characters to use in the name but you can use an underscore to make the name readable.
- You can’t use special characters in a name.
- The length of a name can be 254 characters but using a name that doesn’t make sense.
- There are many reserved words, which are words that you can’t use for the name. If you attempt to use one of these words, you get an error message.
- VBA doesn’t differentiate between cases.
At this point, you know about procedures and which procedure is useful in which situation, you also learn about variables how to define their data types.
The next important thing is to understand the scope, which means availability. That’s where it can be used. When you define a Variable, the scope defines if you can use them outside their home procedure or not. There are three ways of defining scopes to variables (constant and even with the entire procedure).
- Procedure-Level: You can only use a variable in the same procedure where you declare it.
- Module-Level (Private): Makes a variable accessible from all the procedures in a module.
- Module-Level (Public): Makes a variable accessible from all the procedures in all the modules.
The default scope of a variable is the procedure (Procedure-Level) in which that variable is declared.
But let me share something from the real world with you.
I work from a co-working space and the place where I sit is on the first floor in the three-floor building and I mostly sit on the same seat every day. So if you declare a variable with the procedure-level scope you can only use it in the same procedure. Just like I sit on the same seat on the same floor every day. But let me tell you more: Even though I sit on the first floor, I can use any seat on other floors too. And in the below snapshot, we have declared the variable “myName” at the top of the module using the keyword Private before starting any procedure and I have used it in all three codes. Think of this module as a building where you have different floors (Procedures) and as you have already declared the variable at the starting of the module you can use any of the procedures in that module. It is called Private Module-level Scope. Now here’s the last thing: My office has branches in different cities all over the country and if I go there I can go to those offices and use any of the seats if I want. So if you have different modules in your workbook you can use a variable in all those modules by declaring it as Public.