Variable Type

Computers store different types of data in different ways. The way a number is stored is quite different from the way text, or a character string, is stored. Different categories of numbers are also stored in different ways. An integer (a whole number with no decimals) is stored differently from a number with decimals. Most computer languages require that you declare the type of data to be stored in a variable. VBA does not require this, but your code will be more efficient if you do declare variable types. It is also more likely that you will discover any problems that arise when data is converted from one type to another, if you have declared your variable types.

The following table has been taken directly from the VBA Help files. It defines the various data types available in VBA and their memory requirements. It also shows you the range of values that each type can handle:

Data type

Storage size



1 byte

0 to 255


2 bytes

True or False


2 bytes

(long integer)

4 bytes

-2,147,483,648 to 2,147,483,647


(single-precision floating-point)

4 bytes

-3.402823E38 to -1.401298E-45 for negative values; 1.401298E-45 to 3.402823E38 for positive values


(double-precision floating-point)

8 bytes

-1.79769313486231E308 to -4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values

Data type

Storage size


Currency (scaled integer)

8 bytes

-922,337,203,685,477.5808 to 922,337,203,685,477.5807


14 bytes

+/-79,228,162,514,264,337,593,543,950,335 with no decimal point; +/-7.9228162514264337593543950335 with 28 places to the right of the decimal; the smallest non-0 number is +/-0.0000000000000000000000000001


8 bytes

January 1, 100 to December 31, 9999


4 bytes

Any Object reference



10 bytes + string length

0 to approximately 2 billion characters

String (fixed-length)

Length of string

1 to approximately 65,400 characters

Variant (with numbers)

16 bytes

Any numeric value up to the range of a Double

Variant (with characters)

22 bytes + string length

Same range as for variable-length String

User-defined (using Type)

Number required by elements

The range of each element is the same as the range of its data type

If you do not declare a variable's type, it defaults to the Variant type. Variants take up more memory than any other type because each Variant has to carry information with it that tells VBA what type of data it is currently storing, as well as store the data itself.

Variants use more computer overhead when they are processed. VBA has to figure out what types it is dealing with and whether it needs to convert between types in order to process the number. If maximum processing speed is required for your application, you should declare your variable types, taking advantage of those types that use less memory when you can. For example, if you know your numbers will be whole numbers in the range of -32000 to +32000, you would use an Integer type.

Declaring Variable Type

You can declare a variable's type on a Dim statement, or related declaration statements such as Public. The following declares Sales to be a double precision floating-point number:

Dim Sales As Double

You can declare more than one variable on a Dim:

Dim SalesData As Double, Index As Integer, StartDate As Date

The following can be a trap:

Dim Col, Row, Sheet As Integer

Many users assume that this declares each variable to be Integer. This is not true. Col and Row are Variant because they have not been given a type. To declare all three as Integer, the line should be as follows:

Dim Col As Integer, Row As Integer, Sheet As Integer

Declaring Function and Parameter Types

If you have input parameters for sub procedures or function procedures, you can define each parameter type in the first line of the procedure as follows:

Function IsHoliday(WhichDay As Date)

Sub Marine(CrewSize As Integer, FuelCapacity As Double)

You can also declare the return value type for a function. The following example is for a function that returns a value of True or False:

Function IsHoliday(WhichDay As Date) As Boolean


You have seen that many intrinsic constants are built into VBA, such as vbYes and vbNo, discussed previously. You can also define your own constants. Constants are handy for holding numbers or pieces of text that do not change while your code is running, but that you want to use repeatedly in calculations and messages. Constants are declared using the Const keyword, as follows:

Const Pi = 3.14159265358979

You can include the constant's type in the declaration:

Const Version As String = "Release 3.9a"

Constants follow the same rules regarding scope as variables. If you declare a constant within a procedure, it will be local to that procedure. If you declare it in the declarations section of a module, it will be available to all procedures in the module. If you want to make it available to all modules, you can declare it to be Public as follows:

Public Const Error666 As String = "You can't do that"

Variable Naming Conventions

You can call your variables and user-defined functions anything you want, except where there is a clash with VBA keywords and function names. However, many programmers adopt a system whereby the variable or object type is included, in abbreviated form, in the variable name, usually as a prefix, so instead of declaring:

Dim SalesData As Double you can use:

Dim dSalesData As Double

Wherever dSalesData appears in your code, you will be reminded that the variable is of type Double. Alternatively, you could use this line of code:

Dim dblSalesData As Double

For the sake of simplicity, this approach has not been used so far in this chapter, but from here onward, the examples will use a system to create variable names. This is the convention used in this book:

□ One-letter prefixes for the common data types:

Dim iColumn As Integer Dim lRow As Long Dim dProduct As Double Dim sName As String Dim vValue As Variant Dim bChoice As Boolean

□ Two- or three-letter prefixes for object types:

Dim objExcel As Object Dim rngData As Range Dim wkbSales As Workbook

In addition to these characters, a lowercase a will be inserted in front of array variables, which are discussed later in this chapter. If the variable is a module-level variable, it will also have a lowercase m placed in front of it. If it is a public variable, it will have a lowercase g (for global) placed in front of it. For example, malEffect would be a module-level array variable containing long integer values.

0 0

Post a comment