VBA Data Type

VBA Data Type

Data type is the core character of any variable, it 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 that holds the value through the 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”.

• The 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.
• An explicit way of defining a variable is with the word “DIM” and the 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.

Popular Course in this category

VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings) 3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion

View Course

• 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 types. One which can hold numerical data and another one that 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.
• A 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 the same as data type Double.

Examples to use Various VBA Data Types

For example, to assign a data type 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 the Byte data type can hold the values from 0 to 255. But to test I will assign a 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 VBA Excel (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
• VBA Split
• VBA End
• Call Sub in VBA Excel
• VBA LCase