EXCEL VBA中声明32位和64位兼容的winAPI函数的方法

'声明32位和64位Excel兼容的API函数

'当VBA7和Win64都是True时(只有64的Excel才是这种情况)，使用第一条Declare语句。在其他版本中，使用第二条Declare语句

#If VBA7 And Win64 Then
    Declare ptSafe Function GetWindowsDirectory Lib "kernel32" (ByVal ipBuffer As String, ByVal nSize As Long) As Long
#Else
    Declare Function GetWindowsDirectory Lib "kernel32" (ByVal ipBuffer As String, ByVal nSize As Long) As Long
#End If

简单来说 就是利用条件编译语句判断 vbe版本从而判断是不是64位office,遇到不同的平台会自动编译不同的版本.从而达到兼容声明的目的 当然前提是你要先搞清楚64位声明的正确写法,有些函数不仅仅是添加ptrasafe,还需要修改参数类型,需要对api函数底层有一定了解才行,

不过还是强烈建议没有特殊用途没有16g内存+10万行以上数据量的情况下,不要64位,出了麻烦,目前看不到任何好处

再举个例子

#If VBA7 Then
'定义窗体样式
Private Declare PtrSafe Function FindWindow Lib "user32" Alias _
    "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare PtrSafe Function SetWindowLong Lib "user32" Alias _
    "SetWindowLongA" (ByVal Hwnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
#Else
'定义窗体样式
Private Declare Function FindWindow Lib "user32" Alias _
    "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function SetWindowLong Lib "user32" Alias _
    "SetWindowLongA" (ByVal Hwnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
#End If

更多VBA中的API函数介绍请参考

在VBA中使用Windows API 基本原理介绍和参考资料