VBA Variable Types

Excel VBA Variable Types

In order to code efficiently, declaring variables and assigning data types to those declared variables are key to go a long way in VBA coding.  In this article, we will show you what is variable, what data type is and how to assign a data type to those variables.

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

What is Data Type in Excel VBA?

In VBA,  Data Type is the restriction we put on hold the variable. For an example for the declared variable, we can restrict to hold only “Date Values”, “Integer Values”, “Long Values”, “String Value” etc…

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

Data Type has many types, In coding, it is important to understand what each data type can hold. We can classify the data types in two ways i.e.

#1 – Non-Numerical Data Types

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

• String: This can hold two kinds of string values in it i.e. String with fixed length and string with variable length.
• Boolean: Booleans in VBA are logical values i.e. either TRUE or FALSE.
• Variant: It can hold both numerical and non-numerical data.
• Object: Object variables are products of Microsoft. For an example in excel objects are “Worksheet, Workbook, Range”. 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 i.e Byte, Integer, Long, Single, Double, Date, Currency, Decimal.

• Byte: This is a small capacity variable where declared variable can hold values from 0 to 255.
• Integer: This is the improved version of the Byte data type. This can hold values from -32768 to 32768. If any decimal values assigned then it will convert to the nearest integer value. For example, 5.55 will be converted to 6 and 5.49 will be converted to 5.
• Long: Where Integer data types limits 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 VBA variable during coding. We can define the VBA variable types in two different ways i.e. Explicitly and Implicitly.

VBA Variable Type #1 – Implicitly

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

Code:

Sub Data_Type()
  k = 45
End Sub

VBA Variable Type #2 – Explicitly

This is a proper way of declaring a variable, I would call it an official and professional way. To declare this VBA 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 you can see in the above image and assigned data type as “Integer”.

Examples of Excel VBA Variable Types

Below are examples of variable types in excel VBA to understand it better.

VBA Variable Types Example #1

Now let’s look practically at some of the examples of VBA variable types. To define any VBA variable, we need to first use the word “Dim” and followed by a variable name.

Code:

Sub Data_Type()
  Dim var
End Sub

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

Code:

Sub Data_Type()
  Dim var As Integer
End Sub

I have assigned data type as Integer. So now you need to remember the limitations of Integer variable i.e. it can hold values between -32768 to 32768.

Code:

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

In the above image I have assigned 25000 which is well within reach, but the moment you enter the value more than the limit, it will cause an overflow error in VBA.

Code:

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

Now you can run this code using shortcut key F5 or manually, to see the result.

Overflow is nothing but the assigned value of data type is more than its capacity.

VBA Variable Types Example #2

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

Code:

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

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

Things to Remember

• Always use the DIM word to define the VBA variable.
• Before assigning data type make sure what kind of data you are going to store in it.
• Assigning more than capacity value to the data type causes overflow error and assigning a different value to the data type causes “Type Mismatch Error”.

Recommended Articles

This has been a guide to VBA Variable Types. Here we discuss how to define the variable and assign data type along with practical examples and downloadable excel template. Below you can find some useful excel VBA articles –

• LOOKUP Function in Excel VBA
• Excel VBA CDate
• VBA Solver
• Public Variables in Excel VBA
• VBA Variant Data Type
• Global Variables in Excel VBA
• Named Range in Excel VBA