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 vari...

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 vari...

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 ...

 

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

Tags: , , , ,
No Comments »

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

Tags: , , , ,
No Comments »

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

Tags: , , ,
No Comments »

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

Tags: , , , , ,
2 Comments »

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

Tags: , , , ,
No Comments »

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

Tags: , , , , ,
No Comments »

Microsoft Access Field Data Type Reference

April 30, 2013 in Microsoft Access

This page lists every possible field data type that a native Access (Access Database Engine) database can contain. It also shows which methods can be used to create fields of the given type. The information here applies to Access 2007+ (the ACE engine) but most information holds true for earlier versions as well. Contents List of Data Types Examining Data Types Creating Fields, by Data Type List of Microsoft Access Data Types Types of Types Officially, there are 13 primary data types in Microsoft Access. See SQL Data Types. There a number of subtypes, however, depending on how an individual
…read more

Tags: , , , , ,
No Comments »

Arrays in VBA – Part 2: Array Assignment Rules

April 29, 2013 in VBA

This is part 2 of a series of articles about arrays in VBA. For an index, see Arrays in VBA. Arrays are powerful tools in VBA. However, the many types of arrays and the fact that in VBA arrays are value types means that assigning array variables is tricky business. In this post I’ll explain all the rules involved. In a future post I’ll dive into the implications of value-type semantics for arrays. This post refers to reference and value types a lot. If you need a refresher on what that means, start with Values and References in VBA. Assignment
…read more

Tags: , , ,
No Comments »

Values and References in VBA

April 25, 2013 in VBA

Visual Basic for Applications has a unique system of data types. One of the most fundamentally important concepts to understand is the difference between values and references in VBA, but it is very difficult to find clear explanations of this concept. I attempt to present one here. In this article I describe the difference between values and references, and between value types and reference types. In another article I will describe how the ByRef and ByVal keywords work. Synonyms When trying to make sense of this article in comparison with other articles and documentation, keep in mind that other sources
…read more

Tags: , ,
No Comments »

The Bang! (Exclamation Operator) in VBA

April 11, 2013 in VBA

One of the most peculiar aspects of Visual Basic for Applications in the ! operator, called the “bang”. It is particularly common in code for Microsoft Access, where you may see things like this: strCriteria = “EmployeeID = ” & Me!txtEmplID Set rst = CurrentDb.OpenRecordSet(“SELECT * FROM Employees WHERE ” & strCriteria) If Not rst.EOF Then Me!txtName = rst!Name Me!txtTitle = rst!Title Me!txtHireDate = rst!HireDate End If rst.Close Set rst = Nothing There’s a lot of confusion about the bang operator, and nowhere have I seen the correct whole story about it. So, I present it here! Definition: The Bang
…read more

Tags: , , , ,
No Comments »