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

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:

Address 2 3
0x0039F4Fx 2A 00
= 0x002A = 4210
Address C D E F
0x0039F4Ex 8A 5F CE FF
= 0xFFCE5F8A = -3,252,34210

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.

Address 4 5 6 7 8 9 A B
0x0039F4Ex 11 2D 44 54 FB 21 09 40
= 0x400921FB54442D11    
Mantissa = 0x921FB54442D11 | 2^52
= 0x1921FB54442D11
= 1.100100...00012
= 1.57079632679489510
Exponent = 0x4000x3FF = 0x1 = 110
Sign bit = 0 ⇒ Positive
= + 21 × 1.570796326794895
= 3.1415926535897910
Address C D E F 0 1 2 3
0x0039F4Dx 64 A8 EC 60 A2 AD 0D C1
= 0xC10DADA260ECA864
Mantissa = 0xDADA260ECA864 | 2^52
= 0x1DADA260ECA864
= 1.110110...01002
= 1.8548911844359510
Exponent = 0x4100x3FF = 0x11 = 1710
Sign bit = 1 ⇒ Negative
= – 217 × 1.85489118443595
= -243124.29732638910
= (243124 days before 1899-12-30) + (0.297326389 days)
= 1234-05-06 07:08:09 AM

Memory Utility Functions

#If Win64 Then
Public Const PTR_LENGTH As Long = 8
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[/vb]