Microsoft made several changes to VBA in Office 2010, all of them targeted at the one major change in the Office 2010 architecture, i.e., the availability of 64-bit Office applications. This note summarizes how the changes affect developers. I imagine there is a comprehensive list somewhere in the microsoft.com universe but I could not find it.
Microsoft upped the version number of VBA to version 7. While most version changes introduce several new features and capabilities, that is not the case here. The only enhancement is support for 64-bit Office systems.
Until now, VBA has included 3 data types for integers. The Byte, a 1 byte data type, stored unsigned numbers between 0 and 255. The Integer, a 2 byte data type, stored signed integers between -32,768 and 32,767. Finally, the Long, a 4 byte data type, stored signed integers between -2,147,483,648 and 2,147,483,647.
VBA7 introduces the LongLong data type. Available only on a 64 bit installation, this data type is an 8 byte (64bit) signed integer that contains values between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807. Note that a sufficiently large number in this data type cannot be stored in an Excel cell without loss of precision because Excel provides only 15 digits of precision.
To support this new data type, Microsoft also introduced the CLngLng function, the VarType constant vbLongLong, and the DefType statement DefLngLng. The type declaration character is ^. This can cause problems while typing VBA statements that use the exponentiation operator, which is also ^.[1]
Microsoft introduced a new data type that is not a true data type but a declaration that VBA interprets differently on 32-bit and 64-bit systems. The LongPtr data type becomes a Long on a 32-bit Office installation and a LongLong on a 64-bit installation. The corresponding data type conversion function is CLngPtr. The corresponding DefType statement is DefLngPtr.
This data type and associated function greatly simplify programming in VBA 7. The developer doesn’t have to worry about different declarations and statements for 32-bit and 64-bit installations. Instead, with the LongPtr, the code will work on both 32-bit and 64-bit installations.
On a 64-bit Office system, a pointer is a 64-bit variable, rather than the 32-bit variable on a 32-bit system. This requires one key change in the declaration of an API entry point. The developer is required to ensure that any pointer passed as an argument to an API function uses a 32-bit variable on a 32-bit system and a 64-bit variable on a 64-bit system. In addition, the developer has to acknowledge that this has been done correctly by using the PtrSafe keyword in the declaration of an API entrypoint. So, for example, the declaration of the CopyMemory routine should read
Public Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias _
"RtlMoveMemory" (destination As Any, source As Any, _
ByVal length As Long)
The developer essentially acknowledges that the pointer size will be appropriate for the installation type. This is truly where the LongPtr flexible data type comes in. If the developer declares a variable as LongPtr then that variable can be used safely on both 32-bit and 64-bit systems!
An example of how LongPtr interacts with the API:
Dim X As LongPtr
X = CLngPtr(Mid(ThisWorkbook.Names(GlblName).RefersTo, 2))
Dim objRibbon As Object
CopyMemory objRibbon, X, Len(X)
Microsoft introduced two compiler constants to support cross-compatibility between 32-bit and 64-bit systems and backwards compatibility with code running on pre-Excel 2010 systems. The Win64 constant is true on a 64-bit system and false otherwise. The VBA7 constant is true on an Office 2010 system and false on a 2007 (or earlier) system.
As it turns out, VBA7 is the more useful constant when it comes to working with 32-bit and 64-bit and Office 2010 and older systems. As shown above, LongPtr (and associated constructs) make code compatible across 32-bit and 64-bit systems. However, these constructs are not known to VBA6 and would result in syntax errors when executed with Excel 2007 VBA or earlier. With the VBA7 compiler constant (and the LongPtr data type) one can write code that works across versions and platforms. One example:
#If VBA7 Then
Public Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias _
"RtlMoveMemory" (destination As Any, source As Any, _
ByVal length As Long)
#Else
Public Declare Sub CopyMemory Lib "kernel32" Alias _
"RtlMoveMemory" (destination As Any, source As Any, _
ByVal length As Long)
#End If
#If VBA7 Then
Dim X As LongPtr
X = CLngPtr(Mid(ThisWorkbook.Names(GlblName).RefersTo, 2))
#Else
Dim X As Long
X = CLng(Mid(ThisWorkbook.Names(GlblName).RefersTo, 2))
#End If
Dim objRibbon As Object
CopyMemory objRibbon, X, Len(X)
Set Glbl = objRibbon
[1] For example, in a 64-bit Office installation, type X=4^4 and the VB Editor will complain about a syntax error. This is because it interprets the caret as a type declaration character and the statement becomes X=(4^)4 – something that is a meaningless VBA statement. So, it becomes necessary to type a space before the caret since X=4 ^4 clarifies that the ^ is an exponentiation operator and not a type declaration character.
