Microsoft Office Tutorials and References

In Depth Information

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
Va riant
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.

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