Data Type VBA – This VBA basic tutorial series will invite you to learn about data types in Visual Basic For Application (VBA) Excel programming.
Actually, after discussing the VBA procedure, I want to continue the discussion about variables and constants, but because the discussion about Variables in VBA will never be separated from data types, I think it would be better if we first got acquainted with the data types in VBA. Without knowing the data type, it will be very difficult to manage variables and constants effectively and efficiently.
After all, the main purpose of VBA is to manipulate data. So having a good understanding of data types will really help you to master Excel VBA. Right?
Understanding and Functions of Excel VBA Data Types
In general, data is stored in computer memory. If you have a basic understanding of computers, I’m sure you’re familiar with how they work.
In the discussion of VBA, data storage is divided into 2 types. Some data is stored in the form of objects and some other data is stored in the form of variables.
Objects are what Visual Basic for Applications manipulates. Examples of objects are Workbooks, Worksheets , cell ranges (ranges) and cells in excel . While the variable is a specific name as a data storage location. Variables are generally used to represent certain values. In other words, a variable is a container for a certain value.
Variables in Visual Basic for Applications are created by making a declaration of a certain statement by specifying the name and characteristics of each of these variables. One of the characteristics you can define is its data type .
Data type is a method used to determine and classify the type of data. This activity is commonly referred to as ” variable declaration “. We will discuss variables separately in the next guide, this time we will focus on discussing data types first.
There are many types of data types that can be used in programming languages. However, every programming language has data types that may not exist in other programming languages.
The main reason why understanding VBA data types is important is to define the characteristics of the variables you create in VBA.
Why is that? because the VBA data type will determine how the data is stored in computer memory. Each of these data types has a different nominal allocation of memory which means it will require a certain number of different bytes for each data type.
The smaller the number of bytes used, the faster VBA execution will be and conversely the more bytes your data uses, the slower your VBA application will run.
Well I think you understand enough why knowing this VBA data type is important to learn. Then what are the data types in Excel VBA?
Excel VBA Data Type
The data types in VBA, although similar, are certainly different from the Excel data types.
Data types (data types) in VBA can be grouped into two parts. Namely, Numeric Data Types or number/ numeric data types and non-numeric data types or data types other than numbers/numeric.
- Numeric data types are data types consisting of numbers, which can be calculated mathematically with various standard operators such as addition, subtraction, multiplication, division and many more. Examples of variables that usually use data of this numeric data type are serial number, height, weight, number of students in class, price of goods, monthly bill, and so on.
- Non-numeric data types are data types that cannot be manipulated mathematically using standard arithmetic operators.
For more details, let’s study one by one.
Numeric Data Types in VBA Excel
VBA data types that fall into this numeric data type are: Byte, Integer, Long, Single, Double, Currency, Decimal .
Byte Data Type
The Byte data type in VBA is a data type that only requires 1 byte of memory capacity. A variable with the VBA data type Byte can be used to store a number between 0 and 255.
Integer Data Type
You can use the Integer data type to store integers between -32,768 and 32,767.
Integer variables only require a capacity of 2 bytes of computer memory. Due to low memory requirements, the Integer data type in VBA can be your most efficient and better choice for storing integers that fall within its range.
Long Data Type
The Long data type is also known as ” Long Integer “. As the name implies, you can use the Long data type in VBA to store integer values that are in a range that is longer than the range of the Integer data type.
Using the Long VBA data type, you can store numbers between -2,147,483,648 and 2,147,483,647.
If this number is not enough you can use the Double data type.
Single Data Type
The Single data type refers to ” single-precision floating-point “, a number format that determines how the computer handles the number.
You can use the Single data type to store numbers in the following ranges:
- Negative values: -3.402823E38 to -1.401298E-45.
- Positive values: 1.401298E-45 to 3.402823E38.
Variables that use a single data type require 4 bytes of computer memory capacity.
Double Data Type
The Double data type can be used to hold integers and fractions. Double means ” Double-precision Floating-point “.
More precisely, you can use the Double type to store floating-point numbers in the following ranges:
- Negative numbers: -1.79769313486231E308 to -4.94065645841247E-324.
- Positive numbers: 4.94065645841247E-324 to 1.79769313486232E308.
Variables that use this data type require 8 bytes of memory, which is 2 times the needs of the Single and Long Integer data types.
Currency Data Type
As the name suggests Currency data type is usually used to store data related to nominal money (monetary). Of course, basically it doesn’t have to be nominal money.
The Currency data type returns a scaled value with an accuracy of up to 15 digits to the left of the decimal point and 4 digits to the right. Use this data type to avoid rounding errors when precision is critical.
Currency variables can be used to store both positive and negative numbers. This data type is stored as a number in an integer format that is scaled to 10,000.
As a consequence, this data type allows for a range of values between -922,337,203,685,477,5808 and 922,337,203,685,477,5807.
Due to the fact that the Currency VBA data type is precise and these variables give fixed-point numbers, Currency is very useful for monetary calculations or fixed-point calculations where accuracy is very important.
The Currency data type requires 8 bytes of memory.
Decimal Data Type
The Decimal data type can be used to store integers measured to a power of 10. This scaling factor varies depending on how many digits are to the right of the decimal point. The maximum number of digits that the Decimal variable can hold is 28.
How many values can this data type hold?
- If it does not contain a decimal value this type can accommodate – 79,228,162,514,264,337,593,543,950,335 to 79,228,162,514,264,337,593,543,950,335.
- If it contains a decimal value this type can be used to accommodate the numbers -7.9228162514264337593543950335 to 7.9228162514264337593543950335.
The VBA Decimal data type provides the largest number of digits to represent a given number. Therefore, this type is more suitable for cases where you perform a large number of calculations that require precision and cannot avoid rounding errors.
The precision of the Decimal data type, comes at a cost in the form of large memory requirements. The VBA Decimal data type takes up 12 bytes, which is larger than other numeric data types.
As Microsoft explains , you can’t declare the Decimal data type directly. Actually, the Decimal type is a sub-type of Variant. Therefore, to use Decimal, you must convert the CDec function .
Non-Numeric Data Types
Included in this data type are string or text data types, Date data types, Boolean data types, Object data types and Variant data types .
String Data Type
In VBA ( Visual Basic for Applications ), the String data type is generally used to store text. However, this does not mean that you should only use letters in String variables. In addition to letters, String variables can contain numbers, spaces, punctuation and certain characters.
There are 2 types of String data types that can be used. The number of characters and memory required varies depending on the type.
1. String-fixed length
Variables that use the Fixed-length String type can contain between 1 and about 64,000 characters. This String variable requires the amount of memory required by the string or the text itself.
2. String-variable length
Variables that use the Variable-length String type can contain anything from 0 to about 2 billion characters. This type requires 10 bytes of memory plus the memory required for the string itself.
Date Data Type
The Date data type or types can be used to store date, time or both values at once (Date and Time). This type can store a value that represents a date between January 1, 100 and December 31, 9999 and/or a time between 00:00:00 (midnight) and 23:59:59.
The memory capacity required by this Date type is 8 Bytes.
Boolean Data Type
The Boolean data type is only used to store one of 2 logical values TRUE or FALSE. This data requires 2 bytes of memory.
In general Boolean data, TRUE is denoted by 1 and FALSE is denoted by 0. However, in VBA, the conversion between Boolean and numeric VBA data types works a little differently:
- When changing a variable of type Boolean to a numeric data type, TRUE becomes -1 and FALSE becomes 0.
- When changing the numeric VBA data type to Boolean, 0 becomes FALSE and all other values (regardless of whether they are negative or positive) become TRUE.
Object Data Type
You can use the Object data type for the purpose of storing addresses that refer to specific VBA objects. This data type requires 4 bytes of memory.
In general, if you create a variable that refers to a specific Excel VBA object, use this data type. Objects in VBA excel are for example Workbooks, worksheets, cells, ranges, and so on.
Variant Data Type
Variant is the default VBA data type. In other words, this is the data type used by VBA(Visual Basic for Applications) when you don’t specify the data type when declaring a variable. In other words, if you don’t specify a data type when you declare a variable, Excel will use a data type that requires 16 or 22 memory capacities.
Yes, this data type does require a larger memory capacity than others, to be precise:
- The variant for numeric data requires 16 bytes of memory.
- The variant for text data requires 22 bytes of memory plus the memory required by the string.
This is one of the reasons why in the first part of the VBA guide I suggested using the explicit option at the beginning of every module you create. Namely so that you practice determining what data types are most appropriate to use for the reasons for the effectiveness of the VBA execution you make.
Choosing the Right Data Type
The general rule that I recommend you use when determining the type of data to use is to choose the data type that uses the smallest number of bytes . Of course, the data type must still be able to handle the range of data you want to use.
The reason for this is as I said at the beginning that the smaller bytes of memory used, the execution of your VBA program will be relatively faster and of course you don’t want your VBA code to run very slowly, right?.
Some other suggestions when choosing the right data type or data type are as follows:
- Use the BOOLEAN type if the Variable you create is used to store logical true/false values or TRUE and FALSE.
- To store text use String type with condition:
- If the number of characters is not fixed then use a String with a regular declaration
- If the number of characters must be kept N characters then use a String with the declaration String *Number of characters
- If the variable you created will be used to store integers use:
- Byte data type for numbers between 0 to 255
- Integer data type for numbers between -32,768 to 32,767 or a maximum of 4 digits
- Long data type for numbers between -2,147,483,648 to 2,147,483,647 or a maximum of 9 digits
- Numbers do not have to be kept precise with the number of digits and can be in the form of fractions ( fractions ) using Single or Double because both have a very wide range.
- If the stored digits must be kept exactly as they are and can be fractions use:
- Currency data type if in the form of money value with an accuracy of 4 digits behind the comma or a maximum of 14 digits.
- Variant data type if the value cannot change (as is) which can reach 28 digits so that it can be filled with values of type Decimal.
- Use the Date data type to store both Date, Time, or Date and time data.
- For the Object data type:
- If you know the object name, use the object name (eg range, worksheet, pivottable , etc.)
- If you don’t know the object name, use Object Type
- If the datatype is uncertain (as much as possible avoided), use the Variant data type because it can change to a datatype according to the data value.
In addition to the suggestions for selecting the data type above, there are several other things you need to pay attention to:
- If the data value to be stored exceeds the specified datatype limit or even has a different datatype, it will produce an error.
- Writing numbers or numbers in VBA always uses English format or uses the dot (.) character as a decimal sign
- Writing date in VBA should use the universal format YYYY-MM-DD for dates and HH:mm:ss. for time
- VBA will try to convert the inputted datatype to match the declared one
- To be able to use the Decimal datatype, the variable or function type procedure must use the Variant datatype which is filled in using the CDec function as described previously.
So first our discussion about VBA Data Type. If something is not clear, please ask in the comments column provided. In the next series of Basic VBA tutorials, we will learn about Constants and Variables in Excel VBA .
Don’t forget if you find the benefits of the guides on this excel tutorial blog, please share and comment so that other Excel users can also feel the benefits.
