VBA Data Types Summary – Variables and Constants

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

Here are some examples of declaring variables of various types with the Dim keyword:

Sub DeclaringAVariableUsingDim()

Dim productName as String
Dim myNumber as Integer
Dim myRange as Range
Dim wb as Worksheet
Dim myValue as Boolean

End Sub

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.