VBA Variable Types

Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

Excel 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 codingVBA CodingVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more.  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.

VBA-Variable-Types

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA Variable Types (wallstreetmojo.com)

What is Data Type?

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.

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

#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.

How to Define Variable & Assign Data Type in VBA?

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 variableDeclare The VBA VariableVariable declaration is necessary in VBA to define a variable for a specific data type so that it can hold values; any variable that is not defined in VBA cannot hold values.read more 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
VBA Variable Types Implicit Example 1.0.1

#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
Example 1.1.0

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

Rules to Define Variable

  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.

Examples

You can download this VBA Variable Types Excel Template here – VBA Variable Types Excel Template

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
VBA Variable Types Example 1

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
 Example 1.1

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
VBA Variable Types Example 1.2

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 VBAOverflow Error In VBAVBA Overflow Error or "Run Time Error 6: Overflow" occurs when the user inserts a value that exceeds the capacity of a specific variable's data type. Thus, it is an error that results from the overloading of data beyond the desired data type limit of the variable.read more.

Code:

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

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

VBA Variable Types Example1

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 ErrorType Mismatch ErrorWhen we assign a value to a variable that is not of its data type, we get Type mismatch Error or Error code 13. For example, if we assign a decimal or long value to an integer data type variable, we will get this error (Error Code 13) when we run the code.read more.”

Code:

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

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

VBA Variable Types Example 2

Things to Remember

  • 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.”

Recommended Articles

This article has been a guide to VBA Variable Types. Here, we discuss how to define the variable and assign data type in Excel VBA with the help of practical examples and a downloadable Excel template. Below you can find some useful Excel VBA articles: –