VBA Variable Types

What are Excel VBA Variable Types?

VBA variables store data of different types like numbers, strings, long values etc., known as Excel VBA Variable types. When we define a variable in programming languages, we usually know what kind of data it can hold; hence we should decide its data type when defining it.

For example, if you declare a variable “x = 100,” wherever we use the variable “x” in the same procedure or program, we refer to the value 100. This makes the process of writing VBA coding much simpler. Furthermore, since we are assigned numerical data to the defined variable, we can declare it a suitable data type.

For example, look at the following code.

Sub Declare_Variable_Type()

    Dim MyVariable As Integer 

    MyVariable = 100

    MsgBox MyVariable

End Sub

We have defined the variable “MyVariable” and assigned the data type Integer to it. Next, we have assigned it the value 100. Hence, the variable MyVariable is an Integer variable type.

What is Data Type?

Once we define a variable, we must assign it a data type. Hence, we can call it “VBA Variable Types.” It restricts the data type we assign to the variable and easily handles errors. The data a variable holds depends on the data type we give the defined variable.

Data Type is the restriction on the variable to hold or be assigned a particular value type. For example, we can restrict a declared variable to hold only date values, integer values, long values, String values, etc.

The kind of data a variable can hold is called its “Data Type.”

Excel VBA Variable Types List (Data Types): VBA Variable Types can be segregated into two parts in VBA, i.e.,

1. Numerical Data Types
2. Non-Numerical Data Types

#1 – Numerical Data Types: These data types can hold only numerical data. Below are numerical data types.

Byte, Integer, Long, Single, Double, Date, Currency, Decimal.

1. Byte: This has a small capacity where the declared variable can hold values from 0 to 255.
2. Integer: This is the improved version of the Byte data type. This can hold values from -32768 to 32767. If a decimal value is assigned, it will convert it to the nearest integer value. For an example 5.55 will be converted to 6 and 5.49 will be converted to 5.
3. Long: LONG can hold very long values from -2,147,483,648 to 2,147,483,648.
4. Single: Single data type can hold two decimal places. -3.402823E+38 to -1.401298E-45 for negative values 1.401298E-45 to 3.402823E+38 for positive values.
5. Double: Double data type can hold more than two decimal places, i.e., up to 14 decimal places. -1.79769313486232e+308 to -4.94065645841247E-324 for negative values 4.94065645841247E-324 to 1.79769313486232e+308 for positive values.
6. Date: This data type can hold only DATE values.
7. Currency: This data type can hold values from -922,337,203,685,477.5808 to 922,337,203,685,477.5807
8. Decimal: Decimal data types can hold up to 28 decimal places. It can hold from +/- 79,228,162,514,264,337,593,543,950,335 if no decimal is use +/- 7.9228162514264337593543950335

#2 – Non-Numerical Data Types: These data types can hold only non-numerical data. These are the common non-numerical data types.

String, Boolean, Variant, Object.

1. String: This can hold two kinds of string values, i.e., String with fixed and variable lengths.
2. Boolean: These are logical values, i.e., either TRUE or FALSE.
3. Variant: It can hold both numerical and non-numerical data.
4. Object: Object variables are the products of Microsoft. For example, in Excel, objects are “Worksheet, Workbook, Range.” Other Microsoft objects are “MS Word, MS PowerPoint, and MS Outlook.”

How to Define Variable and assign Data Type in VBA?

VBA variable types can be declared in Excel VBA, i.e., Explicitly and Implicitly. Therefore, we can use these two methods to define and assign a data type to a variable.

However, we must examine the following rules before defining the procedure.

Rules to Define a Variable

• A variable should not contain any space in its name.
• A variable should not contain any special characters except “underscore” (_)
• A variable should not start with a numerical character.
• A variable should not contain any direct VBA keywords. For example, CELLS is a reserved keyword in VBA, so we cannot name the variable “CELLS.”

As we learned, we can define a variable in two ways; let us look at the procedure in detail.

Implicitly defining a Variable: When we define a variable, we generally use the DIM keyword. DIM stands for “Dimension.” However, when we define a variable implicitly, we need not use the Dim keyword.

We type the variable name per the rules defined earlier and assign the value to the variable.

In the above code, “MyValue” is the variable name, and we have assigned the value 50. It is the implicit way of defining and assigning a value to a variable in the same step. However, there are better ways of defining a variable.

Explicitly defining a Variable: We can explicitly define a variable using the DIM keyword. We assign the data type to the variable when using the DIM keyword.

For example, assume we need to assign a text value to the variable, then first, we use the DIM keyword like the following.

After the DIM keyword, enter the variable name (remember the rules).

After entering the variable name, enter the “as” keyword and assign the data type “String” because we will assign a text value to the defined data type.

VBA variable types usually follow the explicit way of defining a variable.

Examples

Let us see examples of declaring Excel VBA variable types and how to work with them in detail.

Example #1 – VBA Variable Types – Integer Data Type

The Integer is one of the data types that we can assign to the declared variable in VBA. Integer can hold the value from -32768 to 32767. Let’s look at how to work around the integer variable.

Step 1: Define a variable using the following DIM statement.

Step 2: Once the variable is named, we must assign a data type. We can assign the data type by entering the word “As.”

Step 3: Next, we need to assign the data type. You can access the variable types by entering the data type name, and the IntelliSense list shows the VBA variable types list.

