Specify PtrSafe to use both 32bit and 64bit Excel when importing Windows API

Learn about the Windows API PtrSafe declaration. When coding with Excel VBA, you can check the causes and solutions of PtrSafe-related errors that you occasionally encounter while importing and using Windows APIs.

1. Example Code of Windows API PtrSafe Error Occurrence

This code imports and declares Windows API Sleep, OutputDebugString.

Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Private Declare Sub OutputDebugString Lib "kernel32" Alias "OutputDebugStringA" (ByVal lpOutputString As String)

(Note: the above code is Logging Pattern: Utilizing OutputDebugString and DebugView – Productivity Skill (prodskill.com) This is the code introduced in .)

2. Windows API PtrSafe Error Message

When editing or compiling VBA code, the following error occurs.

——————————————-
Microsoft Visual Basic for Applications
——————————————-

This is a compilation error:

You will need to update the code in this project to use it on 64 bit systems. Review and update the Declare statement and mark it with the PtrSafe attribute.

3. What Causes Windows API PtrSafe Errors

64bit has been supported since MS-Office 2010, and it occurs because the PtrSafe keyword must be specified when imprting the Windows API in 64bit Excel after this.

Reference: The code in this project must be updated for use on 64-bit systems – Office | Microsoft Docs

Some excerpts from the above URL are pasted below.

---

Symptoms

Consider the following scenario:

• You write a Microsoft Visual Basic for Applications (VBA) macro code that uses Declare statements.
• Your VBA macro code uses compilation constants. For example, your macro code uses one the following compilation constants:
  • #If VBA7
  • #If Win64
• You use an #Else block in a conditional block. In the #Else block, you use syntax for a Declare statement designed to run in Microsoft Visual Basic for Applications 6.0.
• You edit the code in a 64-bit version of a Microsoft Office 2010 program.
• You try to change the Declare statement in the #Else block.

In this scenario, you receive the following error message:

Microsoft Visual Basic for Applications

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.

---

4. How to fix Windows API PtrSafe error

The VBA version of MS-Office 2010 is 7.0, and the previous one is 6.x. Since the PtrSafe keyword is required from VBA 7.0, the Windows API import code can be divided and declared according to whether the VBA version is later than 7.0 or not.

Reference: PtrSafe keyword (VBA) | Microsoft Docs

Some excerpts from the above URL are pasted below.

---

PtrSafe keyword

• Article
• 09/14/2021
• 2 minutes to read
• 5 contributors

The PtrSafe keyword is used in this context: Declare statement.

Declare statements with the PtrSafe keyword is the recommended syntax. Declare statements that include PtrSafe work correctly in the VBA7 development environment on both 32-bit and 64-bit platforms only after all data types in the Declare statement (parameters and return values) that need to store 64-bit quantities are updated to use LongLong for 64-bit integrals or LongPtr for pointers and handles.

To ensure backwards compatibility with VBA version 6 and earlier, use the following construct:

#If VBA7 Then 
Declare PtrSafe Sub... 
#Else 
Declare Sub... 
#EndIf

When running in 64-bit versions of Office, Declare statements must include the PtrSafe keyword. The PtrSafe keyword asserts that a Declare statement is safe to run in 64-bit development environments.

Adding the PtrSafe keyword to a Declare statement only signifies that the Declare The statement explicitly targets 64-bits. All data types within the statement that need to store 64-bits (including return values and parameters) must still be modified to hold 64-bit quantities by using either LongLong for 64-bit integrals or LongPtr for pointers and handles.

---

You just need to determine whether a “VBA7” constant or a “Win64” constant is defined. In this case, use the Compiler Directive #If syntax.

The code is structured like this:

'방법1
#If VBA7 Then
    '64bit Windows API import
#Else
    '32bit Windows API import
#End If

'방법2
#If Win64 Then
    '64bit Windows API import
#Else
    '32bit Windows API import
#End If

'방법1, 방법2는 동일한 효과가 있으므로 한 방법을 선택하면 됨

5. Example code for solving Windows API PtrSafe error

5.1. In case of being mixed and used in 32bit and 64bit Excel

#If VBA7 Then 'For 64 Bit Systems
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)
    Private Declare PtrSafe Sub OutputDebugString Lib "kernel32" Alias "OutputDebugStringA" (ByVal lpOutputString As String)
#Else 'For 32 Bit Systems
    Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
    Private Declare Sub OutputDebugString Lib "kernel32" Alias "OutputDebugStringA" (ByVal lpOutputString As String)
#End If

5.2. If used only in 64bit Excel

If you are guaranteed that it will only be used in 64-bit Excel, use only one statement that specifies the PtrSafe keyword.

Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)
Private Declare PtrSafe Sub OutputDebugString Lib "kernel32" Alias "OutputDebugStringA" (ByVal lpOutputString As String)