Excel Macro编译报错Compile error when you edit a VBA macro in the 64-bit version of an Office 2016

在64位的office上运行带Macro的excel出现如下的报错

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.

 

 我们发现如下的报错代码片段

Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

注意看

#If VBA7 Then
    Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal ms As LongPtr)
#Else
    Private Declare Sub Sleep Lib "kernel32" (ByVal ms as Long)
#End If

划重点：需要在异常代码定义的Declare前面加上PtrSafe关键字

我这里出错代码具体修改情况如下

报错前 Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

修改后 Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

参考微软的文章 The code in this project must be updated for use on 64-bit systems - Office | Microsoft Docs