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.
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.
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”.
Rules to Define Variable
- Variable cannot contain any space character.
- The variable should not contain any special characters except “underscore” (_)
- The variable should not start with numerical character.
- The variable should not directly contain any VBA keywords.
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.
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.
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.
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.
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”.
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”.
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