VBA Number Types: Decimal


General information about "Decimal"

Decimal is a subtype of Variant

In VBA, the number type Decimal is special. Decimal is not an independent number type. It is a subtype of the type Variant. The type Variant can contain all kind of data: numbers, texts, objects, Null, Empty and so on. Practically, this means that the following VBA code does not work.

The following code does work. You need to use the conversion function CDec() to convert from String to Decimal.

From -79228162514264337593543950335 to 79228162514264337593543950335

A number of the type Decimal is a number that can contain values with up to 29 decimal digits in the range {-79228162514264337593543950335,..., -79228162514264337593543950335}.

The following VBA code works.

When you assign a value outside that is larger than 79228162514264337593543950335 or smaller than -79228162514264337593543950335, the VBA code will crash. At run time, it will generate an overflow error.

Fractional values

A number of the type Decimal can have a fractional part that has at most 28 decimal digits. The total number of digits is 29, at most. Hence, if the non-fractional consist of 9 digits, the fractional part can cosist of at most 20 decimal places. Hence, the smallest number of the type Decimal - in absolute value - is 0.0000000000000000000000000001 (there are 27 zeroes after the decimal sign). In constrast to the number types Single and Double, the number type Decimal is not a floating point number type.

Be careful, the following VBA code does work.

Default

On declaration, by default, VBA assigns the value Empty to the number of the type Variant. In a numerical context, the value Empty behaves as 0.

Type declaration character

In VBA, there is no type declaration character for Decimal.

Bytes?

We are not sure how many bytes a number of the type Decimal takes in the memory in VBA.
In Visual Basic, according to the documentation, a number of the type Decimal takes 16 bytes in the memory. Twelve of these bytes hold an integer value. As 12 bytes represent 96 bits, there are 296-1 = 79228162514264337593543950336 possible bit representations. This corresponds to the largest possible value for a number of the type Decimal minus one, 79228162514264337593543950335. This leaves 4 bytes (or 32 bits) to represent the scaling factor and the sign, which seems a lot as the scaling factor is limited to a number from 1 to 28 and the sign only requires one bit.

Doing +,-,/ etc.

For Decimal, numercial operations are exact, provided that you stay within the range of numbers that contain at most 29 decimal digits and are in the range in the range {-79228162514264337593543950335,..., -79228162514264337593543950335}.

Of course, division by zero is forbidden. This VBA code will also crash.

The range {-79228162514264337593543950335,..., -79228162514264337593543950335} is unimaginably wide, but not endless. Watch out for overflows. This VBA code will crash because the result is outside the range of the type

Decimal.

The following VBA code also overflows but does not crash. It works, even though the numbers overflow and result is not exact.

The power operator ^ doesn't work with Decimal. If you use it, VBA changes the subtype of Variant while doing the calculations.

Calculations using the type Decimal are known to be slower than calculations using the other the number types.

Conversion to Decimal

VBA offers a conversion function CDec() that will try to convert anything into a number of the type Decimal. As, in VBA, Decimal is not an independent number type, you will need it constantly.