VBA Data Type

VBA Data Type (Table of Contents)

• VBA Data Type
• Types of Data Types
• Example of Data Type in Excel VBA

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.

• 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”.

Code:

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”.

Code:

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.

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 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.

Recommended Articles

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 –

• 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