In this Article
VBA Data Types – Variables and Constants
This VBA Tutorial will help you understand the concept of what variables and constants are. These are important to know for all programming languages and not just VBA.
If you want to start learning to program, then you have to understand what variables and constants are and how to use them in your code.
What is a Variable?
A variable is a value, that you declare in your code and consequently it is reserved in your computer’s memory and stored. You have to name your variable and it’s good practice to declare the data type of your variable. When you declare the data type, you are telling the program, the type of data that needs to be stored by your variable.
You will use the variable in your code, and the program will also access your variable. The actual value of your variable can change while your code is running.
In VBA, we have to use a Dim statement in order to declare a variable. The way to declare a variable in VBA is shown in the code below:
Sub DeclaringAVariable() Dim product_Name As String Dim number_of_Products as Integer End Sub
Once you’ve made your declaration statement, you can initialize your variable, since declaring a variable just reserves space in the memory of your computer. When you initialize your variable you assign an initial value for your variable. The way to initialize a variable in VBA is shown in the code below:
Sub InitializingAVariable() Dim number_of_Products As Integer number_of_Products = 5000 End Sub
In terms of how to name your variable in VBA, you need to ensure that:
• It is not a reserved keyword. VBA has certain reserved keywords such as Dim, Private, Function, Loop and other keywords that you will use in your code and you cannot name your variable after a keyword.
• You don’t use special characters such as !, @, &, ., # or spaces when naming your variables.
• The name of your variable cannot be more than 255 characters in length.
• You also cannot start a variable name with a number.
The Different Common Data Types Available in VBA
There are many data types you can use in VBA. However, there are common ones that you will find yourself using for the most part in your code. These are:
• String – this is used to store text values.
• Boolean – this is used to store TRUE or FALSE values.
• Integer – this is used to store whole number values.
• Double – this is used to store numbers with decimals.
• Date – this is used to store dates.
Note: When you store a value in a String data type, you have to use quotation marks. For example:
Dim product_Name as String product_Name = “ABC Product”
Note: When you store a value in a Date data type, you have to use quotation marks. For example:
Dim start_date as Date start_date = “1/4/2019”
Table of All The VBA Data Types
|Data Type||Stored||Range of Values|
|Byte||1 Byte||0 to 255|
|Integer||2 Bytes||-32,768 to 32,767|
|Single||4 Bytes||-3.402823E38 to -1.401298E-45 for negative values, 1.401298E-45 to 3.402823E38 for positive values|
|Long||4 Bytes||-2,147,483,648 to 2,147,483,648|
|Double||8 Bytes||-1.79769313486232e+308 to -4.94065645841247E-324 for negative values, 4.94065645841247E-324 to 1.79769313486232e+308 for positive values.|
|Decimal||14 Bytes||+/-79,228,162,514,264,337,593,543,950,335 for no decimal points,+/-7.9228162514264337593543950335 for 28 places to the right of the decimal|
|Date||8 Bytes||January 1, 100 to December 31, 9999|
|Currency||8 Bytes||-922,337,203,685,477.5808 to 922,337,203,685,477.5807|
|String (variable length)||10 bytes added to the string length||0 to 2 billion characters|
|String (fixed length)||string length||1 to approximately 65,400|
|Object||4 Bytes||Object in VBA|
|Boolean||2 Bytes||True or False|
Using the Variant Data Type
If you’re not sure about the data type of your variable or it’s likely to need to change, then you can use the variant data type.
The variant data type can store any kind of data except the fixed-length String data type. You declare the variant data type in the following way:
Dim myValue as Variant
Using Option Explicit in VBA
When you use Option Explicit in VBA, this means you have to declare all your variables which is a good idea to do. You can ensure Excel always automatically adds Option Explicit in the VBE by going to Tools>Options>Editor and then check Require Variable Declaration.
Using Variables in Your Code
The example below illustrates all the concepts we covered above:
Sub UsingVariablesInYourCode() Dim product_Name As String product_Name = "ABC Product" MsgBox product_Name End Sub
The result is:
What is a Constant?
A constant is similar to a variable but it stores a value that cannot change. The way to declare a constant in VBA is shown in the code below:
Sub DeclaringAConstant() Const NumberofDays = 1 MsgBox NumberofDays End Sub
The result is:
Note: You can declare a constant as Private which means you can use it in its own module only or you can declare a constant as Public which means you can use it in other modules.