First Published 17 Nov 2023 Last Updated 8 Dec 2023 Difficulty level : Moderate
From version 2010 onwards, Microsoft Office programs have been available in both 32-bit and 64-bit versions. VBA was updated to version 7 to handle 64-bit declarations.
Users with 32-bit Windows can only install 32-bit Office. However, most users now have 64-bit Windows and can choose which bitness of Office to install
For many years, the default installation was 32-bit and for most users there was little benefit in installing the 64-bit version, with the exception of users with large and complex Excel spreadsheets or highly demanding Access databases.
See Choose between the 64-bit or 32-bit version of Office
Gradually the proportion of 64-bit Office users has increased, particularly since the default installation changed to 64-bit a couple of years ago.
At the time of writing this article (Nov 2023), the only versions of Office currently supported are Access 2016/2019/2021/365.
As a result, the vast majority of users will be working with one of those versions.
However, whilst it is not recommended, there are still a large number of users running older versions, including versions before A2010, either because they prefer the interface or because there are no new features they need. It is in those instances, that conditional compilation may be needed.
As discussed in my previous article, 32 to 64-bit Conversion, there are three main issues to deal with to ensure applications will run in both bitnesses:
1. All API declarations must be updated for use in 64-bit
2. Some older ActiveX controls do not work in 64-bit e.g. Flexgrid
3. Whilst ACCDB files will run in both bitnesses, ACCDE files will only run in the same bitness as they were created.
This means creating separate 32-bit & 64-bit ACCDE files for your users.
This article will focus on when and how to use conditional compilation correctly with API declarations.
The next article in this series will discuss the use of conditional compilation with type statements for use in 32/64-bit
When you open or try to compile an Access application in 64-bit containing API declarations written for 32-bit, you will see an error message like this and the incorrect declarations will be shown as having syntax errors in RED
Fortunately, fixing these errors is usually fairly simple.
In my explanation, I will use four API functions used in converting twips to pixels. Before conversion, the APIs are as follows:
API code #1 'A2007 or earlier / A2010 or later (32-bit only)
Private Declare Function GetDC Lib "user32" (ByVal hWnd As Long) As Long
Private Declare Function ReleaseDC Lib "user32" (ByVal hWnd As Long, ByVal hdc As Long) As Long
Private Declare Function GetDeviceCaps Lib "gdi32" (ByVal hdc As Long, ByVal nIndex As Long) As Long
Private Declare Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long
These will work in all 32-bit versions of Access from at least 2000 onwards up to and including 365.
As far as API declarations are concerned, it makes no difference whether Windows is 32-bit or 64-bit. Only the Office bitness matters.
The API declarations must be converted for use in 64-bit Access (version 2010 onwards) by adding PtrSafe and changing Long values in handles/pointers either to LongLong or normally LongPtr.
NOTE:
a) Adding PtrSafe acts solely to tell Access the declarations are safe to use in VBA7 (32-bit or 64-bit).
If omitted, the declarations will not compile in 64-bit
Once PtrSafe is added to each API, the declarations will compile in 64-bit but, unless other changes are made, they may not work.
Of the 4 API statements above, the only one that requires no additional changes is GetSystemMetrics
b) Handles/pointers to memory locations require more space in 64-bit so a new LongLong datatype was created in VBA7 for this purpose.
However, LongLong ONLY works in 64-bit Access
c) To simplify working with APIs in different bitnesses, Microsoft also created a 'dummy' datatype LongPtr.
This resolves to LongLong in 64-bit and Long in 32-bit versions of Access using VBA7 (A2010 or later).
The converted APIs are normally written as follows:
API code #2 'A2010 or later (32-bit or 64-bit)
Private Declare PtrSafe Function GetDC Lib "user32" (ByVal hWnd As LongPtr) As LongPtr
Private Declare PtrSafe Function ReleaseDC Lib "user32" (ByVal hWnd As LongPtr, ByVal hdc As LongPtr) As Long
Private Declare PtrSafe Function GetDeviceCaps Lib "gdi32" (ByVal hdc As LongPtr, ByVal nIndex As Long) As Long
Private Declare PtrSafe Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long
NOTE:
a) hWnd (window handle) and hDc (device context handle) are both pointers to memory locations so become LongPtr
b) nIndex is not a pointer and so Long is still correct
c) The output of GetDC is also a pointer so becomes LongPtr
d) Converting all instances of Long to LongPtr is incorrect and parts of your code will not work correctly in 64-bit Access.
If all users are running VBA7 versions of Access (A2010 or later, 32 or 64-bit), just use API Code #2 with no conditions.
However, if some users are running older versions of Access prior to A2010, use conditional compilation as follows:
API code #3 'Conditional compilation for all versions/bitnesses
#If VBA7 Then 'VBA7: A2010 or later (32-bit/64-bit)
Private Declare PtrSafe Function GetDC Lib "user32" (ByVal hWnd As LongPtr) As LongPtr
Private Declare PtrSafe Function ReleaseDC Lib "user32" (ByVal hWnd As LongPtr, ByVal hdc As LongPtr) As Long
Private Declare PtrSafe Function GetDeviceCaps Lib "gdi32" (ByVal hdc As LongPtr, ByVal nIndex As Long) As Long
Private Declare PtrSafe Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long
#Else 'VBA6: A2007 or earlier (32-bit)
Private Declare Function GetDC Lib "user32" (ByVal hWnd As Long) As Long
Private Declare Function ReleaseDC Lib "user32" (ByVal hWnd As Long, ByVal hdc As Long) As Long
Private Declare Function GetDeviceCaps Lib "gdi32" (ByVal hdc As Long, ByVal nIndex As Long) As Long
Private Declare Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long
#End If
NOTE:
Code that will not compile in a particular version/bitness will be shown in RED.
This is NORMAL behaviour. The syntax error can be ignored as the invalid clause will not be used.
a) In A2007 or earlier, the #If VBA7 clause is not valid. The #Else clause is used:
b) Using 64-bit Access, the #Else clause is not valid. The #If VBA7 clause is used:
c) Using 32-bit Access A2010 or later, both clauses are valid. The VBA7 clause is used as it read first:
The code construct #If VBA7 Then . . . #Else . . . #End If is the simplest way of doing conditional compilation and is the recommended approach.
Although I do not recommend doing so, you can instead use the Win64 compiler condition.
This was poorly named as it refers to the Office bitness. It would have been better named as Office64.
You would use #If Win64 Then . . . #Else . . . #End If as follows:
API code #4 'ALTERNATIVE conditional compilation for all versions/bitnesses
#If Win64 Then 'VBA7: A2010 or later (64-bit ONLY)
Private Declare PtrSafe Function GetDC Lib "user32" (ByVal hWnd As LongLong) As LongLong
Private Declare PtrSafe Function ReleaseDC Lib "user32" (ByVal hWnd As LongLong, ByVal hdc As LongLong) As Long
Private Declare PtrSafe Function GetDeviceCaps Lib "gdi32" (ByVal hdc As LongLong, ByVal nIndex As Long) As Long
Private Declare PtrSafe Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long
#Else 'VBA6 A2007 or earlier / VBA7 A2010 or later (32-bit ONLY)
Private Declare Function GetDC Lib "user32" (ByVal hWnd As Long) As Long
Private Declare Function ReleaseDC Lib "user32" (ByVal hWnd As Long, ByVal hdc As Long) As Long
Private Declare Function GetDeviceCaps Lib "gdi32" (ByVal hdc As Long, ByVal nIndex As Long) As Long
Private Declare Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long
#End If
NOTE:
Whilst the above will also work, you will need to use conditional compilation to support both bitnesses even if all users are running A2010 or later. Extra work for no benefit!
You may also see other variations, particularly in older articles. Most are over complicated and unnecessary but at least one method is flawed.
API code #5 '3 part conditional compilation for all versions/bitnesses
#If Win64 Then 'VBA7: A2010 or later (64-bit)
Private Declare PtrSafe Function GetDC Lib "user32" (ByVal hWnd As LongLong) As LongLong
Private Declare PtrSafe Function ReleaseDC Lib "user32" (ByVal hWnd As LongLong, ByVal hdc As LongLong) As Long
Private Declare PtrSafe Function GetDeviceCaps Lib "gdi32" (ByVal hdc As LongLong, ByVal nIndex As Long) As Long
Private Declare PtrSafe Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long
#ElseIf VBA7 Then 'VBA7: A2010 or later (32-bit)
Private Declare PtrSafe Function GetDC Lib "user32" (ByVal hWnd As Long) As Long
Private Declare PtrSafe Function ReleaseDC Lib "user32" (ByVal hWnd As Long, ByVal hdc As Long) As Long
Private Declare PtrSafe Function GetDeviceCaps Lib "gdi32" (ByVal hdc As Long, ByVal nIndex As Long) As Long
Private Declare PtrSafe Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long
#Else 'VBA6: A2007 or earlier (32-bit)
Private Declare Function GetDC Lib "user32" (ByVal hWnd As Long) As Long
Private Declare Function ReleaseDC Lib "user32" (ByVal hWnd As Long, ByVal hdc As Long) As Long
Private Declare Function GetDeviceCaps Lib "gdi32" (ByVal hdc As Long, ByVal nIndex As Long) As Long
Private Declare Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long
#End If
NOTE:
The above construct #If Win 64 Then . . . #ElseIf VBA7 Then . . . #Else . . . #End If is valid. Whilst it will work, it adds unnecessary complexity.
API code #6 '3 part conditional compilation for all versions/bitnesses
#If VBA7 Then
#If Win64 Then 'VBA7: A2010 or later (64-bit)
Private Declare PtrSafe Function GetDC Lib "user32" (ByVal hWnd As LongLong) As LongLong
Private Declare PtrSafe Function ReleaseDC Lib "user32" (ByVal hWnd As LongLong, ByVal hdc As LongLong) As Long
Private Declare PtrSafe Function GetDeviceCaps Lib "gdi32" (ByVal hdc As LongLong, ByVal nIndex As Long) As Long
Private Declare PtrSafe Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long
#Else 'VBA7: A2010 or later (32-bit)
Private Declare PtrSafe Function GetDC Lib "user32" (ByVal hWnd As Long) As Long
Private Declare PtrSafe Function ReleaseDC Lib "user32" (ByVal hWnd As Long, ByVal hdc As Long) As Long
Private Declare PtrSafe Function GetDeviceCaps Lib "gdi32" (ByVal hdc As Long, ByVal nIndex As Long) As Long
Private Declare PtrSafe Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long
#End If
#Else 'VBA6: A2007 or earlier (32-bit)
Private Declare Function GetDC Lib "user32" (ByVal hWnd As Long) As Long
Private Declare Function ReleaseDC Lib "user32" (ByVal hWnd As Long, ByVal hdc As Long) As Long
Private Declare Function GetDeviceCaps Lib "gdi32" (ByVal hdc As Long, ByVal nIndex As Long) As Long
Private Declare Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long
#End If
NOTE:
The above construct is also valid. Again it will work but adds even more unnecessary complexity
API code #7 'INCORRECT 3 part conditional compilation for all versions/bitnesses
#If VBA7 Then 'VBA7: A2010 or later (32-bit) <== THIS COMMENT IS INCORRECT (see below)
Private Declare PtrSafe Function GetDC Lib "user32" (ByVal hWnd As LongPtr) As LongPtr
Private Declare PtrSafe Function ReleaseDC Lib "user32" (ByVal hWnd As LongPtr, ByVal hdc As LongPtr) As Long
Private Declare PtrSafe Function GetDeviceCaps Lib "gdi32" (ByVal hdc As LongPtr, ByVal nIndex As Long) As Long
Private Declare PtrSafe Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long
#ElseIf Win64 Then 'VBA7: A2010 or later (64-bit)
Private Declare PtrSafe Function GetDC Lib "user32" (ByVal hWnd As LongLong) As LongLong
Private Declare PtrSafe Function ReleaseDC Lib "user32" (ByVal hWnd As LongLong, ByVal hdc As LongLong) As Long
Private Declare PtrSafe Function GetDeviceCaps Lib "gdi32" (ByVal hdc As LongLong, ByVal nIndex As Long) As Long
Private Declare PtrSafe Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long
#Else 'VBA6: A2007 or earlier (32-bit)
Private Declare Function GetDC Lib "user32" (ByVal hWnd As Long) As Long
Private Declare Function ReleaseDC Lib "user32" (ByVal hWnd As Long, ByVal hdc As Long) As Long
Private Declare Function GetDeviceCaps Lib "gdi32" (ByVal hdc As Long, ByVal nIndex As Long) As Long
Private Declare Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long
#End If
Yet again this will work. However, despite the intention of the comment for the VBA7 clause, that will actually work for both 32-bit & 64-bit
So, in this example the #ElseIf Win64 clause will NEVER run and SHOULD be omitted . Doing so, changes the conditional compilation back to the recommended code #3.
Whilst you can successfully use #If VBA7 or #If Win64, you should normally use one or the other construct . . . NOT both!
In fact, using #If Win64 for API declarations is ALMOST NEVER required. Almost all APIs will run in both bitnesses providing they are properly converted.
In recent years, the ONLY time I have used the compiler condition #If Win64 was in a function to determine the Office bitness as part of my Access Version Checker app.
GetTickCount / GetTickCount64
Due to their names, the APIs GetTickCount/GetTickCount64 are often wrongly quoted as exceptions where each is supposedly intended for a specific bitness.
Both APIs are used for timimg intervals based on the system clock.
As an example, a fellow developer recently sent me this code which he suggested as a good use case for #If Win64:
#If Win64 Then
Private Declare PtrSafe Function GetTickCount64 Lib "kernel32" () As LongLong
#Else
Private Declare Function GetTickCount Lib "kernel32" () As Long
#End If
It was then used to close a form after 5 seconds inactivity with this code:
Public Function GetIdleSecs()
Dim lastInput As LASTINPUTINFO
With lastInput
.cbSize = LenB(lastInput)
Call GetLastInputInfo(lastInput)
#If Win64 Then
GetIdleSecs = (GetTickCount64() - .dwTime) / 1000
#Else
GetIdleSecs = (GetTickCount() - .dwTime) / 1000
#End If
End With
End Function
I’ve seen that code before in various online articles and it does work. However, I pointed out that it was unnecessary as GetTickCount works in both bitnesses (with PtrSafe).
In response, my fellow developer agreed that GetTickCount works in both bitnesses. However he then stated that:
GetTickCount is deprecated and now we must use GetTickCount64, which is more precise and also works in both bitnesses.
The final part of that sentence is correct but the rest is completely wrong.
To explain why, I will look at the two APIs in more detail, starting with GetTickCount:
#If VBA7 Then
Private Declare PtrSafe Function GetTickCount Lib "kernel32" () As Long
#Else
Private Declare Function GetTickCount Lib "kernel32" () As Long
#End If
As used above, GetTickCount works in all versions / bitnesses to measure time intervals to a precision of 1 millisecond.
In practice its accuracy is limited by the system clock which 'ticks' about 64 times/second so it is accurate to about 16 milliseconds (0.016 seconds)
The API is based on an unsigned Long datatype with a range 0 to 4,294,967,295 and its value indicates the time in milliseconds since the workstation was last restarted.
The maximum value in milliseconds is equivalent to about 49.7 days at which point the timer will 'roll over' after a short pause and restart.
Whilst this maximum value is fine for most time interval measurements, it can cause problems for very long time intervals and for machines that are always on.
It is for these situations that GetTickCount64 was introduced in VBA7. Documentation normally shows this API as:
Private Declare PtrSafe Function GetTickCount64 Lib "kernel32" Alias "GetTickCount64" () As LongLong
As written above using LongLong, it will only work in 64-bit Access. However, using LongPtr instead it will work in both bitnesses (A2010 onwards)
Private Declare PtrSafe Function GetTickCount64 Lib "kernel32" Alias "GetTickCount64" () As LongPtr
GetTickCount64 also measure time intervals to exactly the same degree of precision as GetTickCount. For most purposes, either can be used.
However, GetTickCount64 uses an unsigned LongLong datatype with a range 0 to 18,446,744,073,709,551,615.
If my calculations are correct, the maximum value in milliseconds is approximately equal to 584,542,046,090 years!
At this point, the timer would 'roll over' . . . but that is clearly never going to be an issue!
UPDATE - 24 Nov 2023
Following a question by one of my regular readers, I should clarify that to use GetTickCount64, you must be running Windows 7 or later (32-bit or 64-bit).
GetTickCount64 cannot run on a machine running 32-bit Windows XP or earlier because, at that time, it was not available in the kernel32.dll library file.
However, both GetTickCount64 and GetTickCount are included in the kernel32.dll file supplied with 32-bit Windows 7 or later. For example:
The screenshot below shows GetTickCount64 working successfully in Access 2010 32-bit running in Windows 7 32-bit. Its counterintuitive but it works!
So its usage is limited to Access 2010 or later running in Windows 7 or later. The bitness of Windows or Access is not relevant to its use.
However, do remember that as LongPtr resolves to Long in 32-bit, GetTickCount64 will still 'roll over' after 49.7 days (*) - exactly the same as using GetTickCount
* At the risk of adding to any possible confusion, this limit can be circumvented by defining the GetTickCount64 API as Currency or BigInt (Large integer) datatype instead of LongPtr.
If any of the above points are an issue, use a different method of measuring very long time intervals. See my article Timer Comparison Tests
NOTE:
There are a few other pairs of APIs with similar names (with/without a 64 suffix). For example:
InterlockedDecrement / InterlockedDecrement64
InterlockedIncrement / InterlockedIncrement64
InterlockedExchange / InterlockedExchange64
It may well be that similar comments apply to each of these API 'pairs' but, as I have never used them, I am hesitant about making a definitive statement in these cases.
Further Reading
Data Type Ranges (Microsoft Learn)
32 to 64-bit Conversion
Office 2010 Help Files: Win32API_PtrSafe with 64-bit Support
Feedback
Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.
Please also consider making a donation towards the costs of maintaining this website. Thank you
Colin Riddington Mendip Data Systems Last Updated 8 Dec 2023
Return to Access Articles Page
Page 2 of 5
1
2
3
4
5
Return To Top
|
|
|