Variant

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


VBA Internals: Variant Variables and Pointers in Depth

Pointers and memory for Variant variables

In the Component Object Model (COM) Automation framework, the VARIANT structure provides a wrapper for passing around any type of data, and a suite of manipulation functions facilitate using the VARIANT as a platform-level dynamically-typed variable. I say platform-level because the structures, enumerations, and functions that implement VARIANTs exist at the Windows API level. Any language -- including those that are not dynamically typed -- can use the API to accomplish something like dynamic types.

VBA does provide dynamically typed variables, and calls them Variants, just like the supporting structures in the COM API. When writing VBA code you never have to call the API functions like VarAdd or VarXor. The compiler and runtime do it for you behind the scenes. But when you pop the hood and start directly working with the bits and bytes of Variant variables and pointers it's important to know what you're really dealing with -- namely, a COM VARIANT structure.

The details of the layout of the 16 bytes in the VARIANT structure are covered in detail in What's in a variable. The full code of the memory utility functions used in the examples in this post are included in Scalar Variables and Pointers in Depth.

Read more


VBA Internals: What's in a variable

Language designers and runtime authors are always telling us to ignore implementation details. For the most part, including for VBA, that's good advice. But sometimes you need or want know how things really work under the covers. In VBA this often arises when importing windows API functions which require pointers. To really understand pointers in VBA you first have to understand variables in VBA. In this article I'll delve into the gritty implementation details of variables in VBA.

VBA background

Language vs runtime: An aside on terminology

The VBA language and the VB runtime are not technically the same thing. The language is the syntax described by the VBA language specification, while the runtime is a bundle of utility functions that make implementing the language a lot easier. The runtime is a specific COM dll: msvbvmXX.dll for stand-alone classic VB applications, VBEX.dll for Office. You could use the functions in the VB runtime library from any COM-aware language. The actual implementation of VBA is in the compiler included with the last version of Visual Studio before .NET, and with Microsoft Office. These compilers in turn use the VB runtimes for a lot of the implementation work. For the purposes of this article I'll treat the language, the compiler, and the runtime as the same thing. The fact is there isn't any widely distributed implementation of the VBA language apart from Microsoft's VB compiler and runtime.

Read more