VBA Internals: Decimal Variables and Pointers In Depth

Decimal Variants

The Decimal type is special. It's a structure that takes 14 bytes. Essentially it's a 96-bit (12-byte) signed integer, plus a 1-byte scaling factor and a 1-byte sign. The scaling works just like the Scale attribute of a SQL Decimal, indicating the number of significant base-10 digits that appear after the decimal. Although the scale is a whole byte, valid values are only 0 to 28. Likewise the only valid values for the sign byte are 0 (positive) or 1 (negative).

Note that all other non-Variant built-in types in VBA take 8 or fewer bytes; they all fit within a single register on a 64-bit platform. But 8 bytes isn't enough for a useful exact decimal type. An ANSI SQL Decimal, in contrast, takes up to 17 bytes. The designers of COM Automation could have taken the approach of making the Decimal a pointer to a variable-length structure, just as a BSTR (String in VBA) is a pointer to a variable-length character buffer. But, presumably to avoid the overhead of an additional pointer indirection, they settled for a compromise by limiting a decimal to 14 bytes and allowing it to fit entirely within the free portion of a 16-byte Variant. As a result, the Decimal type exists only as a sub-type of the Variant. VBA reflects this at a language level by prohibiting declaration of variables explicitly as Decimals, and instead only producing Decimal values through the CDec function.

A "pointer to a Decimal" is thus a tricky thing. Technically, the Automation DECIMAL structure is defined as a 16-byte structure, where the first two bytes are "reserved". This typedef is included in the union typedef of the VARIANT structure, and in practice those first two bytes are still populated with the two-byte VARENUM enumeration value.

Read more