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:
HexPtris a quick function to format a
LongPtrin fixed-width hexadecimal format.
Mem_ReadHexreads 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.
My PC is running 64-bit Windows 7, but I am running the 32-bit version of Office 2010. As a result, all my pointers appear 4 bytes wide. If you are running the 64-bit versions of 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.
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.
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
intVar : 0x0039F4F2 : 0x2A00
lngVar : 0x0039F4EC : 0x8A5FCEFF
dblVar : 0x0039F4E4 : 0x112D4454FB210940
datVar : 0x0039F4DC : 0x64A8EC60A2AD0DC1
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:
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:
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.
Memory Utility Functions
#If Win64 Then
Public Const PTR_LENGTH As Long = 8
Public Const PTR_LENGTH As Long = 4
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
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
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[/vb]