Internals

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: Array Variables and Pointers in Depth

This is the next installment of a series of deep-dives into the structure and implementation of variables in Visual Basic for Applications. For the previous posts, see the following:

In this post, I will cover the details of array variables and pointers. See Scalar Variables and Pointers in Depth for additional background and for the code for the utility functions HexPtr and Mem_ReadHex.

Pointers and memory for array variables

Like strings, arrays in VBA are treated semantically like value types but are implemented as reference types. Also like strings, arrays in VBA are implemented using a COM automation structure. For arrays the supporting COM type is the safe array, which comes with a large group of utility functions.

Read more


VBA Internals: String Variables and Pointers in Depth

This is the next installment of a series of deep-dives into the structure and implementation of variables in Visual Basic for Applications. For the previous posts, see the following:

In this post, I will cover the details of string variables and pointers. See Scalar Variables and Pointers in Depth for additional background and for the code for the utility functions HexPtr and Mem_ReadHex.

Pointers and memory for string variables

Even though string variables are treated semantically as value types, they are reference types by implementation. The contents of a string variable is actually a pointer to another memory location where the actual string characters are stored. With VBA we can either get the address to the variable itself using VarPtr, or we can go straight to the start of the character buffer by using StrPtr. For a variable declared as a String, then, directly reading the memory at the address returned by VarPtr should give you the same pointer value as calling StrPtr.

Read more


VBA Internals: Scalar Variables and Pointers in Depth

In VBA Internals: What’s in a variable, I introduced the types of variables in VBA as categorized by how they are implemented. In VBA Internals: Getting Pointers, I described the functions used to obtain pointers in VBA. Now we'll take the next step: Putting these two ideas together to really get an inside view of variables in VBA.

Memory utility functions

In the examples here, I use two utility functions of my own making:

  • HexPtr is a quick function to format a LongPtr in fixed-width hexadecimal format.
  • Mem_ReadHex reads an arbitrary number of bytes from memory and returns them as a hexadecimal formatted string, with each byte taking two characters.

Example implementations are included at the end of this article.

Read more


VBA Internals: Getting Pointers

Generally speaking you don't use pointers in VBA. There is no language-level syntax to explicitly obtain or dereference a pointer as in C or C++ (such as int* somePtr = &someInt).

Within VBA there is almost no reason at all to use pointers. The VB runtime handles all the implementation details of allocating, using, and reclaiming memory for you. But sometimes you need (or want) to reach out to the power of an API function that actually requires a pointer and you have no choice but to take that plunge. Or maybe (like me) you're just mischevious or curious, and want to dig into all the implementation details that language and runtime designers are constantly telling us programmers to ignore.

Either way, there are in fact ways to obtain and work with pointers in VBA. You can do some powerful things with them. You can also crash your whole program if you don't know what you're doing. Using pointers is a big topic, so in this post I'll just present an overview and a description of the functions used to obtain them.

Read more


VBA Internals: Performance of Fixed vs. Dynamic Arrays

Arrays in VBA can have their dimensions either fixed at compile time or modifiable at runtime. Fixed-length arrays are declared by specifying the bounds in the declaration, while dynamic arrays must be initialized using the ReDim statement before they can be used. (See Types of Arrays for more details).

Given the flexibility of dynamic arrays I have sometimes wondered why anyone would use a fixed-length array. Sometimes it is required for byte buffers or in user defined types for passing to and from API methods. But for straight VB code, is there any advantage?

In particular, what is the performance cost of using a dynamic array? I set to measure exactly that, and I present my results here. I'll give it away from the start: There is essentially no statistical difference in how long it takes to initialize or copy fixed-length arrays vs. dynamic-length arrays. My recommendation, then: Only use fixed-length arrays when an API function or other external limitation requires it!

Methodology

To compare the speed of initializing and copying fixed and dynamic arrays, I used the high-resolution performance timer described in Accurate Performance Timers in VBA. I executed each test 1,000 times and recorded each measurement. I then calculated the overall average and standard deviations of the measurements, and also graphed the histograms of the actual measurements. The code used to actually perform the tests is included at the end of this post.

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