Compatibility Between the 32-bit and 64-bit Versions of Office 2010
Summary: For customers working with 2GB or more of data, Microsoft Office 2010 is now available in a 64-bit version. This article discusses issues around the compatibility of the 32-bit version with the new 64-bit version and legacy 32-bit Office applications and their solutions. (7 Printed Pages)
Last modified: April 07, 2011
Applies to: Excel 2010 | Office 2007 | Office 2010 | Open XML | PowerPoint 2010 | SharePoint Server 2010 | VBA | Visual Basic for Applications 7.0 (VBA 7.0) | Word 2010
In this article
Introducing the 32-bit and 64-bit Versions of Microsoft Office 2010
Comparing 32-Bit Systems to 64-Bit Systems
Introducing the VBA 7 Code Base
ActiveX Control and COM Add-in Compatibility
Application Programming Interface Compatibility
Using Conditional Compilation Attributes
Frequently Asked Questions
Published: March 2010
Provided by: Frank Rice, Microsoft Corporation
The Microsoft Office 2010 system is available in both 32-bit and 64-bit versions. The 64-bit version enables you to work with much larger sets of data. This need is especially true when working with large numbers in Microsoft Excel 2010.
With the introduction of the new 64-bit version of Microsoft Office 2010, a new version of Microsoft Visual Basic for Applications (VBA), known as Microsoft Visual Basic for Applications 7.0 (VBA 7), is being released to work with both 32-bit and 64-bit applications. It is important to note that the changes addressed in this article apply only to the 64-bit version of Microsoft Office 2010. Using the 32-bit version of Office 2010 enables you to use solutions built in previous versions of Microsoft Office without modification.
In a default installation of Office 2010, the 32-bit version is installed, even on 64-bit systems. You must explicitly select the Office 2010 64-bit version installation option.
In VBA 7, you must update existing Windows Application Programming Interface (API) statements (Declare statements) to work with the 64-bit version. Additionally, you must update address pointers and display window handles in user-defined types that are used by these statements. This is discussed in more detail in this article as well as compatibility issues between the 32-bit and 64-bit versions of Office 2010 and suggested solutions.
Applications built with the 64-bit version of Office 2010 can reference larger address spaces, and therefore provide the opportunity to use more physical memory than ever, potentially reducing the overhead spent moving data in and out of physical memory.
In addition to referring to specific locations (also known as pointers) in physical memory that an application uses to store data or to store programming instructions, you can also use addresses to reference display window identifiers (known as handles). Depending on whether you are using a 32-bit or 64-bit system determines the size (in bytes) of the pointer or handle.
There are two fundamental issues when you run existing solutions with the 64-bit version of Office 2010:
Native 64-bit processes in Office 2010 cannot load 32-bit binaries. This is expected to be a common issue when you have existing Microsoft ActiveX controls and existing add-ins,
VBA previously did not have a pointer data type and because of this, developers used 32-bit variables to store pointers and handles. These variables now truncate 64-bit values returned by API calls when using Declare statements.
VBA 7 is a new code base, replacing the earlier version of VBA. VBA 7 exists for both the 32-bit and 64-bit versions of Office 2010. It provides two conditional compilation constants: VBA7 and Win64. The VBA7 constant helps ensure the backward compatibility of your code by testing whether your application is using VBA 7 or the previous version of VBA. The Win64 constant is used to test whether code is running as 32-bit or as 64-bit. Both of these compilation constants are demonstrated later in this article.
With certain exceptions shown elsewhere in this article, the macros in a document (this also includes workbook and presentations) that have worked by using the 32-bit version of that application will work when the document is loaded in the 64-bit version of the same application.
Existing 32-bit ActiveX controls, both third-party and Microsoft-supplied, are not compatible with the 64-bit version of Office 2010. For ActiveX controls and COM objects, there are three possible solutions:
If you have the source code, you can generate a 64-bit version yourself,
You can contact the vendor for an updated version,
You can search for an alternative solution.
Native 64-bit processes in Office 2010 cannot load 32-bit binaries. This includes the common controls of MSComCtl (TabStrip, Toolbar, StatusBar, ProgressBar, TreeView, ListViews, ImageList, Slider, ImageComboBox) and the controls of MSComCt2 (Animation, UpDown, MonthView, DateTimePicker, FlatScrollBar).These controls were installed by previous versions of Microsoft Office and are installed by 32-bit Office 2010. An alternative must be found for existing Microsoft Office VBA solutions that utilize these controls when the code is migrated to 64-bit Office 2010. 64-bit Office 2010 does not provide 64-bit versions of the Common Controls.
The combination of VBA and type libraries gives you lots of functionality to create Microsoft Office applications. However, sometimes you must communicate directly with the computer’s operating system and other components such as when you manage memory or processes, when working with the user interface such as windows and controls, or when modifying the Windows registry. In these scenarios, your best option is to use one of the external functions that are embedded in dynamic linked library (DLL) files. You do this in VBA by making API calls using Declare statements.
Microsoft provides a Win32API.txt file which contains 1,500 Declare statements and a tool to cut and paste the Declare statement that that you want into your code. However, these statements are for 32-bit systems and must be converted to 64-bit by using the information discussed later in this article. Existing Declare statements will not compile in 64-bit VBA until they have been marked as safe for 64-bit by using the PtrSafe attribute. You can find samples of this type of conversion at Excel MVP Jan Karel Pieterse’s Web site at: http://www.jkp-ads.com/articles/apideclarations.asp.
The Office Code Compatibility Inspector user’s guide is a useful tool to inspect the syntax of API Declare statements for the PtrSafe attribute, if needed, and the appropriate return type.
Declare statements resemble one of the following, depending whether you are calling a subroutine (which has no return value) or a function (which does have a return value).
Public/Private Declare Sub SubName Lib "LibName" Alias "AliasName" (argument list) Public/Private Declare Function FunctionName Lib "Libname" alias "aliasname" (argument list) As Type
The SubName function or FunctionName function is replaced by the actual name of the procedure in the DLL file and represents the name that is used when the procedure is called from VBA code. You can also specify an AliasName argument for the name of the procedure, if desired. The name of the DLL file that contains the procedure being called follows the Lib keyword. And finally, the argument list contains the parameters and the data types that must be passed to the procedure.
The following Declare statement opens a subkey in the Windows registry and replaces its value.
Declare Function RegOpenKeyA Lib "advapi32.dll" (ByVal Key As Long, ByVal SubKey As String, NewKey As Long) As Long
The Windows.h (window handle) entry for the RegOpenKeyA function is as follows:
In Microsoft Visual C and Microsoft Visual C++, the previous example compiles correctly for both 32-bit and 64-bit. This is because HKEY is defined as a pointer, whose size reflects the memory size of the platform that the code is compiled in.
In previous versions of VBA, there was no specific pointer data type so the Long data type was used. And because the Long data type is always 32-bits, this breaks when used on a system with 64-bit memory because the upper 32-bits may be truncated or may overwrite other memory addresses. Either of these situations can result in unpredictable behavior or system crashes.
To resolve this, VBA now contains a true pointer data type: LongPtr. This new data type enables you to write the original Declare statement correctly as:
Declare PtrSafe Function RegOpenKeyA Lib "advapire32.dll" (ByVal hKey as LongPtr, ByVal lpSubKey As String, phkResult As LongPtr) As Long
This data type and the new PtrSafe attribute enable you to use this Declare statement on either 32-bit or 64-bit systems. The PtrSafe attribute indicates to the VBA compiler that the Declare statement is targeted for the 64-bit version of Office 2010. Without this attribute, using the Declare statement in a 64-bit system will result in a compile-time error. Note that the PtrSafe attribute is optional on the 32-bit version of Office 2010. This enables existing Declare statements to work as they always have.
The following table provides more information on the new qualifier and data type already discussed as well as another data type, two conversion operators, and three functions.
Indicates that the Declare statement is compatible with 64-bits. This attribute is mandatory on 64-bit systems.
A variable data type which is a 4-bytes data type on 32-bit versions and an 8-byte data type on 64-bit versions of Office 2010. This is the recommended way of declaring a pointer or a handle for new code but also for legacy code if it has to run in the 64-bit version of Office 2010. It is only supported in the VBA 7 runtime on 32-bit and 64-bit. Note that you can assign numeric values to it but not numeric types.
This is an 8-byte data type which is available only in 64-bit versions of Office 2010. You can assign numeric values but not numeric types (to avoid truncation).
Converts a simple expression to a LongPtr data type.
Converts a simple expression to a LongLong data type.
Variant converter. Returns a LongPtr on 64-bit versions, and a Long on 32-bit versions (4 bytes).
Object converter. Returns a LongPtr on 64-bit versions, and a Long on 32-bit versions (4 bytes).
String converter. Returns a LongPtr on 64-bit versions, and a Long on 32-bit versions (4 bytes).
The follow example shows how to use some of these items in a Declare statement.
Declare PtrSafe Function RegOpenKeyA Lib "advapi32.dll" (ByVal Key As LongPtr, ByVal SubKey As String, NewKey As LongPtr) As Long
Note that Declare statements without the PtrSafe attribute are assumed not to be compatible with the 64-bit version of Office 2010.
As stated earlier, there are two new conditional compilation constants: VBA7 and Win64. To ensure backward compatibility with previous versions of Microsoft Office, you use the VBA7 constant (this is the more typical case) to prevent 64-bit code from being used in the earlier version of Microsoft Office. For code that is different between the 32-bit version and the 64-bit version, such as calling a math API which uses LongLong for its 64-bit version and Long for its 32-bit version, you use the Win64 constant. The following code demonstrates the use of these two constants.
#if Win64 then Declare PtrSafe Function MyMathFunc Lib "User32" (ByVal N As LongLong) As LongLong #else Declare Function MyMathFunc Lib "User32" (ByVal N As Long) As Long #end if #if VBA7 then Declare PtrSafe Sub MessageBeep Lib "User32" (ByVal N AS Long) #else Declare Sub MessageBeep Lib "User32" (ByVal N AS Long) #end if
To summarize, if you write 64-bit code and intend to use it in previous versions of Microsoft Office, you will want to use the VBA7 conditional compilation constant. However, if you write 32-bit code in Office 2010, that code works as is in previous versions of Microsoft Office without the need for the compilation constant. If you want to ensure that you are using 32-bit statements for 32-bit versions and 64-bit statements for 64-bit versions, your best option is to use the Win64 conditional compilation constant.
The following code is an example of legacy VBA code that needs to be updated. Notice the data types in the legacy code that are updated to use LongPtr because they refer to handles or pointers
Legacy VBA Code
Declare Function SHBrowseForFolder Lib "shell32.dll" _ Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long Public Type BROWSEINFO hOwner As Long pidlRoot As Long pszDisplayName As String lpszTitle As String ulFlags As Long lpfn As Long lParam As Long iImage As Long End Type
New VBA Code
#if VBA7 then ' VBA7 Declare PtrSafe Function SHBrowseForFolder Lib "shell32.dll" _ Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long Public Type BROWSEINFO hOwner As LongPtr pidlRoot As Long pszDisplayName As String lpszTitle As String ulFlags As Long lpfn As LongPtr lParam As LongPtr iImage As Long End Type #else ' Downlevel when using previous version of VBA7 Declare Function SHBrowseForFolder Lib "shell32.dll" _ Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long Public Type BROWSEINFO hOwner As Long pidlRoot As Long pszDisplayName As String lpszTitle As String ulFlags As Long lpfn As Long lParam As Long iImage As Long End Type #end if Sub TestSHBrowseForFolder () Dim bInfo As BROWSEINFO Dim pidList As Long bInfo.pidlRoot = 0& bInfo.ulFlags = &H1 pidList = SHBrowseForFolder(bInfo) End Sub
The addition of a 64-bit version of Office 2010 enables you to move more data around for increased capability. When writing 32-bit code, you can use the 64-bit version of Microsoft Office without any changes. However, when you write 64-bit code, you should ensure that your code contains specific keywords and conditional compilation constants to ensure that the code is backward compatible with earlier version of Microsoft Office, and that the correct code is being executed if you mix 32-bit and 64-bit code.