VBA Data Type (Table of Contents)
VBA Data Type
Before we assign any data type in Excel VBA, 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 in VBA
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.
Numeric Data Types & Non-Numeric Data Types.
#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.
- A 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.
- The single data type can hold values only up to 7 digits. For example 1234567, 1.245564.
- The double data type can hold values up to 15 digits. It is just the expansion of the data type Single.
- The currency data type can hold 15 digits before the decimal value and holds 4 digits after the decimal value.
- The 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.
4.6 (247 ratings)
- 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.
- A 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.
- The 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 of Data Type in VBA Excel
For examples to assign a data type in VBA Excel first we need to declare a variable by using the word “Dim”.
Sub DT_Example1() Dim k End Sub
Now using the word “As” we need to assign the data type in Excel VBA. Now I will assign the data type as “Byte”.
Sub DT_Example1() Dim k As Byte End Sub
As we know the data type in Excel VBA that can hold the values from 0 to 255. But to test I will assign the value of more than 255.
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.
Sub DT_Example2() Dim k As Boolean k = 100 MsgBox k End Sub
The VBA data type is assigned as “Boolean” which holds either TRUE or FALSE. But I have assigned the values 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 numerical value and see what happens.
We got the error because “Boolean” cannot accept text values other than TRUE or FALSE.
This has been a guide to VBA Data Type. Here we cover how to assign and declare the top 2 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 –