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

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
Address 2 3
0x0039F4Fx 2A 00
 
= 0x002A = 4210
Long
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.

Double
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
Date
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