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.
Caveats The declaration of |
||||
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 declarationHere 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
Sub PtrExample() Dim lLong As Long Dim sString As String Dim oCollection As Collection Dim aDoubles(10) As Double Dim vDate As Variant lLong = 42 sString = "Hello" Set oCollection = New Collection aDoubles(0) = 3.14159 vDate = Now Dim ptrToLong As LongPtr Dim ptrToStringVar As LongPtr Dim ptrToBSTR As LongPtr Dim ptrToObjVar As LongPtr Dim ptrToObject As LongPtr Dim ptrToArray As LongPtr Dim ptrToArrayData As LongPtr Dim ptrToVarVar As LongPtr Dim ptrToDate As LongPtr ' Pointer to Long variable, which is also the actual Long value ptrToLong = VarPtr(lLong) ' Pointer to String variable ptrToStringVar = VarPtr(sString) ' Pointer to actual string content ptrToBSTR = StrPtr(sString) ' Pointer to Object variable ptrToObjVar = VarPtr(oCollection) ' Pointer to actual object in memory ptrToObject = ObjPtr(oCollection) ' Pointer to array variable ptrToArray = VarPtrArray(aDoubles) ' Pointer to the start of the actual array content ptrToArrayData = VarPtr(aDoubles(0)) ' Pointer to Variant variable ptrToVarVar = VarPtr(vDate) ' Pointer to actual Date value, at offset 8 of VARIANT struct ptrToDate = ptrToVarVar + 8 End Sub |
Hi,
Thanks for writing this, I am currently writing a VBA macro which should probably be writen in C++ or another language. However I am obliged to use VBA. I wrote a program before in C++ which used objects which I declared myself. These were organised together into an object tree. I am not 100% sure but I believe that I need to use pointers in order for my tree to work. Otherwise if I dimension and initialise new objects for each object as well as “the pointers”* to the next branching objects, when I initialize the first object it will try and initialize two more within that object, which will initialize 2 more in each of them etc. etc. basically crashing the computer. *(I say pointers here because in my previous C++ program that is what I used, but before I read this article I thought pointers couldn’t be used in VBA and so these are in my VBA code as full objects.)
Sorry for the long comment, if I could ask one question, when using a pointer to a “user defined” object, is it necessary to point to any particular position in the object memory or is simply pointing to the start ok. I ask because I see in your example above you have written:
' Pointer to Object variable
ptrToObjVar = VarPtr(oCollection)
' Pointer to actual object in memory
ptrToObject = ObjPtr(oCollection)
Can just one of these be used to point to the object, and if so which one. If both need to be used and I wish to point to an object and then call subs & functions in that object how can I do that …
An example of what I mean in C++ would be:
Account *account = new Account();
account->setAccountID(a);
account->setCustomerID(c);
account->setBalance(b);
Without seeing your VBA code it’s hard to know just what isn’t working. I provided an example of getting a pointer to an object in VBA, but in practice I can’t think of a reason why you’d ever use one, because object type variables in VBA are already essentially pointers. I’d highly recommend posting your question on StackOverflow. Just make sure you provide the code that is causing you problems : In this case I believe it is the code in your tree node. If I had to guess, you’ve declared the connected nodes like this:
Dim LeftChild As New TreeNodeDim RightChild As New TreeNode
When really you should be leaving out the “New”:
Dim LeftChild As TreeNodeDim RightChild As TreeNode
Now the LeftChild and RightChild fields are declared but not instantiated to new objects, and thus no infinite cascade.
What a hell of a good job. I mean it: I’ve been reading this article and the others like “What’s in a variable” and those are awesome articles. Do you know? my brain started like “warping” at the possibilities. After a few experiments now I have my “Objects” getting 953 Megabytes of memory for me. I have been programming in VB but in a very different way… AND with the contrains in memory per Module by default (in different Excel versions). THANK YOU for this articles in deep, deep, deep.
And yes, it is very important to grasp –as programmer in VBA– the difference between: “Dim LeftChild As TreeNode” and “Dim LeftChild As New TreeNode” to get much, much memory from VBA.
Warm regards from Mexico.
[…] I am not too familiar with these, but here is a website that may prove of help with scalars: VBA Internals: Getting Pointers | Byte Comb This website discusses e : MS Excel: EXP Function (WS, VBA) As I mentioned before I will be […]
Great article. Thank you.
But I have still problem. I am using RFID device to read label which unique TID is “E2 00 68 06 73 D5 E2 1F”
My DBLVAR is EPClenandEPC received from function below(function is reading RFID label in range info):
Private Declare Function Inventory_G2 Lib “ZK_RFID105.dll” (ByRef ConAddr As Byte, ByVal AdrTID As Byte, ByVal LenTID As Byte, ByVal TIDFlag As Byte, EPClenandEPC As double, ByRef Totallen As Long, ByRef CardNum As Long, ByVal PortHandle As Long) As Integer
DLL description for this result is: “EPClenandEPC: Output, Pointed to the array storing the inventory result. It is the EPC data of tag Reader read. The unit of the array includes 1 byte EPCLen and N (the value of EPCLen) bytes EPC. It is the former high-word, low word in the EPC of each tag. It is the former high-byte, low byte in the each word.”
After function is called I run:
dblVar=EPClenandEPC
MsgBox “dblVar : 0x” & HexPtr(VarPtr(DBLVAR)) & ” : 0x” & Mem_ReadHex(VarPtr(DBLVAR), 9)
Mem_ReadHex(VarPtr(DBLVAR), 9 SHOULD GIVE ME:
E2 00 68 06 73 D5 E2 1F
but unfortunately it gives me:
08 E2 00 68 06 73 D5 E2
where 08 is amount of bytes.
I do not want to have this information because more important for me is last bit “1F”
How to get only E2 00 68 06 73 D5 E2 1F in VBA?
Thank you.
Hi, I am trying to use these principles to help out with a problem automating Excel and Access. From Access I have a table in a database that is opened exclusively by the application. I need some changes that a running Excel macro makes, to update the table in the Access database, however the instance of access that was running “owns” that database exclusively, and so I cannot run injection SQL against it, or whatever it is called – using ADO and the like. So I thought perhaps I could declare and set a recordset, in Access, and get a pointer to the recordset (or table) object – store its memory address somewhere that Excel can reference it – such as in a cell (at this point there is a reference to that Excel object in Access’s memory). My hope is that then Access has a pointer to the same object and if I query its attributes, they will have been changed in memory, and I can write their contents to the ultimate table that I would have preferred Excel were able to update directly, but which it could not because of exclusivity and locking.
I have no idea if this even could work in practice/principle but would love to be pleasantly surprised that this could work without bringing Windows, or either application, to a screeching halt.
… [Trackback]
[…] Find More Informations here: bytecomb.com/vba-internals-getting-pointers/ […]