Collections of Collections in VBA

December 2, 2013 in VBA

In many situations using collections of one kind or another is very useful. Once in a while, you even want to go another step and use collections of collections. Just as there are several types of collections in VBA, there are several ways you can use collections of collections. All of them have benefits and drawbacks. I’ll provide a summary of them here. Nested collection types The three types of nested collections I’ll cover in this article are as follows: Multidimensional arrays Jagged arrays Jagged Collections Multidimensional arrays Good Simple syntax (only one variable) Type safety. All elements of a
…read more

What is Microsoft Access?

August 3, 2013 in Microsoft Access

The fact is, most people don’t understand what Microsoft Access is. That includes most developers. This fact struck me in particular at the start of a recent project overhauling a collection of legacy Access applications. Naturally the end users were pretty murky on what Access was, they just knew they had to open Access to get to their applications. The business analyst who had been managing a lot of configuration and building queries and reports was certainly not a developer, and was not well versed in how Access applications are (or can be) architected. My colleague and lead on the
…read more

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
…read more

VBA Internals: Variant Variables and Pointers in Depth

July 5, 2013 in VBA

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.
…read more

VBA Internals: Array Variables and Pointers in Depth

June 5, 2013 in VBA

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: What’s in a variable – Categories of variables in VBA, by implementation Getting Pointers – Overview of how to obtain pointers in VBA Scalar Variables and Pointers in Depth – Details of numeric scalar variables and pointers String Variables and Pointers in Depth – Details of string variables and pointers In this post, I will cover the details of array variables and pointers. See Scalar Variables and Pointers in Depth for
…read more

VBA Internals: String Variables and Pointers in Depth

May 31, 2013 in VBA

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: What’s in a variable – Categories of variables in VBA, by implementation Getting Pointers – Overview of how to obtain pointers in VBA Scalar Variables and Pointers in Depth – Details of numeric scalar variables and pointers 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.
…read more

VBA Internals: Scalar Variables and Pointers in Depth

May 28, 2013 in VBA

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
…read more

VBA Internals: Getting Pointers

May 15, 2013 in VBA

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
…read more

VBA Internals: Performance of Fixed vs. Dynamic Arrays

May 8, 2013 in VBA

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
…read more

VBA Internals: What’s in a variable

May 6, 2013 in VBA

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.
…read more