I’ve put off writing about x64 MS Office because I was a bit lazy and hadn’t build the necessary environment to test it properly.
In my day-to-day job -as far as Access is concerned-, I have so far targeted the 32 bit (x86) version of Office 2010 but the 64 bit version is bound to become more popular with time and some people already _have_ to use it, even if sometimes their reasons are misguided.
### When should I care?
If your code has any chance of being run on Office 2010 and later, and you want to avoid issues when one of your users or customers will have Office x64 (or just one of its components) installed, then you should make sure to use your code uses the new VBA7 constructs.
The changes to be performed only affect the parts of your code that use Win API calls or manipulate Windows control handles and memory pointers.
For a small fraction of applications, actually using Access/Excel x64 could make sense as it allows you to deal with 64 bit integers (as opposed to the standard 32 bit `long`). While this could be a useful feature, code that use these 64 bit integers will not compile on a 32 bit version of Office.
### If you use external libraries and controls
If you are using external libraries and ActiveX controls, it’s unlikely that they will only work in Office x64.
The reason is that most libraries (especially older ones) are only available in 32 bits.
For these to work under Office x64, they would have to be compiled especially for 64 bit as well.
So, the bottom line is: do not use external libraries and ActiveX components ([not even the ones that usually come with Office!]) unless you can either:
* completely control the user environment an ensure it will always run Office x86 (32 bit).
* you can compile or get 64 bit versions of these libraries and ActiveX components so you can build a version of your application that will work for all your users.
### If your code targets Access 2010 and later
There are 2 changes that may affect your code: external library declarations and usage of the new pointer and very large integer types.
Any declaration for external libraries (such as Win API or code that resides in DLLs), the declaration statement has changed and must use the new `PtrSafe` keyword to tell the compiler that the declaration should be interpreted in a 64 bit context when relevant. If you omit the keyword, your declarations will always be interpreted as being in a 32 bit context and the results may be random.
For instance, the old declaration for the `SetFocus` Win API function was:
Private Declare Function WinAPISetFocus Lib "user32" _ Alias "SetFocus" (ByVal hwnd As Long) As Long
Now it is:
Private Declare PtrSafe Function WinAPISetFocus Lib "user32" _ Alias "SetFocus" (ByVal hwnd As LongPtr) As Long
Note how the `hwnd` parameter is now a `LongPtr` instead of just a `Long`.
You will need to replace declarations that use `long` with the more appropriate type:
Use the new `LongPtr` wherever you deal with declarations that relate to Windows control handles or memory locations.
Defines a 64 bit integer.
The reason for `LongLong` is mainly to access some 64 bit versions of the Windows API, in particular, for math functions.
This type only exists if you are running under a 64 bit version of Access. On 32 bit systems, it will cause a compile error. To avoid that problem where your code needs to run on both 32/64 bit platforms, you can use the new conditional constant `Win64`:
#If WIN64 Then Dim largeInt As LongLong ' Declares a 64 bit integer #Else Dim largeInt As Long ' Declares a standard 32 bit integer #End If
The compiler will only see and compile one code path and will ignore the other, so doing this does not affect the speed of your application.
###If your code must also work in Access 2007 and earlier
Older versions of Access will not be able to compile the new `PtrSafe`, `LongPtr` and `LongLong` keywords introduced by VBA7.
If your code must also work in older versions of Access, you must either create 2 versions of your application (not very convenient), or simply use the new conditional constant `VBA7` to separate blocks of code that will run on Office 2010 and later from blocks of code that will run on Office 2007 and ealier.
The new `VBA7` conditional constant will evaluate to `True` if it is running under Office 2010 and later.
So now you can create variations of your code that will work everywhere:
#If VBA7 Then Private Declare PtrSafe Function WinAPISetFocus Lib "user32" _ Alias "SetFocus" (ByVal hwnd As LongPtr) As Long #Else Private Declare Function WinAPISetFocus Lib "user32" _ Alias "SetFocus" (ByVal hwnd As Long) As Long #End If
###Adapting old code
When going through old code, you can’t just add `PtrSafe` to all API declarations and do a search to replace `Long` into `LongPtr`. That would be the surest way to get everything wrong.
Instead, you must be very careful about the intent of the code and understand what your variables are manipulating: is that `long` really just a number or is it mean to be a memory location or a control handle ?
To help you, at least as far as the most common Win API declarations are concerned, Microsoft has made available a text file with hundreds of such declarations compatible for Office x64: [Win32API_PtrSafe with 64-bit Support]
If you need to use a WinAPI call that isn’t in the list, you will need to have a look at its actual definition and, based on the type of data being passed around, decide if you need to use a simple `Long` or a `LongPtr`.
For instance, I had to use the `MonitorFromWindow` API function. Its [C++ definition] is as follow:
HMONITOR MonitorFromWindow( _In_ HWND hwnd, _In_ DWORD dwFlags );
The definition tells me:
* That the function is taking a Handle (basically a pointer) to a Window and some flags
* and that the function returns a Handle to the display monitor (basically a pointer).
So the API definition that will work on all versions of Access is:
#If VBA7 Then ' Works in 32/64 bits of Office 2010 and later Declare PtrSafe Function MonitorFromWindow Lib "user32" _ (ByVal hwnd As LongPtr, ByVal dwFlags As Long) As LongPtr #Else ' Works in Office 2007 and earlier Declare Function MonitorFromWindow Lib "user32" _ (ByVal hwnd As Long, ByVal dwFlags As Long) As Long #End If
When using the external function, you also need to be careful in the code and use the `VBA7` conditional to separate the parts where you access structure pointers:
Private Sub Redimension() #If VBA7 Then Dim hMonitor As LongPtr #Else Dim hMonitor As Long #End If hMonitor = MonitorFromWindow(Application.hWndAccessApp, &H0&) ... do stuff with hMonitor ... End Sub
Make sure you save, re-compile and test your project as you go along. Also make frequent backups of your application as a wrong declaration could crash and corrupt it.
It may even be possible to crash the whole machine, although I have not experienced that so far.
Updating old code to make it work properly on all versions of Access is not difficult. It is usually fairly easy, although it can be a bit cumbersome at times.
The trick is to know what your code really needs to target:
* Office 2010 and later, the present and the future, in which case everything is straightforward and you use the new VBA7 keywords `PtrSafe`, `LongPtr` and `LongLong` where appropriate.
* All versions of Access, in which case you will have sometimes to separate and maintain 2 versions of your code using the `VBA7` conditional.
* [64-Bit Visual Basic for Applications Overview] from MSDN.
* [Compatibility Between the 32-bit and 64-bit Versions of Office 2010] from MDSN.
* [Declaring API Functions In 64 Bit Office].
* [Office 2010 Help Files: Win32API_PtrSafe with 64-bit Support].
Please help me! I’m using both 32 and 64 bit Access 2010 versions on two different computers for developing a database. I copy the accdb file on usb-stick from one computer to the other. Suddenly I received a warning message on my 32 bit computer stating a DLL file is missing when I first open it after copy the newest work being done on the other computer. The reference library say it is a MISSING Microsoft Windows Common Controls 6.0 causing the problem. (c:\windows\SysWOW64\MSCOMCTL.OCX) My problem is that this computer do not have a SysWOW64 directory because it is a 32 bit. I unclick the tickbox, save and exit the program but after opening it again – same old story – DLL missing! How can I get rid of this MISSING reference that clearly came from my 64bit computer? I have been working on this development for quite a number of weeks and this is the first time I received this problem. Please give me guidance!
@Rex: Have a look at this blog post: MSCOMCTL.ocx problems after August 2012 security patch From what you say, I’m wondering if you are using Access 2010 x64: the 64 bit version of Access can’t access the MSCOMCTL.OCX since it’s a 32 bit control only and Microsoft did not provide a 64 bit version of that control. On a 64 bit OS, the control would be under the SysWOW64 directory (where 32 bit components are located), but you would need a 32 bit version of Access to use that. If you do not need any of these Common controls, just deactivate the reference in your database. At any rate, I can only recommend avoiding using these external ActiveX as they are often a source of problems (wrongs versions installed, usually exists only in 32 bits, often quite old, etc). if you are looking to replace a useful control like the TreeView, have a look at this replacement: An MSForms (All VBA) Treeview. Similarly, if you are looking for a calendar control, have a look at this one: Calendar Control for All Office versions – including Office 2010 64 bit
Hi Renaud Thanks for an extremely informative piece. I have a question for you: I’m compiling my Access database from a 32-bit Office machine running Access 2010. I’m deploying as an .accdr file. My references are: VBE7.DLL MSACC.OLB stdole2.tlb ACEDAO.DLL EXCEL.EXE msxml6.dll I take it there is no problem with most of the references as the deployed system will be using Access 2010 Runtime. However, Excel will give me a problem. I was hoping to register the Excel reference (any any others that could differ by machine) on the fly, but it seems I can’t register a 64-bit version of Excel from a 32-bit system. Or can I? Also, would I still need to make changes to API calls if I’m using Runtime 2010?
Thank you very much!
Comments are closed.