Databases Reference
In-Depth Information
precision real)
Double (double-
precision real)
8 bytes
Approximately -1.8E308 to 4.9E324
Currency (scaled
integer)
Approximately -922,337,203,685,477.5808 to
922,337,203,685,477.5807
8 bytes
D
ate
8 bytes
1/1/100 to 12/31/9999
Object
4 bytes
Any Object reference
Variable length: 10 bytes + string
length; Fixed length: string length
Variable length: <= about 2 billion (65,400 for Win
3.1) Fixed length: up to 65,400
String
16 bytes for numbers
Number: same as Double
Variant
22 bytes + string length
String: same as String
U
ser-defined
Varies
10.4.1 Variable Declaration
To
declare
a variable means to define its data type. Variables are declared with the
Dim
keyword (or with the keywords
Private
and
Public
, which we will discuss later in this
chapter). Here are some examples:
Dim Name As String
Dim Holiday As Date
Dim Age As Integer
Dim Height As Single
Dim Money As Currency
Dim db as Database
Dim rs as Recordset
The general syntax of a variable declaration is:
Dim VariableName As DataType
If a particular variable is used without first being declared, or if it is declared without a
data type mentioned, as in
Dim
Age
, then VBA will treat the variable as having type
Variant. As we can see from Table 10-1, this is generally a waste of memory, since
variants require more memory than most other types of variables.
For instance, an integer variable requires 2 bytes, whereas a variant that holds the same
integer requires 16 bytes, which is a waste of 14 bytes. It is common to have hundreds or
even thousands of variables in a complex program, and so the memory waste could be
significant. For this reason, it is a good idea to declare all variables.
Perhaps more importantly, much more overhead is involved in maintaining a Variant than
its corresponding String or Integer, for example. This in turn means that using Variants
typically results in worse performance than using an equivalent set of explicit data types.
We can place more than one declaration on a line to save space. For instance, the line:
