VBA Internals: Decimal Variables and Pointers In Depth

July 17, 2013 in VBA

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.

Put another way: Only 14 bytes of a Decimal contain the Decimal value. To make the DECIMAL structure interchangeable with the VARIANT structure, however, the authors of Automation defined the DECIMAL structure (in OleDb.h) as a full 16 bytes, where the first two bytes are reserved. And yet in practice the COM Variant manipulation functions do write on those first two bytes, treating them like the VARTYPE field (called “vt”) defined for all other variations of the VARIANT structure (in OaIdl.h).

IN PRACTICE: We can forget the convoluted typedef of the DECIMAL, and use a pointer to a Variant/Decimal exactly as if it were a pointer to a DECIMAL. Pointers to DECIMALs aren’t terribly useful, but you can use them to directly call the Decimal arithmetic functions included in the Automation API. In VBA this would make for much more complicated code because you would have to declare and use the API functions instead of simply using the built-in arithmetic operators.

Having clearly stated the caveat that I can’t think of any practical benefit of using pointers to DECIMAL structures, I’ll still present an example of using them. A substantial point of this series, after all, is simply to explore the implementation details of VBA, regardless of whether there’s a practical payoff to every insight.


The code for the memory utility functions (Mem_ReadHex and Mem_Copy) is included at the end of Scalar Variables and Pointers in Depth.

Private Declare PtrSafe Function VarDecMul Lib "OleAut32" ( _
    pDecLeft As Any, _
    pDecRight As Any, _
    pDecOut As Any) As Long

Public Sub DecPtrExample()
    Dim decPI As Variant
    Dim decRadius As Variant
    Dim decArea As Variant
    Dim decWork As Variant
    Dim ptrToPI As LongPtr
    Dim ptrToRad As LongPtr
    Dim ptrToArea As LongPtr
    Dim ptrToWork As LongPtr
    Dim hResult As Long
    decPI = CDec("3.14159265358979323846")
    decRadius = CDec("234.0981896230980329")
    ptrToPI = VarPtr(decPI)
    ptrToRad = VarPtr(decRadius)
    ptrToWork = VarPtr(decWork)
    ptrToArea = VarPtr(decArea)
    Debug.Print " ptrToPI  : 0x"; HexPtr(ptrToPI)
    Debug.Print "*ptrToPI  : "; Mem_ReadHex(ptrToPI, 16)
    Debug.Print "*ptrToRad : "; Mem_ReadHex(ptrToRad, 16)
    ' Calculation 1: Using Variant variables ByRef
    ' Directly pass the variants
    ' hResult = VarDecMul(ByVal ptrToRad, ByVal ptrToRad, ByVal ptrToWork)
    hResult = VarDecMul(decRadius, decRadius, decWork)
    If hResult <> 0 Then Debug.Print "Error # : " & Hex$(hResult)
    ' VarDecMul succeeded, but the VB runtime still has decWork
    ' flagged as an Empty type, so it doesn't return a value when read
    Debug.Print "Before setting vt flag:"
    Debug.Print "   *ptrToWork: "; Mem_ReadHex(ptrToWork, 16)
    Debug.Print "   r^2 = " & decWork
    ' If we manually set the vt (variable type) field of the
    ' variant by using our pointer, the VB runtime now reads the
    ' embdedded DECIMAL structure correctly:
    Mem_Copy ByVal ptrToWork, vbDecimal, 2
    Debug.Print "After setting vt flag:"
    Debug.Print "   *ptrToWork: "; Mem_ReadHex(ptrToWork, 16)
    Debug.Print "   r^2 = " & decWork
    ' Calculation 2: Using pointers ByVal
    ' Pass the pointers to the variants (ByVal)
    hResult = VarDecMul(ByVal ptrToPI, ByVal ptrToWork, ByVal ptrToArea)
    If hResult <> 0 Then Debug.Print "Error # : " & Hex$(hResult)
    Debug.Print "Before setting vt flag:"
    Debug.Print "   *ptrToArea: "; Mem_ReadHex(ptrToArea, 16)
    Debug.Print "   Area = " & decArea
    Mem_Copy ByVal ptrToArea, vbDecimal, 2
    Debug.Print "After setting vt flag:"
    Debug.Print "   *ptrToArea: "; Mem_ReadHex(ptrToArea, 16)
    Debug.Print "   Area = " & decArea

End Sub
 ptrToPI  : 0x00E9F2C0
*ptrToPI  : 0x0E00140011000000C6D7A45B5BEBD507
*ptrToRad : 0x0E00100000000000E9CA77AEA1D67C20
Before setting vt flag:
   *ptrToWork: 00001800ED1E13B1B43674E5EEFB3FB4
   r^2 = 
After setting vt flag:
   *ptrToWork: 0E001800ED1E13B1B43674E5EEFB3FB4
   r^2 = 54801.962384811963530955994804
Before setting vt flag:
   *ptrToArea: 000017000D31A13793471AD4D2AFCC8C
   Area = 
After setting vt flag:
   *ptrToArea: 0E0017000D31A13793471AD4D2AFCC8C
   Area = 172165.44243042945028425664403

Decimal Layout

First, to demonstrate the layout of the DECIMAL structure we will look at the byte content of the variable ptrToPI:

Address 0 1 2 3 4 5 6 7 8 9 A B C D E F
0x00E9F2Cx 0E 00 14 00 11 00 00 00 C6 D7 A4 5B 5B EB D5 07
0E 00
= 0x000E = 1410VT_DECIMAL
= 0x14 = 2010 ⇒ 20 digits after decimal
= 0x00 ⇒ Positive
11 00 00 00
= 0x00000011
C6 D7 A4 5B
= 0x5BA4D7C6
5B EB D5 07
= 0x07D5EB5B

1Really the vt field of a VARIANT

To get all the digits, we have to concatenate the three 32-bit parts of the Decimal:

(Digits) = Hi32 & Mid32 & Lo32
= 0x00000011 & 0x07D5EB5B & 0x5BA4D7C6
= 0x0000001107D5EB5B5BA4D7C6
= 31415926535897932384610

Finally, from the scale field we need to count 20 digits from the right to place the decimal. The result is 3.14159265358979323846, as expected.

Decimal Pointers

The two calls to VarDecMul demonstrate that the pointers to the Variants work exactly as if they were pointers to Decimals. Either way, the COM APIs just need 16 free bytes to work with. In both cases, the output Variant is uninitialized when passed to the VarDecMul function, and in both cases the API happily writes the output of the multiplication operation to bytes 2-15 of the output buffer.

To the VB runtime, however, a Decimal variable is not the same as an [empty] Variant variable. So although the calls to Mem_ReadHex show that all the content of the output Decimals have been written, the VB runtime still renders the value of the variables as Empty. We have to manually set the VARTYPE flag in the first two bytes of the VARIANT structure for the Variant variable to be correctly interpreted as a Decimal by the VB runtime. I accomplish this in the example code by directly writing the value of the built-in constant vbDecimal to the address of the pointers to the output variants, which by definition writes to the start of the VARIANT structures represented by the variables.