VBA Data Types – Variables and Constants

Associated Files Download Links

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:

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:

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:

Note: When you store a value in a Date data type, you have to use quotation marks. For example:

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:

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.

VBA Option Explicit

Using Variables in Your Code

The example below illustrates all the concepts we covered above:

The result is:

Message Box showing Product Name

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:

The result is:

Declaring a Constant

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.

You may also like some of this related content...

We covered arrays, static arrays and dynamic arrays in a previous tutorial.  We are going
A VBA Static variable retains its value even when the procedure ends. Declaring a Static
We covered an introduction to variables and constants in our VBA Data Types – Variables and
We have already covered an introduction to variables, constants and VBA data types in an
We are going to cover Global Variables, in this tutorial. A variable in VBA can