Excel VBA
Introduction To VBA: Macros And Automation Part 1
Language Basics
Data Types
The data types described below are used within both VB and VBA. The type name, a description of the information that it can store, as well as an example of this information will be provided. The full list of data types available in VB can be found HERE on the Microsoft website.
Boolean
Only holds a value of TRUE or FALSE. These values are not considered to be equivalent to the numbers one and zero, and should only be used when logical outputs are the intended result. The default value of this data type is FALSE.
Byte
Contains 8-bit integers ranging from 0 to 255. It can contain various forms of byte values including integer, hexadecimal, and binary forms. The default value of a bye type variable is zero.
Generally, users will choose the integer form unless a specific operation is better suited to one of the other forms.
Char
Stores 16-bit integers ranging from 0 to 65535. Each of these points is representative of a letter, number, or symbol as derived from the Unicode character system. The initial 128 values cover those found on a classic US keyboard, with other values incorporating symbols and special characters used globally. The default value of a char type variable is the character represented by zero.
As an example, the character value of 32 represents a space within a line of text.
Date
Holds a value which represents dates beginning from January 1st of the year 1 AD/CE, up to December 31st of the year 9999 AD/CE. It can be stored using an integer value, or through a literal format. Using a literal format is a much simpler process than determining the date/time value needed in the integer format. The default value of a date type variable is zero.
To save a value as a date literal you must use the following format: #YYYY-MM-DD#
And yes the octothorpe (#) are necessary to save the date in that format!
Decimal
The data type which despite its name can also hold integer values. It can hold any number value up to a total of 29 digits in length. The default value of a decimal variable is zero.
The smallest and largest values which can be contained in a decimal type variable are as follows (assuming the 29 digit limitation is held to):
Smallest – 0.0000000000000000000000000001
Largest – 79,228,162,514,264,337,593,543,950,335
Decimal type variables can be positive or negative, this does not go against the 29 digit limitation.
Double
The more robust variant of the decimal data type. It can hold number values that can go to over 300 digit places in length, including decimal values. This is the numerical data type which is most commonly used due to its versatility, although there are benefits to using their more precise counterparts as well. The default value of a double type variable is zero.
Integer
A distinction from the other numerical data types, the integer format only accepts integer values. They can be positive or negative, but cannot exceed the absolute value of 2,147,483,647. As with the byte data type, integer data types can be store in the decimal, hexadecimal, and binary formats. The default value of an integer type variable is zero.
Generally, users will choose the integer form unless a specific operation is better suited to one of the other forms.
Long
In the same way that the decimal data type has the double data type for larger values, the integer data type has the long data type. Long holds integer values up to an absolute value of 9,223,372,036,854,775,807. As with many of our previous data types, it has a default value of zero, and can be stored in the same formats as the other data types.
Object
The most distinct of the data types, object can refer to data of any other type, as well as defined objects within your application. This includes arrays, classes, and interfaces, but can also refer to class and collection objects. Object is also used when it is unclear what data type the object that is selected will be. Because of this, it can be slower to use in code. It has a default value of nothing, or a null value.
Short
Similar to the integer data type, with the exception that it can only hold values up to 32,768, either positive or negative. It is for variables which hold integers we know will not exceed this limit, and in larger macros/programs it can improve the speed at which the code is executed. It has a default value of zero.
Single
Similar to the double data type, with the exception of a reduced range in both the positive and negative spaces. This data type is limited to values which have up to forty digit places, and has a default value of zero.
String
The traditional text data type. It can hold strings of length zero to approximately two billion. It simplifies the process of text creation by not having to store multiple characters as an array the way the char data type does. The default value of a string is nothing, or a null value, which is not the same as an empty text string (“”).