VBA Data Types

The keyword DIM is used to specify the data type of a variable. When Option Explicit is used (as is always recommended), the type of every variable in the program must be specified explicitly. Later it will be seen that DIM is also used to specify an Array data structure. Table 2.2 summarizes some of the VBA built-in data types. Only the types most often used are included. A full list of data types can be viewed via the Help system. 

---

The optional Lower To can be omitted. Type can be Integer, Single, Double, Variant, etc. See the complete list of data types in "VBA Data Types" in chapter 13.) A Variant array can hold values of different data types (e.g., 1.173 and "Billo, E. Joseph"). 

VBA uses a range of different data types. Table 14-5 lists the built-in data types. 

The Variant data type is the default data type in VBA. Like Excel itself, the Variant data type handles and interconverts between many different kinds of data integer, floating point, string, etc. The Variant data type automatically chooses the most compact representation. But if your procedure deals with only one kind of data, it will be more efficient and usually faster to declare the variables as, for example. Integer. 

VBA uses the Variant data type as the default data type for variables and arguments. The Variant data type permits Excel to switch between floatingpoint, integer and string variables as required. 

VBA recognizes three different data types textual, numerical, and logical. There are two types of numbers general numbers and integers, and both can be represented in single or double precision. Note that the spreadsheet always uses double precision, but that VBA needs to be told specifically to do so, otherwise the macro computes in single precision instead. 

---