VBA Data Types – Variables and Constants

What is VBA Data Types

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.

15 VBA Data Types Explained

Following is the complete list of data types which you can use in VBA.

Data TypeBytes UsedRange of Values
Byte1 byte0 to 255
Boolean2 bytesTrue or False
Integer2 bytes-32,768 to 32,767
Long (long integer)4 bytes-2,147,483,648 to 2,147,483,647
Single4 bytes-3.402823E38 to -1.401298E-45 for negative values; 1.401298E-45 to 3.402823E38 for positive values
Double8 bytes-1.79769313486231E308 to-4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values
Currency8 bytes-922,337,203,685,477.5808 to 922,337,203,685,477.5807
Decimal14 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
Date8 bytesJanuary 1, 100 to December 31, 9999
Object4 bytesAny Object reference
String (variable-length)10 bytes + string length0 to approximately 2 billion
String (fixed length)Length of string1 to approximately 65,400
Variant (with numbers)16 bytesAny numeric value up to the range of a Double
Variant (with characters)22 bytes + string lengthSame range as for variable-length String
User-definedVariesThe range of each element is the same as the range of its data type

This is strongly recommended to declare the data type for VARIABLES 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 to not to skip specifying data types is to use Option Explicit at the very top of the module before starting any code.

To understand how data types work in real Excel Programming you need to understand variables and constants in depth.

VBA Variable

Variable is like a storage box which 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:

  1. Use the keyword “Dim” in the starting.
  2. Specify a name for the variable.
  3. Use the keyword “As” after the name.
  4. 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 start date in your code Excel will use the date you have assigned to it.

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:

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 to 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:

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”, just like I have used in the below example.

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.

The best way is to create a convention of names which can be readable and makes sense to anyone who is reading it.

VBA Constants

A Constant is a storage box which is itself stored in your system and it can store a value in it for you, but the value which you assign to it can’t be changed during the execution of the code.

In VBA there two different kinds of constants which you can use:

Intrinsic Constants: Those which are built into the VBA language itself. For example, the built-in constant vbOKCancel which you use in a message box.

User-Defined Constants: Those which you can create by assigning a value to it. To create a user-defined constant use the following steps:

  1. Use the keyword “Const” in the starting.
  2. Specify a name for the variable.
  3. Use the keyword “As” after the name.
  4. Specify the “Data Type” for the variable according to the value you want to assign to it.
  5. Equals to “=” sign.
  6. In the end, the value you want to assign to it.

Above is a constant which stores a birthdate.

Now if you think, a birth date is something that is supposed to be fixed and this kind of value you can use a CONSTANT.

Unlike a Variable, while declaring a constant you can you can assign value to it in the same line.

What’s Next