VBA Data Type
Data Type is the core character of any variable, the data type represents what is the type of value we can store in the variable and what is the limit or the range of values which can be stored in the variable, data types are built-in VBA and user or developer needs to be aware which type of value can be stored in which data type. Data types assign to variables tells the compiler storage size of the variable.
In VBA we have two types of data types i.e. numeric and non-numeric.
Before we assign any data type, first we need to declare the variable name. Variable is a name which holds the value through assigned data type. While giving a name to the variable we need to keep in mind a few things.
We can define the variable in two ways. One is “Implicitly” and another one is “Explicitly”.
- Implicit way of defining a variable is without using the word “DIM” and data type to it. For example, MyValue = 500 is the implicit way of declaring a variable.
- Explicit way of defining a variable is with the word “DIM” and data type associated with it. For example Dim MyValue as Integer.
Top 2 Types of Data Types
To assign the data type to the variable first we need to understand the types of data types. To understand better we can categorize in two slabs.
#1 – Numeric Data Types
These can type can hold only numerical values. The most common numerical data types are Byte, Integer, Long, Single, Double, Currency, and Decimal.
Each of the data types can hold their respective values. Below is a detailed explanation.
- Byte can hold the values from 0 to 255. It cannot hold anything more than 255.
- Integer can hold values from -32768 to 32768. It cannot hold any decimal numbers.
- Long can hold values from -2,147,483,648 to 2,147,483,648.
- Single data type can hold values only up to 7 digits. For example 1234567, 1.245564.
- Double data type can hold values up to 15 digits. It is just the expansion of the data type Single.
- Currency data type can hold 15 digits before the decimal value and holds 4 digits after the decimal value.
- Decimal data type can hold up to 28 decimal places. If you wish to store anything more than 15 decimal places you can use this data type.
#2 – Non-Numeric Data Types
These data types can hold values of anything other than numerical values. More often used non-numeric data types are Variant, String, Boolean, Date, Object.
- String: There are two types of string data type. One which can hold numerical data and another one which can hold non-numerical data.
- String Fixed length can hold characters from 1 to 65400 characters.
- String Variable-length can hold characters from 0 to 2 billion characters.
- Boolean can hold logical result values i.e. TRUE or FALSE.
- Date can hold only date values from January 1, 100 to December 31, 9999
- Object it can hold only objects of Microsoft product. For example Worksheet, Workbook, Range, PowerPoint, Word.
- Variant also has two data types i.e. Text and Numeric.
- Variant Text can hold text values the same as the variable String Variable length.
- Variant Numeric can hold numerical values same as data type Double.
Examples
For examples to assign a data type in VBA Excel first we need to declare a variable by using the word “Dim”.
Code:
Sub DT_Example1() Dim k End Sub
Now using the word “As” we need to assign the data type. Now I will assign the data type as “Byte”.
Code:
Sub DT_Example1() Dim k As Byte End Sub
As we know Byte data type can hold the values from 0 to 255. But to test I will assign the value of more than 255.
Code:
Sub DT_Example1() Dim k As Byte k = 260 End Sub
Now if I run this code we will get the error as “Over Flow”.
Similarly, other data types also can hold values according to their limits.
Now take a look at the below code.
Code:
Sub DT_Example2() Dim k As Boolean k = 100 MsgBox k End Sub
The data type is assigned as “Boolean” which holds either TRUE or FALSE. But I have assigned the value as “100” to the variable “k”.
Run this code and see what happens.
We got the result as “True”.
The reason why we got TRUE because excel treats anything more than 0 as TRUE and treats 0 as FALSE.
Now I will assign non-numerical value and see what happens.
We got the error because “Boolean” cannot accept text values other than TRUE or FALSE.
Recommended Articles
This has been a guide to VBA Data Type. Here we learn how to assign and declare data type in Excel VBA (Numerical and Non-Numerical) along with examples and explanations. You may also have a look at other articles related to Excel VBA –
- Selection Property in VBA
- Split Function Examples in VBA
- VBA Tutorial in Excel
- VBA End Property Selection
- Call Sub in VBA Excel
- ENUM in VBA examples
- Integers in VBA example
- Use SubString in VBA
- ArrayList in VBA
- 3 Courses
- 12 Hands-on Projects
- 43+ Hours
- Full Lifetime Access
- Certificate of Completion
