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.

Mind the runtime

The VBA runtime manages memory for you

VBA has a runtime. That’s important. It means behind all your code, whether attached to a Microsoft Office file or a free-standing VB6 program, there is another program running all the time handling a bunch of utility stuff for you. Runtimes are also called virtual machines. In fact for most of its life the VB runtime was called “the VB runtime” while the dll that contained it was called msvbvmXX.dll, for “Microsoft Visual Basic Virtual Machine [version number].”

One of the core features of every major runtime / virtual machine (think Java, .NET, Flash, and all the different JavaScript engines) is memory management and data type management. The two go hand in hand, as data types describe how bits and bytes in memory and disk are to be interpreted and used. The same is true with VBA. You can declare and use variables at will. The runtime will handle all the details of actually obtaining a chunk of memory to store the data, using the memory, and then reclaiming that memory. The reclaiming part is crucial. It requires the runtime to keep track of which variables are attached to a chunk of memory and decide automatically when those variables are no longer needed — that is, when they go out of scope.

When you go in and start messing directly with memory and addresses it is easy to confuse the runtime, particularly when the runtime decides it needs to clean up memory. This theme will come up repeatedly in the rest of this article.

Types of variables, types of pointers

A variable is more than just an address. It also contains metadata including the data type and length. And when you essentially deference a variable’s pointer by directly reading the memory at that address you will get different things depending on the type of the of the variable. This means that you cannot naively ask for a “pointer for a variable” and go on you merry way operating “directly” on the “content” of that variable.

Effectively using pointers in VBA depends on understanding how variables of different types are stored. I’ve covered that in detail in another post: VBA Internals: What’s in a Variable. For the rest of this article I’ll assume you read or are otherwise familiar with what’s covered in that article.

Functions for Obtaining Pointers

VBA has three built-in functions for obtaining pointers. A fourth requires a Declare statement.

Function Description
VarPtr VarPtr will always return the address to the content of the variable itself. What is stored there depends on the type of the variable, as described in detail in VBA Internals: What’s in a Variable.
StrPtr StrPtr automatically performs some indirection for you and obtains the address of the 5th byte of a BSTR, the COM Unicode string type that VBA uses behind the scenes. Note this could be multiple levels of indirection if the string itself is stored in a ByRef variant, which is a special type created when a non-Variant variable is passed to a function that takes a ByRef Variant argument.


The declaration of StrPtr explicitly takes a String argument. If you pass something that is not a string, the runtime will perform the typical automatic conversion to create a string that is equivalent to whatever you passed. But that means the pointer you get back will be to a string that was temporarily created for the function call, and then immediately destroyed when the function returns. In other words, the address you get will be nonsense.

ObjPtr ObjPtr also automatically performs some indirection for you and obtains the address of the object in memory — technically the address of the object’s IUnknown interface. The function explicitly takes an argument of type Unknown. Any variable that is not an object type will raise a type mismatch error. As with StrPtr, this function may perform multiple levels of indirection if the variable is a ByRef Variant.
VarPtrArray Due to VBA’s syntax, there is no way to directly pass an array variable to VarPtr. In order to get a pointer to an array variable you must declare an alternate form of VarPtr, traditionally aliased as VarPtrArray. Naturally you could call it anything you want.

VarPtrArray declaration

Here is the declaration for VarPtrArray for Office 2010+:

For older versions of Office, use this declaration:

Examples: Getting Pointers

Here are some quick examples of using the pointer functions in VBA. I don’t delve into the details of working with pointers to arrays and variants here. Those are substantial topics in themselves, and will be covered in future posts. In the examples below, note carefully the difference between the pointers to the variables themselves (obtained through VarPtr) and the pointers to the semantic content of the object, array, and variant variables.