Page 87 - Microsoft Office Excel 2003 Programming Inside Out

VBA Programming Starter Kit

Handling Variants and Data Type Conversions
Variants are the catchall data type of VBA. If a variable is declared but not typed, it will be
cast as a Variant data type. Variants can hold any type of data except for fixed-length strings
and must be used for certain data types, such as Currency.
When working with variants, VBA will attempt to use it as the data type that best fits the data
being stored in the variable, but it might not always pick the right type. Assigning a variant
the value of 64 * 1024 produces an error message. Because both operands, 64 and 1024, are
considered Integer data types by VBA, it tries to store the result as an Integer. The actual
result, 65536, is too large for an Integer data type and produces the error message.
To avoid having VBA pick the wrong data type, you can convert values to a specific data type.
In the preceding example, by explicitly converting one of the values to the Long data type,
VBA will cast the result as a Long also and avoid the error.
VBA provides functions to convert values to any numeric data type. When converting from
one data type to another, keep in mind the level of precision that is used by each type and the
limitations of each data type. For example, converting a Single variable, which has a decimal
component, to a Long, which does not, will result in losing the decimal portion of the value,
and converting the variable back to a Single will not restore the decimal portion of the
original value.
lngOffset = CLong(sngOffset)

A list of the conversion functions along with the type of data returned and rounding rules is
provided in Table 4-8. The expression argument provided to the functions can be any
numeric or string expression. String expressions need to be in a format that can be recog­
nized as a numeric value, but the specific format varies from region to region. Chapter 4

Table 4-8. Data Type Conversion Functions
Function Result Remarks
CBool(expression) True/False (-1/0) Any non-zero expression will result in True (-1).
CByte(expression) Byte .5 will round to the nearest even integer.
CCur(expression) Currency Rounding to four decimal places. Five
decimals and greater is undocumented and
might return unexpected results.
CDate(expression) Date/Time€ Numeric expressions will return a date
matching number of days from January 1,
100. String expressions will return an
interpreted date.
CDbl(expression) Double€ Rounding to the nearest floating-point number
within range.
CDec(expression) Decimal€ Rounding to a variable number of decimal
places dependent upon size of number.
continued
61
Part 2: Visual Basic for Applications