VBA Variable Types

VBA variables are like an address for the storage of data. Data can be in many forms like numerical or strings or characters etc. So, how does a code know what value or data one can store in which variable? One may do this by different variable types or data types which one may use to store the data as per type. For example, a String variable type will store a string value while an Integer data type will store an Integer value, and so on.

To code efficiently, declaring variables and assigning data types to those declared variables are key to going a long way in VBA coding.  To code efficiently, declaring variables and assigning data types to those declared variables are key to going a long way in

As the name says, the variable will vary from time to time, and we store some value in those variables. To understand this better, let’s remember our “mathematics” classes, where we assume the variable “x = something,” so whenever we use the “x” variable, it would be equal to the value we have assigned.

The data type is the restriction we put on hold the variable. For example, for the declared variable, we can restrict it to hold only “Date Values,” “Integer Values,” “Long Values,” “String Value,” etc.

The data types that a variable may hold are called “Data Type” in VBA.

It has many types. It is important to understand what each data type can hold in coding. We can classify the data types in two ways:

#1 - Non-Numerical Data Types

These data types can hold only non-numerical data. These are common non-numerical data types: String, Boolean, Variant, and Object.

• String: This can hold two kinds of string values, i.e., a String with a fixed and variable length.
• Boolean: Booleans in VBA are logical values: either TRUE or FALSE.
• Variant: It can hold both numerical and non-numerical data
• Object: Object variables are products of Microsoft. For example, in Excel, objects are “Worksheet,“ “Workbook,“ and “Range.” In addition, Microsoft objects are “MS Word,“ “MS PowerPoint,“ and “MS Outlook.”

#2 - Numerical Data Types

These data types can hold only numerical data. Below are numerical data types: Byte, Integer, Long, Single, Double, Date, Currency, and Decimal.

• Byte: This is a small capacity variable where the declared variable can hold values from 0 to 255.
• Integer: This is the improved version of the Byte data type. It can hold values from -32768 to 32768. If decimal values are assigned, they will convert to the nearest integer value. For example, it will convert 5.55 to 6, and it will convert 5.49 to 5.
• Long: Where Integer data types limit its value at 32768 LONG can hold very long numbers from -2,147,483,648 to 2,147,483,648.
• Single: Single data type can hold two decimal places -3.402823E+38 to -1.401298E-45 for negative values and 1.401298E-45 to 3.402823E+38 for positive values.
• 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 and 4.94065645841247E-324 to 1.79769313486232e+308 for positive values.
• Date: This data type can hold only DATE values.
• Currency: This data type can hold values from -922,337,203,685,477.5808 to 922,337,203,685,477.5807.
• 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.

The most important thing to know is to define the variable during coding. We can define the variable types differently: Implicitly and Explicitly.

#1 - Implicitly

We can declare the VBA variable implicitly, i.e., without using the “DIM” word. Dim stands for “Dimension.” For example, look at the below image.

Code:

Sub Data_Type()
  k = 45
End Sub

#2 - Explicitly

It is a proper way of declaring a variable. We would call it an official and professional way. To declare a variable, we have to use the word “DIM” and assign a data type to the variable.

Code:

Sub Data_Type()
  Dim k As Integer
  k = 45
End Sub

We have defined the variable “k,” as shown in the above image, and assigned the data type as “Integer.”

1. A variable cannot contain any space character.
2. The variable should not contain any special characters except “underscore” (_)
3. The variable should not start with a numerical character.
4. The variable should not directly contain any VBA keywords.

Example #1

To define any variable, we need first to use the word “Dim” followed by a variable name.

Code:

Sub Data_Type()
  Dim var
End Sub

Next, we need to assign a data type once given the variable name. As we discussed above, we can assign any data type.

Code:

Sub Data_Type()
  Dim var As Integer
End Sub

We have assigned the data type as an Integer. So, now you need to remember the limitations of the Integer variable. It can hold values between -32768 to 32768.

Code:

Sub Data_Type()
  Dim var As Integer
  var = 25000
End Sub

In the above image, we have assigned 25000, which is well within reach but entering the value more than the limit will cause an overflow error in VBA.

Code:

Sub Data_Type()
  Dim var As Integer
  var = 35000
End Sub

You can run this code using shortcut key F5 or manually to see the result.

Overflow is the assigned value of a data type that is more than its capacity.

Example #2

Similarly, we cannot assign different values also. For example, we can not assign the “String” value to the integer data type variable. If assigned, we will get a “Type Mismatch Error.”

Code:

Sub Data_Type1()
  Dim var As Integer
  var = "Hii"
End Sub

Now, run this code through shortcut key F5 or manually to see the result.

• We must always use the DIM word to define the variable.
• Before assigning data type, ensure what kind of data you will store.
• Assigning more than the capacity value to the data type causes an overflow error, and assigning a different value to the data type causes a “Type Mismatch Error.”