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 aLongPtr
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.
Pointer size
My PC is running 64-bit Windows, but I am running the 32-bit version of Office. As a result, all my pointers appear 4 bytes wide. If you are running a 64-bit version of Office (available starting with Office 2010), your pointers will all be 8 bytes wide. The LongPtr
type in VBA is a nifty compiler trick that automatically gets swapped out for a 4-byte Long
on 32-bit Office but for a 8-byte LongLong
on 64-bit office. This allows code, including the examples provided here, to work the same on both 32- and 64-bit versions of office.
Endianness
Like the vast majority of current workstations, my Intel-driven PC has a little-endian architecture. As a result the raw bytes returned by Mem_ReadHex
appear backwards in some contexts. I account for this in the printouts and diagrams below. If you are running VBA code on a big-endian machine then your Mem_ReadHex
outputs will be flipped around.
Pointers and memory for numerical scalar variables
This post will treat the simplest category: numeric scalar variables. These types of variables are the simplest kind. As noted in VBA Internals: Getting Pointers, the VarPtr
function gives you the address directly to where the value is stored. Thus by reading the memory at that address, we should see the literal binary contents representing the encoded numerical value of the variable.
Code
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
Sub ScalarPointerExamples() Dim intVar As Integer Dim lngVar As Long Dim dblVar As Double Dim datVar As Date intVar = 42 lngVar = -3252342 dblVar = 3.14159265358979 datVar = "1234-05-06 07:08:09" Debug.Print "intVar : 0x"; HexPtr(VarPtr(intVar)); _ " : 0x"; Mem_ReadHex(VarPtr(intVar), 2) Debug.Print "lngVar : 0x"; HexPtr(VarPtr(lngVar)); _ " : 0x"; Mem_ReadHex(VarPtr(lngVar), 4) Debug.Print "dblVar : 0x"; HexPtr(VarPtr(dblVar)); _ " : 0x"; Mem_ReadHex(VarPtr(dblVar), 8) Debug.Print "datVar : 0x"; HexPtr(VarPtr(datVar)); _ " : 0x"; Mem_ReadHex(VarPtr(datVar), 8) End Sub |
Output
intVar : 0x0039F4F2 : 0x2A00
lngVar : 0x0039F4EC : 0x8A5FCEFF
dblVar : 0x0039F4E4 : 0x112D4454FB210940
datVar : 0x0039F4DC : 0x64A8EC60A2AD0DC1
Explanation
In this example we have four variables, all declared as a specific numeric scalar type. From the VarPtr
function, we can see that there is in essence a variable table that looks like this:
Variables | ||
---|---|---|
Name | Type | Address |
intVar | Integer | 0x0039F4F2 |
lngVar | Long | 0x0039F4EC |
dblVar | Double | 0x0039F4E4 |
datVar | Date | 0x0039F4DC |
The format of the 2-byte Integer
and 4-byte Long
types are straightforward, so when we read the contents at the addresses returned by VarPtr
it is immediately clear that we did in fact get the literal numeric value of the variables:
Integer
|
|
Long
|
|
The Double
and Date
are a little more complicated. Both are in fact implementations of a IEEE 754 double-precision floating point number. See Double-precision floating-point format for a detailed explanation of the format. For Dates, VBA takes the integer part of the encoded Double as the number of days offset from the arbitrary starting point of 30-Dec-1899. The fractional portion of the encoded double is always take as the absolute time, as a proportion of an entire day. So in the example below, even though the encoded double value is negative (-243124.297326389 to be precise), the fractional part of the number is treated as positive 0.297326389, which works out to be 7:08:09 AM.
Double
|
|
Date
|
|
Memory Utility Functions
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 |
#If Win64 Then Public Const PTR_LENGTH As Long = 8 #Else Public Const PTR_LENGTH As Long = 4 #End If Public Declare PtrSafe Sub Mem_Copy Lib "kernel32" Alias "RtlMoveMemory" ( _ ByRef Destination As Any, _ ByRef Source As Any, _ ByVal Length As Long) ' Platform-independent method to return the full zero-padded ' hexadecimal representation of a pointer value Function HexPtr(ByVal Ptr As LongPtr) As String HexPtr = Hex$(Ptr) HexPtr = String$((PTR_LENGTH * 2) - Len(HexPtr), "0") & HexPtr End Function Public Function Mem_ReadHex(ByVal Ptr As LongPtr, ByVal Length As Long) As String Dim bBuffer() As Byte, strBytes() As String, i As Long, ub As Long, b As Byte ub = Length - 1 ReDim bBuffer(ub) ReDim strBytes(ub) Mem_Copy bBuffer(0), ByVal Ptr, Length For i = 0 To ub b = bBuffer(i) strBytes(i) = IIf(b < 16, "0", "") & Hex$(b) Next Mem_ReadHex = Join(strBytes, "") End Function |