With the introduction of Windows 7 and Office 2010 VBA developers face
a new challenge: ensuring their applications work on both 32 bit and 64
bit platforms.
This page is meant to become the first stop for anyone who needs the
proper syntax for his API declaration statement in Office VBA.
Many of the declarations were figured out by Charles Williams of
www.decisionmodels.com
when he created the 64 bit version of our
Name Manager.
Links
Of course Microsoft documents how to do this. There is an introductory
article on Microsoft MSDN:
When you run the installer after downloading the file form
the link above, it does not tell you where it installed the information.
Look in this -new- folder on your C drive:
This is a complicated one because it has a lot of arguments. A fully
functional example is included below the example declaration lines.
Courtesy: The
example code was taken from this page
DeclareFunction
CreateProcess Lib "kernel32" _
Alias "CreateProcessA" (ByVal lpApplicationName
AsString, _ ByVal lpCommandLine AsString, _
lpProcessAttributes As SECURITY_ATTRIBUTES,
_
lpThreadAttributes As SECURITY_ATTRIBUTES,
_ ByVal bInheritHandles AsLong, _ ByVal dwCreationFlags AsLong, _
lpEnvironment As Any, _ ByVal lpCurrentDriectory
AsString, _
lpStartupInfo As STARTUPINFO, _
lpProcessInformation As PROCESS_INFORMATION)
AsLong
Declare PtrSafe
Function CreateProcess Lib "kernel32"
_
Alias "CreateProcessA" (ByVal lpApplicationName
AsString, _ ByVal lpCommandLine AsString, _
lpProcessAttributes As SECURITY_ATTRIBUTES,
_
lpThreadAttributes As SECURITY_ATTRIBUTES,
_ ByVal bInheritHandles AsLong, _ ByVal dwCreationFlags AsLong, _
lpEnvironment As Any, _ ByVal lpCurrentDriectory
AsString, _
lpStartupInfo As STARTUPINFO, _
lpProcessInformation As PROCESS_INFORMATION)
As LongPtr
'Full example shown below, including the necessary structures #If VBA7 Then Declare PtrSafe
Function CreateProcess Lib "kernel32"
_
Alias "CreateProcessA" (ByVal lpApplicationName
AsString, _ ByVal lpCommandLine AsString, _
lpProcessAttributes As SECURITY_ATTRIBUTES,
_
lpThreadAttributes As SECURITY_ATTRIBUTES,
_ ByVal bInheritHandles AsLong, _ ByVal dwCreationFlags AsLong, _
lpEnvironment As Any, _ ByVal lpCurrentDriectory
AsString, _
lpStartupInfo As STARTUPINFO, _
lpProcessInformation As PROCESS_INFORMATION)
As LongPtr
PrivateFunction
SuperShell(ByVal App AsString, ByVal WorkDir
AsString, dwMilliseconds
AsLong, _ ByVal start_size As
enSW, ByVal Priority_Class
As enPriority_Class) AsBoolean
Dim pclass AsLong Dim sinfo As
STARTUPINFO Dim pinfo As
PROCESS_INFORMATION 'Not used, but needed Dim sec1 As
SECURITY_ATTRIBUTES Dim sec2 As
SECURITY_ATTRIBUTES 'Set the structure size
sec1.nLength = Len(sec1)
sec2.nLength = Len(sec2)
sinfo.cb = Len(sinfo) 'Set the flags
sinfo.dwFlags = STARTF_USESHOWWINDOW 'Set the window's startup position
sinfo.wShowWindow = start_size 'Set the priority class
pclass = Priority_Class
'Start the program If CreateProcess(vbNullString, App,
sec1, sec2, False, pclass, _
0&, WorkDir, sinfo, pinfo) Then 'Wait ' WaitForSingleObject pinfo.hProcess,
dwMilliseconds
SuperShell = True Else
SuperShell = False EndIf EndFunction
Sub Test() Dim sFile AsString 'Set the dialog's title
sFile = Application.GetOpenFilename("Executables (*.exe), *.exe",
, "")
SuperShell sFile, Left(sFile, InStrRev(sFile, "\")), 0, SW_NORMAL,
HIGH_PRIORITY_CLASS EndSub
#If VBA7 Then PublicDeclare
PtrSafe Function DrawMenuBar
Lib "user32" (ByVal
hWnd As LongPtr) AsLong
#Else PublicDeclareFunction DrawMenuBar Lib
"user32" (ByVal hWnd AsLong) As Long
#End If
PrivateDeclare PtrSafe
Function GdipCreateHBITMAPFromBitmap
Lib "GDIPlus" (ByVal
bitmap As LongPtr, hbmReturn
AsLongPtr,
ByVal background As
Long) As LongPtr
PrivateDeclare PtrSafe
Function GdiplusStartup Lib
"GDIPlus" (token As LongPtr, inputbuf
As GdiplusStartupInput, OptionalByVal outputbuf As LongPtr
= 0) As LongPtr
PublicDeclareFunction GetClassName Lib
"USER32" Alias "GetClassNameA" _
(ByVal hWnd As Long,
ByVal lpClassName As String,
_ ByVal nMaxCount As Long)
As Long
PublicDeclare PtrSafe
Function GetClassName Lib
"USER32" Alias "GetClassNameA" _
(ByVal hWnd AsLongPtr, ByVal lpClassName
As String, _ ByVal nMaxCount AsLongPtr) AsLong
This is one of the few API functions that requires the Win64 compile constant:
#If VBA7 Then
#If Win64 Then PrivateDeclare PtrSafe Function
GetWindowLongPtr Lib "USER32" Alias "GetWindowLongPtrA"
(ByVal hWnd As LongPtr,
ByVal nIndex AsLong) As LongPtr
#Else PrivateDeclare PtrSafe Function
GetWindowLongPtr Lib "USER32" Alias "GetWindowLongA"
(ByVal hWnd As LongPtr,
ByVal nIndex As Long)
As LongPtr
#End If
#Else PrivateDeclareFunction GetWindowLong Lib
"USER32" Alias "GetWindowLongA" (ByVal hWnd
As Long, ByVal nIndex
AsLong)
As Long
#End If
Public Declare PtrSafe Function GetWindowText Lib "USER32" Alias "GetWindowTextA"
_
(ByVal hWnd As LongPtr, ByVal lpString As String, _
ByVal cch As LongPtr) As Long
#If VBA7 Then Declare PtrSafe
Sub mouse_event Lib
"user32" (ByVal dwFlags AsLong, ByVal dx
AsLong, _ ByVal
dy AsLong,
ByVal cButtons AsLong, _ ByVal
dwExtraInfo As LongPtr)
#Else PrivateDeclareSub mouse_event Lib
"user32" (ByVal dwFlags AsLong, ByVal dx
AsLong, _ ByVal
dy AsLong,
ByVal cButtons AsLong, _ ByVal
dwExtraInfo AsLong)
#End If PrivateConst MOUSEEVENTF_MOVE
= &H1
' mouse move
OleCreatePictureIndirect
PrivateDeclareFunction OleCreatePictureIndirect
Lib "oleaut32.dll" (PicDesc
As PICTDESC, RefIID As GUID,
ByVal fPictureOwnsHandle AsLong, IPic As IPicture)
AsLong
Private Type PICTDESC
Size As Long
Type As Long
hPic As Long
hPal As Long
End Type
PrivateDeclare PtrSafe
Function OleCreatePictureIndirect
Lib "oleaut32.dll" (PicDesc
As PICTDESC, RefIID As GUID,
ByVal fPictureOwnsHandle As
LongPtr, IPic As IPicture)
As LongPtr
PrivateType PICTDESC
Size AsLong TypeAsLong
hPic As LongPtr
hPal As LongPtr EndType
This is another one of the few API functions that require the Win64 compile
constant:
#If VBA7 Then
#If Win64 Then PrivateDeclare PtrSafe Function
SetWindowLongPtr Lib "USER32" Alias "SetWindowLongPtrA"
(ByVal hWnd As LongPtr,
ByVal nIndex AsLong, ByVal dwNewLong
As LongPtr) As LongPtr
#Else PrivateDeclareFunction SetWindowLongPtr
Lib "USER32" Alias "SetWindowLongA" (ByVal
hWnd As LongPtr, ByVal
nIndex As Long, ByVal
dwNewLong As LongPtr) As LongPtr
#End If
#Else PrivateDeclareFunction SetWindowLong Lib
"USER32" Alias "SetWindowLongA" (ByVal hWnd
As Long, ByVal nIndex
AsLong,
ByVal dwNewLong As Long)
As Long
#End If
#If VBA7 Then Type SHFILEOPSTRUCT
hWnd As LongPtr
wFunc As Long
pFrom AsString
pTo AsString
fFlags AsInteger
fAborted AsBoolean
hNameMaps As
Longptr
sProgress AsString EndType Declare PtrSafe
Function SHFileOperation Lib "shell32.dll"
Alias "SHFileOperationA" _
(lpFileOp As SHFILEOPSTRUCT)
AsLongPtr
#Else Type SHFILEOPSTRUCT
hWnd AsLong
wFunc AsLong
pFrom AsString
pTo AsString
fFlags AsInteger
fAborted AsBoolean
hNameMaps AsLong
sProgress AsString EndType DeclareFunction
SHFileOperation Lib "shell32.dll" Alias "SHFileOperationA"
_
(lpFileOp As SHFILEOPSTRUCT)
As Long
#End If
Private Declare PtrSafe Function SHGetPathFromIDList Lib "shell32.dll" Alias
"SHGetPathFromIDListA" _
(ByVal pidl As LongPtr, ByVal pszPath
As String) As Boolean
It's actually pretty easy to determine what requires LongPtr and what can
stay as Long. The only things that require LongPtr are function arguments or
return values that represent addresses in memory. This is because a 64-bit OS
has a memory space that is too large to hold in a Long data type variable. Arguments
or return values that represent data will still be declared Long even in 64-bit.
The SendMessage API is a good example because it uses both types:
PublicDeclare PtrSafe
Function SendMessageA Lib
"user32" (ByVal hWnd As LongPtr,
ByVal wMsg AsLong, _ ByVal wParam AsLongPtr, lParam As Any)
As LongPtr
The first argument -hWnd- is a window handle, which is an address in memory.
The return value is a pointer to a function, which is also an address in memory.
Both of these must be declared LongPtr in 64-bit VBA. The argument wMsg is used
to pass data, so can be Long in both 32-bit and 64-bit.
How to determine what is a memory address and what is data? You just have
to read
the MSDN documentation for the API functions (the C++ version) and it will
tell you. Anything called a handle, pointer, brush or any other object type
will require a LongPtr in 64-bit. Anything that is strictly data can stay as
Long.
Conditional compiling
If your code needs to run on both 32 bit and 64 bit Excel, then another thing
to do is add conditional compilation to your VBA.
Microsoft devised two compile constants to handle this:
VBA7: True if you're using Office 2010, False for older versions
WIN64: True if your Office installation is 64 bit, false for 32 bit.
Since the 64 bit declarations also work on 32 bit Office 2010, all you have
to test for is VBA7:
#If VBA7 Then PrivateDeclare
PtrSafe Function GetDeviceCaps
Lib "gdi32" (ByVal hDC
As LongPtr, ByVal nIndex
AsLong)
AsLong
#Else PrivateDeclareFunction GetDeviceCaps Lib
"gdi32" (ByVal hDC AsLong, ByVal nIndex
AsLong)
AsLong
#End If
And then in the routine where this function is put to use:
#If VBA7 Then Dim hDC As
LongPtr
#Else Dim hDC AsLong
#EndIf Dim lDotsPerInch AsLong 'Get the user's DPI setting
lDotsPerInch = GetDeviceCaps(hDC, LOGPIXELSX)
Other API functions
Have a function declaration which is not on this list? I invite you to send
me your (working and tested!!!) declarations so I can add them here.
I also welcome comments and suggestions on improvements!
Occasionally I see something like:
"lpEnvironment As Any"
What does ANY mean in this context? If it seems like a pointer, should I change it to LongPtr, when doing 64-bit?
The program I inherited has it too, and I never learned that wording...
Thanks.
Comment by: Jan Karel Pieterse (1-12-2020 10:56:00)
The Any keyword is similar to declaring a variable as Variant; it accepts data of any type. That does not mean it is necessarily the correct declaration, each argument of an API function usually is of a specific type. So in my view, using Any is the lazy approach you can often get away with in VBA :-)
#If VBA7 Then
Private Declare PtrSafe Function _
CoRegisterMessageFilter Lib "OLE32.DLL" _
(ByVal lFilterIn As LongPtr, _
ByRef lPreviousFilter As LongPtr) As Long
#Else
Private Declare Function _
CoRegisterMessageFilter Lib "OLE32.DLL" _
(ByVal lFilterIn As Long, _
ByRef lPreviousFilter As Long) As Long
#End If
Have a question, comment or suggestion? Then please use this form.
If your question is not directly related to this web page, but rather a more
general "How do I do this" Excel question, then I advise you to ask
your question here: www.eileenslounge.com.
Comments
Showing last 8 comments of 331 in total (Show All Comments):Comment by: Jan Karel Pieterse (24-8-2020 10:26:00)
The declarations for GetWindowLong were taken directly from the Microsoft documents as available for download here:
http://www.microsoft.com/en-us/download/confirmation.aspx?id=9970
Are you saying the docs are wrong?
Comment by: Jan Karel Pieterse (24-8-2020 10:27:00)
Did you mean to ask a question about those declarations?
Comment by: Jim Meehan (30-11-2020 23:05:00)
"lpEnvironment As Any"
What does ANY mean in this context? If it seems like a pointer, should I change it to LongPtr, when doing 64-bit?
The program I inherited has it too, and I never learned that wording...
Thanks.
Comment by: Jan Karel Pieterse (1-12-2020 10:56:00)
The Any keyword is similar to declaring a variable as Variant; it accepts data of any type. That does not mean it is necessarily the correct declaration, each argument of an API function usually is of a specific type. So in my view, using Any is the lazy approach you can often get away with in VBA :-)
Comment by: andy (22-12-2020 06:10:00)
CoRegisterMessageFilter Lib "OLE32.DLL" _
(ByVal lFilterIn As Long, _
ByRef lPreviousFilter) As Long
-i need to edit make it work for 64bit. can someone help me pleae?
Comment by: Jan Karel Pieterse (22-12-2020 14:49:00)
I expect this:
Private Declare PtrSafe Function _
CoRegisterMessageFilter Lib "OLE32.DLL" _
(ByVal lFilterIn As LongPtr, _
ByRef lPreviousFilter As LongPtr) As Long
#Else
Private Declare Function _
CoRegisterMessageFilter Lib "OLE32.DLL" _
(ByVal lFilterIn As Long, _
ByRef lPreviousFilter As Long) As Long
#End If
Comment by: Andrii (26-12-2020 14:55:00)
Please 32 bit code -> 64 bit code
(TYPE and DECLARE FUNCTION)
Const VK_H = 72
Const VK_E = 69
Const VK_L = 76
Const VK_O = 79
Const VK_ENTER = &HD
Const KEYEVENTF_KEYUP = &H2
Const INPUT_MOUSE = 0
Const INPUT_KEYBOARD = 1
Const INPUT_HARDWARE = 2
Private Type MOUSEINPUT
dx As Long
dy As Long
mouseData As Long
dwFlags As Long
time As Long
dwExtraInfo As Long
End Type
Private Type KEYBDINPUT
wVk As Integer
wScan As Integer
dwFlags As Long
time As Long
dwExtraInfo As Long
End Type
Private Type HARDWAREINPUT
uMsg As Long
wParamL As Integer
wParamH As Integer
End Type
Private Type GENERALINPUT
dwType As Long
xi(0 To 23) As Byte
End Type
Private Declare PtrSafe Function SendInput Lib "user32.dll" (ByVal nInputs As Long, pInputs As GENERALINPUT, ByVal cbSize As Long) As Long
Private Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (pDst As Any, pSrc As Any, ByVal ByteLen As Long)
Sub Test()
Shell "NotePad.EXE", 1
SendKey VK_H
SendKey VK_E
SendKey VK_L
SendKey VK_L
SendKey VK_O
SendKey VK_ENTER
SendKey VK_H
SendKey VK_E
SendKey VK_L
SendKey VK_L
SendKey VK_O
End Sub
Private Sub SendKey(bKey As Byte)
Dim GInput(0 To 1) As GENERALINPUT, KInput As KEYBDINPUT
KInput.wVk = bKey
KInput.dwFlags = 0
GInput(0).dwType = INPUT_KEYBOARD
CopyMemory GInput(0).xi(0), KInput, Len(KInput)
KInput.wVk = bKey
KInput.dwFlags = KEYEVENTF_KEYUP
GInput(1).dwType = INPUT_KEYBOARD
CopyMemory GInput(1).xi(0), KInput, Len(KInput)
Call SendInput(2, GInput(0), Len(GInput(0)))
End Sub
Thank you very much !
Comment by: Jan Karel Pieterse (28-12-2020 11:45:00)
You should be able to decide what the declaration looks like from the Microsoft dcoumentation:
https://docs.microsoft.com/en-us/windows/win32/api/winuser/nf-winuser-sendinput
https://docs.microsoft.com/en-us/previous-versions/windows/desktop/legacy/aa366535%28v=vs.85%29
Have a question, comment or suggestion? Then please use this form.
If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.