Step 4: As observed, once we start entering the data type VBA IntelliSense list shows the matching word for the variable types.

Once the data type is successfully assigned, “As” and “Integer” turn blue. It is an indication that we have successfully defined the variable. Any value we can assign for this variable should be an integer.

Step 5: Let’s assign the value 100 to the variable “x.”

Step 6: Show the assigned value of the variable in the message box.

Step 7: Press the F5 key and run the code; we should see a value of 100 in the message box.

Overflow Error with Integer Data Type: Once the data type is assigned to the variable, we must know the errors we may get  in the VBA variable types. For example, look at the following code of Integer data type.

Sub Declare_Variable()
Dim x As Integer
x = 35000
MsgBox x
End Sub

We have assigned 35000 to the integer data type variable. Execute the code, and we will get the following error.

We get the “Run-time error ‘6’:” i.e., “Overflow” error.

It is because the assigned value to the variable is 35000, which is beyond the limit of the integer data type.

Anything outside the range of -32768 to 32767 gives us the overflow error.

Type Mismatch Error with Integer Data Type: Another error with the Integer data type is the mismatch error. For example, look at the following code.

Sub Declare_Variable_Integer()
Dim x As Integer
x = “January”
MsgBox x
End Sub

We have assigned the value “January” to the variable “x,” which has an integer data type. Therefore, when we run this code, we get the following error.

Integer data types can hold numerical values within their specified range. Since we have assigned text to the variable, we have the “Type mismatch” error.

Example #2 – VBA Variable Types – String Variable

A String variable can hold both numerical and text values. However, if we assign it a numerical value, it will be treated as a string, not a numerical value.

For example, look at the following code.

Sub Declare_Variable_String()
Dim x As String
x = “January”
MsgBox x
End Sub

We have assigned the value “January” to the variable. Because we have assigned the “String” data type to the variable, we will get the assigned value in the message box.

Step 1: Assigning some numerical values to the same variable will make it work as usual.

Sub Declare_Variable_String()
Dim x As String
x = 567
MsgBox x
End Sub

Step 2: Run the code. We see the assigned numerical value in the message box, but it will be treated as a string, not a numerical value.

Example #3 – VBA Variable Types – Date Data Type

The VBA variable types list has the Date data type, which accepts only date values.

For example, look at the following code.

Sub Declare_Variable_Date()
Dim x As Date
x = “2023-01-15”
MsgBox x
End Sub

Step 1: The date value is assigned to the variable in double-quotes. It will be treated as a date, not a string value.

Once we execute the code, we get the following date in the message box.

Interestingly, we have assigned the date as “yyyy-mm-dd,” but the result in the message box is in the form of “dd-mm-yyyy”. It is because of the user’s system date settings. Here, the user’s system date format is “dd-mm-yyyy.” Hence, we get the date in the same format.

Step 2: For the date type, we can give the month name instead of the month number. For example, look at the following code.

Sub Declare_Variable_Date()
Dim x As Date
x = “2023-Jan-15”
MsgBox x
End Sub

We gave the month’s name, but this will still work as long as the month name is correct.

Even though we have given the month name, the result is still a numerical month, not the month’s name.

Example #4 – VBA Variable Types – Boolean Data Type for the Variable

Boolean is a data type that accepts either TRUE or FALSE.

For example, look at the following code.

Sub Declare_Variable_Boolean()
Dim x As Boolean
x = True
MsgBox x
End Sub

Step 1: We have defined a variable “x” and assigned it the VBA variable type “Boolean.” In the following line, we have given the value TRUE to it.

Step 2: Let us run the code and see the result.

Step 3: Let’s assign a value other than TRUE or FALSE.

Sub Declare_Variable_Boolean()
Dim x As Boolean
x = “Hi there!”
MsgBox x
End Sub

We assigned the “Hi there!” value instead of Boolean values.

Step 4: When we run this code, we get the following error.

We get the “Type Mismatch” error. It is because the value assigned to variable “x” is not a Boolean value.

Pro Tips – Declare VBA Variable Types in Shortcut

Single Line VBA Variable Types:

When we define two or more variables, it is common to declare them in multiple lines using multiple DIM statements, as shown below.

However, this is a lengthy process, and we can declare variables using only one DIM statement.

Each VBA variable type is separated by a comma followed by the next variable name. It is the best way to declare variables without using the DIM statement multiple times.

Single Line Variable Declaration and Initialization in VBA Variable Types: It is a common practice to declare a variable in the first line and then assign it a value in the following line.

Look at the following code.

There are two lines, one to define a variable, and another, to assign a value to it.  However, we can do both of these in the same line.

First, we have defined the variable and assigned a VBA variable type to it. Then, we entered the colon (:) to end the declaration and used the variable name to assign a value to it.

Important Things to Note

• If we do not assign any data type to the defined variable, it will take the Variant data type by default.
• Be aware of the data type ranges when you assign a value to a variable data type.
• If the value assigned to the numerical data type is not within the limit of the data type, it will result in an overflow error.
• When we define multiple variables in a single line, a comma should separate each variable.
• When we assign a value to the variable in the same line, we should enter a colon (:) between the variable definition and the value assigned to it.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the VBA Variable Types, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to VBA Variable Types. Here we explain how to declare and assign different data type to variables in Excel VBA with examples and downloadable excel template. You may learn more from the following articles –

• VBA 1004 Error
• VBA FileSystemObject
• VBA Set Statement