Last Update: 2017 - 07 - 09
How to convert Windows API declarations in VBA for 64-bit
by Philipp Stiefel, originally published August 7th, 2017
Since Office 2010 all the Office applications including Microsoft Access and VBA are available as a 64bit edition in addition to the classic 32bit edition.
To clear up an occasional misconception. You do not need to install Office/Access as 64-bit- application just because you got a 64-bit operating system. Windows x64 provides an excellent 32-bit subsystem that allows you to run any 32-bit application without drawbacks.
For now, 64-bit Office/Access still is rather the exception than the norm, but this is changing more and more.
Access - 32-bit vs. 64-bit
If you are just focusing on Microsoft Access there is actually no compelling reason to use the 64-bit edition instead of the 32bit edition. Rather the opposite is true. There are several reasons not to use 64Bit Access.
There is only one benefit of 64-bit-Access I’m aware of. When you open multiple forms at the same time that contain a large number of sub-forms, most likely on a Tab-Control, you might run into out-of-memory-errors on 32-bit-systems. The basic problem exists with 64-Bit-Access as well, but it takes much longer until you will see any memory related error.
Unfortunately (in this regard) Access is part of the Office Suite as is Microsoft Excel. For Excel, there actually are use cases for the 64-Bit Edition. If you use Excel to calculate large data models, e.g. financial risk calculations, you will probably benefit from the additional memory available to a 64-Bit-Application.
So, whether you as an Access developer like it or not, you might be confronted with the 64-Bit-Edition of Microsoft Access because someone in your or your client’s organization decided they will install the whole Office Suite in 64-bit. – It is not possible to mix and match 32- and 64-bit applications from the Microsoft Office Suite.
I can’t do anything about the availability of third-party-components, so in this article, I’m going to focus on the migration of Win-API-Calls in VBA to 64-bit-compatibility.
Migrate Windows API-Calls in VBA to 64-bit
Fortunately, the Windows API was completely ported to 64-Bit, so you will not encounter any function, which was available on 32-bit but isn’t anymore on 64-bit. – At least I do not know of any.
However, I frequently encounter several common misconceptions about how to migrate your Windows API calls. I hope I will be able to debunk them with this text.
But first things first. The very first thing you will encounter when you try to compile an Access Application with an API-Declaration that was written for 32-bit in VBA on in 64-Bit-Access is an error message.
Compile error: The code in this project must be updated for use on 64-bit-systems. Please review and update Declare statements and then mark them with the PtrSafe attribute.
This message is pretty clear about the problem, but you need further information to implement the solution.
With the introduction of Access 2010, Microsoft published an article on 32- and 64-Compatibility in Access. In my opinion, that article was comprehensive and pretty good, but many developers had the opinion it was insufficient.
Just recently there was a new, and in my opinion excellent, introduction to the 64-bit extensions in VBA7 published on MSDN. It actually contains all the information you need. Nevertheless, it makes sense to elaborate on how to apply it to your project.
The PtrSafe keyword
What does PtrSafe do? It actually does … nothing. Correct, it has no effect on how the code works at all.
The only purpose of the PtrSafe attribute is that you, as the developer, explicitly confirm to the VBA runtime environment that you checked your code to handle any pointers in the declared external function call correctly.
As the data type for pointers is different in a 64-bit-environment (more on that in a moment) this actually makes sense. If you would just run your 32-Bit-API-Code in a 64-bit-context, it would work; sometimes. Sometimes it would just not work. And sometimes it would overwrite and corrupt random areas of your computer’s memory and cause all sorts of random application instability and crashes. These effects would be very hard to track down to the incorrect API-Declarations.
For this understandable reason, the PtrSafe keyword is mandatory in 64-Bit-VBA for each external function declaration with the Declare Statement. The PtrSafe keyword can be used in 32-Bit-VBA as well but is optional there for downward compatibility.
Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
The LongLong type
The data types Integer (16-Bit-Integer) and Long (32-Bit-Integer) are unchanged in 64-Bit-VBA. They are still 2 bytes and 4 bytes in size and their range of possible values is the same as it were before on 32-bit. This not only true for VBA, but for the whole Windows 64-Bit-Plattform. Generic data types retain their original size.
Now, if you want to use a true 64-Bit-Integer in VBA, you have to use the new LongLong data type. This data type is actually only available in 64-Bit-VBA, not in the 32-bit-version. In context with the Windows API, you will actually use this data type only very, very rarely. There is a much better alternative.
The LongPtr data type
On 32-Bit-Windows all pointers to memory addresses are 32-Bit-Integers. In VBA, we used to declare those pointer variables as Long. On 64-Bit-Windows these pointers were changed to 64-Bit-Integers to address the larger memory space. So, obviously, we cannot use the unchanged Long data type anymore.
In theory, you could use the new LongLong type to declare integer pointer variables in 64-Bit-VBA-Code. In practice, you absolutely should not. There is a much better alternative.
Particularly for pointers, Microsoft introduced an all new and very clever data type. The LongPtr data type. The really clever thing about the LongPtr type is, it is a 32-Bit-Integer if the code runs in 32-Bit-VBA and it becomes a 64-Bit-Integer if the code runs in 64-Bit-VBA.
LongPtr is the perfect type for any pointer or handle in your Declare Statement. You can use this data type both environments and it will always be appropriately sized to handle the pointer size of your environment.
Misconception: “You should change all Long variables in your Declare Statements and Type declarations to be LongPtr variables when adapting your code for 64-bit.”
As mentioned above, the size of the existing, generic 32-bit data types has not changed. If an API-Function expected a Long Integer on 32-bit it will still expect a Long Integer on 64-Bit.
Only if a function parameter or return value is representing a pointer to a memory location or a handle (e.g. Window Handle (HWND) or Picture Handle), it will be a 64-Bit-Integer. Only these types of function parameters should be declared as LongPtr.
If you use LongPtr incorrectly for parameters that should be plain Long Integer your API calls may not work or may have unexpected side effects. Particularly if you use LongPtr incorrectly in Type declarations. This will disrupt the sequential structure of the type and the API call will raise a type mismatch exception.
Public Declare PtrSafe Function ShowWindow Lib "user32" (ByVal hWnd As LongPtr, ByVal nCmdShow As Long) As Boolean
The hWnd argument is a handle of a window, so it needs to be a LongPtr. nCmdShow is an int32, it should be declared as Long in 32-bit and in 64-bit as well.
Do not forget a very important detail. Not only your Declare Statement should be written with the LongPtr data type, your procedures calling the external API function must, in fact, use the LongPtr type as well for all variables, which are passed to such a function argument.
VBA7 vs WIN64 compiler constants
Also new with VBA7 are the two new compiler constants Win64 and VBA7. VBA7 is true if your code runs in the VBA7-Environment (Access/Office 2010 and above). Win64 is true if your code actually runs in the 64-Bit-VBA-Environment. Win64 is not true if you run a 32-Bit VBA Application on a 64-bit-system.
Misconception: “You should use the WIN64 compiler constants to provide two versions of your code if you want to maintain compatibility with 32-bit VBA/Access.”
For 99% of all API declarations, it is completely irrelevant if your code runs in 32-bit-VBA or in 64-Bit-VBA.
As explained above, the PtrSafe Keyword is available in 32-bit-VBA as well. And, more importantly, the LongPtr data type is too. So, you can and should write API code that runs in both environments. If you do so, you’ll probably never need to use conditional compilation to support both platforms with your code.
However, there might be another problem. If you only target Access (Office) 2010 and newer, my above statement is unconditionally correct. But if your code should run with older version of Access as well, you need to use conditional compilation indeed. But you still do not need to care about 32/64-Bit. You need to care about the Access/VBA-Version you code is running in.
You can use the VBA7 compiler constant to write code for different versions of VBA. Here is an example for that.
Private Const SW_MAXIMIZE As Long = 3 #If VBA7 Then Private Declare PtrSafe Function ShowWindow Lib "USER32" _ (ByVal hwnd As LongPtr, ByVal nCmdShow As Long) As Boolean Private Declare PtrSafe Function FindWindow Lib "USER32" Alias "FindWindowA" _ (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr #Else Private Declare Function ShowWindow Lib "USER32" _ (ByVal hwnd As Long, ByVal nCmdShow As Long) As Boolean Private Declare Function FindWindow Lib "USER32" Alias "FindWindowA" _ (ByVal lpClassName As String, ByVal lpWindowName As String) As Long #End If Public Sub MaximizeWindow(ByVal WindowTitle As String) #If VBA7 Then Dim hwnd As LongPtr #Else Dim hwnd As Long #End If hwnd = FindWindow(vbNullString, WindowTitle) If hwnd <> 0 Then Call ShowWindow(hwnd, SW_MAXIMIZE) End If End Sub
Now, here is a screenshot of that code in the 64-bit VBA-Editor. Notice the red highlighting of the legacy declaration. This is code section is marked, but it does not produce any actual error. Due to the conditional compilation it will never be compiled in this environment.
When to use the WIN64 compiler constant?
There are be situations where you still want to check for Win64. There are some new API functions available on the x64-Plattform that simply do not exist on the 32-bit-platform. So, you might want to use a new API function on x64 and a different implementation on x86 (32-bit).
A good example for this is the GetTickCount function. This function returns the number of milliseconds since the system was started. Its return value is a Long. The function can only return the tick count for 49.7 days before the maximum value of Long is reached. To improve this, there is a new GetTickCount64 function available on Windows 64. If you want to use this new function when your code is running in a 64-bit-environment, you need to use the Win64 constant.
#If Win64 Then Public Declare PtrSafe Function GetTickCount Lib "Kernel32" Alias "GetTickCount64" () As LongPtr #Else Public Declare PtrSafe Function GetTickCount Lib "Kernel32" () As LongPtr #End If
In this sample, I reduced the platform dependent code to a minimum by declaring both versions of the function as GetTickCount. Only on 64-bit, I use the alias GetTickCount64 to map this to the new version of this function. The “correct” return value declaration would have been LongLong for the 64-bit-version and just Long for the 32-bit-version. I use LongPtr as return value type for both declarations to avoid platform dependencies in the calling code.
With the content covered in this article you should be able to adapt most of your API-Declarations to 64-bit.
Many samples and articles on this topic available on the net today are lacking sufficient explanation to highlight the really important issues. I hope I was able to show the key facts for a successful migration.
Always keep in mind, it is actually not that difficult to write API code that is ready for 64-bit. - Good luck!
© 1999 - 2017 by Philipp Stiefel