Declaring API functions for 64 bit Office (and Mac Office)
Content
- Introduction
- Links
- Declarations by API function
- Which Longs should become LongPtr?
- Conditional compiling
- Other API functions
Introduction
If you develop VBA code for multiple versions of Office, you may face a challenge: ensuring your code works on both 32 bit and 64 bit platforms.
This page is meant to be 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.
All of these are Windows API calls. Some have Mac equivalents however (like the CopyMemory one). I'll try to add those as I find them.
Links
Of course Microsoft documents how to do this. There is an introductory article on Microsoft MSDN:
Compatibility Between the 32-bit and 64-bit Versions of Office 2010
That article describes the how-to's to properly write the declarations. What is missing is which type declarations go with which API function or sub.
Microsoft has provided an updated version of the Win32API.txt with all proper declarations available for download here:
Office 2010 Help Files: Win32API_PtrSafe with 64-bit Support
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:
C:\Office 2010 Developer Resources\Documents\Office2010Win32API_PtrSafe
You can find a list of the old Win32 API declarations here:
Visual Basic Win32 API Declarations
Microsoft also published a tool to check your code for 64 bit related problems, called the Microsoft Office Code Compatibility inspector addin.
API functions that were added/modified in 64-bit Windows: http://msdn.microsoft.com/en-us/library/aa383663(VS.85).aspx
API Functions by Windows release:
http://msdn.microsoft.com/en-us/library/aa383687(VS.85).aspx
Utter Access API declarations (a comprehensive list of many declarations)
Last, but certainly not least: Dennis Walentin has built an API viewer that is really helpful. You can find the API viewer here.
Declarations by API function
CloseClipboard
Declare PtrSafe Function CloseClipboard Lib "User32" () As LongPtr
#Else
Declare Function CloseClipboard Lib "User32" () As Long
#End If
CopyMemory
Private Declare PtrSafe Function CopyMemory_byVar Lib "libc.dylib" Alias "memmove" (ByRef dest As Any, ByRef src As Any, ByVal size As Long) As LongPtr
#Else
#If VBA7 Then
Public Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" _
(ByRef destination As Any, ByRef SOURCE As Any, ByVal Length As LongPtr)
#Else
Public Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" _
(ByRef destination As Any, ByRef SOURCE As Any, ByVal Length As Long)
#End If
#End If
CreateProcess
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
#If VBA7 Then
Declare PtrSafe Function CreateProcess Lib "kernel32" _
Alias "CreateProcessA" (ByVal lpApplicationName As String, _
ByVal lpCommandLine As String, _
lpProcessAttributes As SECURITY_ATTRIBUTES, _
lpThreadAttributes As SECURITY_ATTRIBUTES, _
ByVal bInheritHandles As Long, _
ByVal dwCreationFlags As Long, _
lpEnvironment As Any, _
ByVal lpCurrentDriectory As String, _
lpStartupInfo As STARTUPINFO, _
lpProcessInformation As PROCESS_INFORMATION) As LongPtr
Const INFINITE = &HFFFF
Const STARTF_USESHOWWINDOW = &H1
Private Enum enSW
SW_HIDE = 0
SW_NORMAL = 1
SW_MAXIMIZE = 3
SW_MINIMIZE = 6
End Enum
Private Type PROCESS_INFORMATION
hProcess As LongPtr
hThread As LongPtr
dwProcessId As Long
dwThreadId As Long
End Type
Private Type STARTUPINFO
cb As Long
lpReserved As String
lpDesktop As String
lpTitle As String
dwX As Long
dwY As Long
dwXSize As Long
dwYSize As Long
dwXCountChars As Long
dwYCountChars As Long
dwFillAttribute As Long
dwFlags As Long
wShowWindow As Integer
cbReserved2 As Integer
lpReserved2 As Byte
hStdInput As LongPtr
hStdOutput As LongPtr
hStdError As LongPtr
End Type
Private Type SECURITY_ATTRIBUTES
nLength As Long
lpSecurityDescriptor As LongPtr
bInheritHandle As Long
End Type
Private Enum enPriority_Class
NORMAL_PRIORITY_CLASS = &H20
IDLE_PRIORITY_CLASS = &H40
HIGH_PRIORITY_CLASS = &H80
End Enum
#Else
Declare Function CreateProcess Lib "kernel32" _
Alias "CreateProcessA" (ByVal lpApplicationName As String, _
ByVal lpCommandLine As String, _
lpProcessAttributes As SECURITY_ATTRIBUTES, _
lpThreadAttributes As SECURITY_ATTRIBUTES, _
ByVal bInheritHandles As Long, _
ByVal dwCreationFlags As Long, _
lpEnvironment As Any, _
ByVal lpCurrentDriectory As String, _
lpStartupInfo As STARTUPINFO, _
lpProcessInformation As PROCESS_INFORMATION) As Long
Const INFINITE = &HFFFF
Const STARTF_USESHOWWINDOW = &H1
Private Enum enSW
SW_HIDE = 0
SW_NORMAL = 1
SW_MAXIMIZE = 3
SW_MINIMIZE = 6
End Enum
Private Type PROCESS_INFORMATION
hProcess As Long
hThread As Long
dwProcessId As Long
dwThreadId As Long
End Type
Private Type STARTUPINFO
cb As Long
lpReserved As String
lpDesktop As String
lpTitle As String
dwX As Long
dwY As Long
dwXSize As Long
dwYSize As Long
dwXCountChars As Long
dwYCountChars As Long
dwFillAttribute As Long
dwFlags As Long
wShowWindow As Integer
cbReserved2 As Integer
lpReserved2 As Byte
hStdInput As Long
hStdOutput As Long
hStdError As Long
End Type
Private Type SECURITY_ATTRIBUTES
nLength As Long
lpSecurityDescriptor As Long
bInheritHandle As Long
End Type
Private Enum enPriority_Class
NORMAL_PRIORITY_CLASS = &H20
IDLE_PRIORITY_CLASS = &H40
HIGH_PRIORITY_CLASS = &H80
End Enum
#End If
Private Function SuperShell(ByVal App As String, ByVal WorkDir As String, dwMilliseconds As Long, _
ByVal start_size As enSW, ByVal Priority_Class As enPriority_Class) As Boolean
Dim pclass As Long
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
End If
End Function
Sub Test()
Dim sFile As String
'Set the dialog's title
sFile = Application.GetOpenFilename("Executables (*.exe), *.exe", , "")
SuperShell sFile, Left(sFile, InStrRev(sFile, "\")), 0, SW_NORMAL, HIGH_PRIORITY_CLASS
End Sub
DrawMenuBar
Private Declare PtrSafe Function DrawMenuBar Lib "user32" (ByVal hWnd As LongPtr) As Long
#Else
Private Declare Function DrawMenuBar Lib "user32" (ByVal hWnd As Long) As Long
#End If
EmptyClipboard
Declare PtrSafe Function EmptyClipboard Lib "User32" () As LongPtr
#Else
Declare Function EmptyClipboard Lib "User32" () As Long
#End If
FindWindow
Private Declare PtrSafe Function FindWindow Lib "USER32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr
#Else
Private Declare Function FindWindow Lib "USER32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
#End If
FindWindowEx
Private Declare PtrSafe Function FindWindowEx Lib "USER32" _
Alias "FindWindowExA" (ByVal hWnd1 As LongPtr, ByVal hWnd2 As LongPtr, _
ByVal lpsz1 As String, ByVal lpsz2 As String) As LongPtr
#Else
Private Declare Function FindWindowEx Lib "USER32" _
Alias "FindWindowExA" (ByVal hWnd1 As Long, ByVal hWnd2 As Long, _
ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
#End If
GdipCreateBitmapFromFile
Private Declare PtrSafe Function GdipCreateBitmapFromFile Lib "GDIPlus" (ByVal filename As LongPtr, bitmap As LongPtr) As LongPtr
#Else
Private Declare Function GdipCreateBitmapFromFile Lib "GDIPlus" (ByVal filename As Long, bitmap As Long) As Long
#End If
GdipCreateHBITMAPFromBitmap
Private Declare PtrSafe Function GdipCreateHBITMAPFromBitmap Lib "GDIPlus" (ByVal bitmap As LongPtr, hbmReturn As LongPtr, ByVal background As Long) As LongPtr
#Else
Private Declare Function GdipCreateHBITMAPFromBitmap Lib "GDIPlus" (ByVal bitmap As Long, hbmReturn As Long, ByVal background As Long) As Long
#End If
GdipDisposeImage
Private Declare PtrSafe Function GdipDisposeImage Lib "GDIPlus" (ByVal image As LongPtr) As LongPtr
#Else
Private Declare Function GdipDisposeImage Lib "GDIPlus" (ByVal image As Long) As Long
#End If
GdiplusShutdown
Private Declare PtrSafe Function GdiplusShutdown Lib "GDIPlus" (ByVal token As LongPtr) As LongPtr
#Else
Private Declare Function GdiplusShutdown Lib "GDIPlus" (ByVal token As Long) As Long
#End If
GdiplusStartup
Private Declare PtrSafe Function GdiplusStartup Lib "GDIPlus" (token As LongPtr, inputbuf As GdiplusStartupInput, Optional ByVal outputbuf As LongPtr = 0) As LongPtr
Private Type GdiplusStartupInput
GdiplusVersion As Long
DebugEventCallback As LongPtr
SuppressBackgroundThread As Long
SuppressExternalCodecs As Long
End Type
#Else
Private Declare Function GdiplusStartup Lib "GDIPlus" (token As Long, inputbuf As GdiplusStartupInput, Optional ByVal outputbuf As Long = 0) As Long
Private Type GdiplusStartupInput
GdiplusVersion As Long
DebugEventCallback As Long
SuppressBackgroundThread As Long
SuppressExternalCodecs As Long
End Type
#End If
GetClassName
Private Declare PtrSafe Function GetClassName Lib "user32" Alias "GetClassNameA" _
(ByVal hWnd As LongPtr, ByVal lpClassName As String, _
ByVal nMaxCount As LongPtr) As Long
#Else
Private Declare Function GetClassName Lib "user32" Alias "GetClassNameA" _
(ByVal hWnd As Long, ByVal lpClassName As String, _
ByVal nMaxCount As Long) As Long
#End If
GetDiskFreeSpaceEx
Private Declare PtrSafe Function GetDiskFreeSpaceEx Lib "kernel32" Alias _
"GetDiskFreeSpaceExA" (ByVal lpDirectoryName As String, _
lpFreeBytesAvailableToCaller As Currency, lpTotalNumberOfBytes As _
Currency, lpTotalNumberOfFreeBytes As Currency) As LongPtr
#Else
Private Declare Function GetDiskFreeSpaceEx Lib "kernel32" _
Alias "GetDiskFreeSpaceExA" (ByVal lpDirectoryName As String, _
lpFreeBytesAvailableToCaller As Currency, _
lpTotalNumberOfBytes As Currency, _
lpTotalNumberOfFreeBytes As Currency) As Long
#End If
GetDC
Private Declare PtrSafe Function GetDC Lib "user32" (ByVal hWnd As LongPtr) As LongPtr
#Else
Private Declare Function GetDC Lib "user32" (ByVal hWnd As Long) As Long
#End If
GetDesktopWindow
Private Declare PtrSafe Function GetDesktopWindow Lib "user32" () As LongPtr
#Else
Private Declare Function GetDesktopWindow Lib "user32" () As Long
#End If
getDeviceCaps
Private Declare PtrSafe Function GetDeviceCaps Lib "gdi32" (ByVal hDC As LongPtr, ByVal nIndex As Long) As Long
#Else
Private Declare Function GetDeviceCaps Lib "gdi32" (ByVal hDC As Long, ByVal nIndex As Long) As Long
#End If
GetDriveType
Private Declare PtrSafe Function GetDriveType Lib "kernel32" Alias _
"GetDriveTypeA" (ByVal sDrive As String) As LongPtr
#Else
Private Declare Function GetDriveType Lib "kernel32" Alias _
"GetDriveTypeA" (ByVal sDrive As String) As Long
#End If
GetExitCodeProcess
Declare PtrSafe Function GetExitCodeProcess Lib "kernel32" (ByVal _
hProcess As LongPtr, lpExitCode As Long) As Long
#Else
Declare Function GetExitCodeProcess Lib "kernel32" (ByVal _
hProcess As Long, lpExitCode As Long) As Long
#End If
GetForegroundWindow
Declare PtrSafe Function GetForegroundWindow Lib "user32.dll" () As LongPtr
#Else
Declare Function GetForegroundWindow Lib "user32.dll" () As Long
#End If
GetFrequency
Private Declare PtrSafe Function GetFrequency Lib "kernel32" Alias "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
#Else
Private Declare Function GetFrequency Lib "kernel32" Alias "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
#End If
GetKeyState
Declare PtrSafe Function GetKeyState Lib "USER32" (ByVal vKey As Long) As Integer
#Else
Declare Function GetKeyState Lib "USER32" (ByVal vKey As Long) As Integer
#End If
GetLastInputInfo
Private Type LASTINPUTINFO
cbSize As LongPtr
dwTime As LongPtr
End Type
Private Declare PtrSafe Sub GetLastInputInfo Lib "USER32" (ByRef plii As LASTINPUTINFO)
#Else
Private Type LASTINPUTINFO
cbSize As Long
dwTime As Long
End Type
Private Declare Sub GetLastInputInfo Lib "USER32" (ByRef plii As LASTINPUTINFO)
#End If
GetOpenFileName
#If VBA7 Then
Private Declare PtrSafe Function GetOpenFileName Lib "comdlg32.dll" Alias _
"GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long
Private Type OPENFILENAME
lStructSize As Long
hwndOwner As LongPtr
hInstance As LongPtr
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As LongPtr
lpfnHook As LongPtr
lpTemplateName As String
End Type
#Else
Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
"GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long
Private Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type
#End If
'/////////////////////////////////
'// End code GetOpenFileName //
'/////////////////////////////////
Private Function GetMyFile(strTitle As String) As String
Dim OpenFile As OPENFILENAME
Dim lReturn As Long
OpenFile.lpstrFilter = ""
OpenFile.nFilterIndex = 1
OpenFile.hwndOwner = 0
OpenFile.lpstrFile = String(257, 0)
#If VBA7 Then
OpenFile.nMaxFile = LenB(OpenFile.lpstrFile) - 1
OpenFile.lStructSize = LenB(OpenFile)
#Else
OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
OpenFile.lStructSize = Len(OpenFile)
#End If
OpenFile.lpstrFileTitle = OpenFile.lpstrFile
OpenFile.nMaxFileTitle = OpenFile.nMaxFile
OpenFile.lpstrInitialDir = "C:\"
OpenFile.lpstrTitle = strTitle
OpenFile.flags = 0
lReturn = GetOpenFileName(OpenFile)
If lReturn = 0 Then
GetMyFile = ""
Else
GetMyFile = Trim(Left(OpenFile.lpstrFile, InStr(1, OpenFile.lpstrFile, vbNullChar) - 1))
End If
End Function
GetSystemMetrics
Private Declare PtrSafe Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long
#Else
Private Declare Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long
#End If
GetTempPath
Private Declare PtrSafe Function GetTempPath Lib "kernel32" _
Alias "GetTempPathA" (ByVal nBufferLength As LongPtr, _
ByVal lpbuffer As String) As Long
#Else
Private Declare Function GetTempPath Lib "kernel32" _
Alias "GetTempPathA" (ByVal nBufferLength As Long, _
ByVal lpbuffer As String) As Long
#End If
getTickCount
Private Declare PtrSafe Function getTickCount Lib "kernel32" Alias "QueryPerformanceCounter" (cyTickCount As Currency) As Long
#Else
Private Declare Function getTickCount Lib "kernel32" Alias "QueryPerformanceCounter" (cyTickCount As Currency) As Long
#End If
timeGetTime
Private Declare PtrSafe Function timeGetTime Lib "winmm.dll" () As Long
#Else
Private Declare Function timeGetTime Lib "winmm.dll" () As Long
#End If
GetWindow
Private Declare PtrSafe Function GetWindow Lib "user32" _
(ByVal hWnd As LongPtr, ByVal wCmd As Long) As LongPtr
#Else
Private Declare Function GetWindow Lib "user32" _
(ByVal hWnd As Long, ByVal wCmd As Long) As Long
#End If
GetWindowLong
This is one of the few API functions that requires the Win64 compile constant:
#If Win64 Then
Private Declare PtrSafe Function GetWindowLongPtr Lib "USER32" Alias "GetWindowLongPtrA" (ByVal hWnd As LongPtr, ByVal nIndex As Long) As LongPtr
#Else
Private Declare PtrSafe Function GetWindowLongPtr Lib "USER32" Alias "GetWindowLongA" (ByVal hWnd As LongPtr, ByVal nIndex As Long) As LongPtr
#End If
#Else
Private Declare Function GetWindowLong Lib "USER32" Alias "GetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long) As Long
#End If
GetWindowsDirectory
Private Declare PtrSafe Function GetWindowsDirectory& Lib "kernel32" Alias _
"GetWindowsDirectoryA" (ByVal lpbuffer As String, _
ByVal nSize As LongPtr)
#Else
Private Declare Function GetWindowsDirectory& Lib "kernel32" Alias _
"GetWindowsDirectoryA" (ByVal lpbuffer As String, _
ByVal nSize As Long)
#End If
GetWindowText
Private Declare PtrSafe Function GetWindowText Lib "user32" Alias "GetWindowTextA" _
(ByVal hWnd As LongPtr, ByVal lpString As String, _
ByVal cch As LongPtr) As Long
#Else
Private Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA" _
(ByVal hWnd As Long, ByVal lpString As String, _
ByVal cch As Long) As Long
#End If
GetWindowTextLength
Private Declare PtrSafe Function GetWindowTextLength Lib "user32" Alias "GetWindowTextLengthA" _
(ByVal hWnd As LongPtr) As Long
#Else
Private Declare Function GetWindowTextLength Lib "user32" Alias "GetWindowTextLengthA" _
(ByVal hWnd As Long) As Long
#End If
GlobalAlloc
Private Declare PtrSafe Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, ByVal dwBytes As LongPtr) As LongPtr
#Else
Private Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, ByVal dwBytes As Long) As Long
#End If
GlobalLock
Private Declare PtrSafe Function GlobalLock Lib "kernel32" (ByVal hMem As LongPtr) As LongPtr
#Else
Private Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) As Long
#End If
InternetGetConnectedState
Private Declare PtrSafe Function InternetGetConnectedState _
Lib "wininet.dll" (lpdwFlags As LongPtr, _
ByVal dwReserved As Long) As Boolean
#Else
Private Declare Function InternetGetConnectedState _
Lib "wininet.dll" (lpdwFlags As Long, _
ByVal dwReserved As Long) As Boolean
#End If
IsCharAlphaNumericA
Private Declare PtrSafe Function IsCharAlphaNumericA Lib "user32" (ByVal byChar As Byte) As Long
#Else
Private Declare Function IsCharAlphaNumericA Lib "user32" (ByVal byChar As Byte) As Long
#End If
lstrcpy
Private Declare PtrSafe Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, ByVal lpString2 As Any) As LongPtr
#Else
Private Declare Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, ByVal lpString2 As Any) As Long
#End If
Mouse_Event
Private Declare PtrSafe Sub mouse_event Lib "user32" (ByVal dwFlags As Long, ByVal dx As Long, _
ByVal dy As Long, ByVal cButtons As Long, _
ByVal dwExtraInfo As LongPtr)
#Else
Private Declare Sub mouse_event Lib "user32" (ByVal dwFlags As Long, ByVal dx As Long, _
ByVal dy As Long, ByVal cButtons As Long, _
ByVal dwExtraInfo As Long)
#End If
Private Const MOUSEEVENTF_MOVE = &H1 ' mouse move
OleCreatePictureIndirect
Private Declare PtrSafe Function OleCreatePictureIndirect Lib "oleaut32.dll" (PicDesc As PICTDESC, RefIID As GUID, ByVal fPictureOwnsHandle As LongPtr, IPic As IPicture) As LongPtr
Private Type PICTDESC
Size As Long
Type As Long
hPic As LongPtr
hPal As LongPtr
End Type
#Else
Private Declare Function OleCreatePictureIndirect Lib "oleaut32.dll" (PicDesc As PICTDESC, RefIID As GUID, ByVal fPictureOwnsHandle As Long, IPic As IPicture) As Long
Private Type PICTDESC
Size As Long
Type As Long
hPic As Long
hPal As Long
End Type
#End If
OleTranslateColor
Private Declare PtrSafe Function OleTranslateColor Lib "oleaut32.dll" (ByVal lOleColor As Long, _
ByVal lHPalette As Long, lColorRef As Long) As Long
#Else
Private Declare Function OleTranslateColor Lib "olepro32.dll" (ByVal lOleColor As Long, _
ByVal lHPalette As Long, ByRef lColorRef As Long) As Long
#End If
OpenClipboard
Private Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hWnd As LongPtr) As LongPtr
#Else
Private Declare Function OpenClipboard Lib "user32" (ByVal hWnd As Long) As Long
#End If
OpenProcess
Declare PtrSafe Function OpenProcess Lib "kernel32" (ByVal _
dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal _
dwProcessId As Long) As LongPtr
#Else
Declare Function OpenProcess Lib "kernel32" (ByVal _
dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal _
dwProcessId As Long) As Long
#End If
ReleaseDC
Private Declare PtrSafe Function ReleaseDC Lib "user32" (ByVal hWnd As LongPtr, ByVal hDC As LongPtr) As Long
#Else
Private Declare Function ReleaseDC Lib "user32" (ByVal hWnd As Long, ByVal hDC As Long) As Long
#End If
SendMessage
Private Declare PtrSafe Function SendMessageA Lib "user32" (ByVal hWnd As LongPtr, ByVal wMsg As Long, _
ByVal wParam As LongPtr, lParam As Any) As LongPtr
#Else
Private Declare Function SendMessageA Lib "user32" (ByVal hWnd As Long, ByVal wMsg As Long, _
ByVal wParam As Long, lParam As Any) As Long
#End If
SetActiveWindow
Private Declare PtrSafe Function SetActiveWindow Lib "user32.dll" (ByVal hWnd As LongPtr) As LongPtr
#Else
Private Declare Function SetActiveWindow Lib "user32.dll" (ByVal hWnd As Long) As Long
#End If
SetClipboardData
Private Declare PtrSafe Function SetClipboardData Lib "user32" (ByVal wFormat As LongPtr, ByVal hMem As LongPtr) As LongPtr
#Else
Private Declare Function SetClipboardData Lib "user32" (ByVal wFormat As Long, ByVal hMem As Long) As Long
#End If
SetCurrentDirectory
Private Declare PtrSafe Function SetCurrentDirectoryA Lib "kernel32" (ByVal lpPathName As String) As Long
#Else
Private Declare Function SetCurrentDirectoryA Lib "kernel32" (ByVal lpPathName As String) As Long
#End If
SetWindowLongPtr
This is another one of the few API functions that require the Win64 compile constant:
#If Win64 Then
Private Declare PtrSafe Function SetWindowLongPtr Lib "USER32" Alias "SetWindowLongPtrA" (ByVal hWnd As LongPtr, ByVal nIndex As Long, ByVal dwNewLong As LongPtr) As LongPtr
#Else
Private Declare Function SetWindowLongPtr Lib "USER32" Alias "SetWindowLongA" (ByVal hWnd As LongPtr, ByVal nIndex As Long, ByVal dwNewLong As LongPtr) As LongPtr
#End If
#Else
Private Declare Function SetWindowLong Lib "USER32" Alias "SetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
#End If
SetWindowPos
Private Declare PtrSafe Function SetWindowPos _
Lib "user32" (ByVal hwnd As LongPtr, ByVal hWndInsertAfter As LongPtr, _
ByVal X As Long, ByVal Y As Long, ByVal cx As Long, _
ByVal cy As Long, ByVal wFlags As Long) As Long
#Else
Private Declare Function SetWindowPos _
Lib "user32" (ByVal hwnd As Long, _
ByVal hWndInsertAfter As Long, _
ByVal X As Long, ByVal Y As Long, _
ByVal cx As Long, _
ByVal cy As Long, _
ByVal wFlags As Long) As Long
#End If
SHBrowseForFolder
Private Type BROWSEINFO
hOwner As LongPtr
pidlRoot As LongPtr
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As LongPtr
lParam As LongPtr
iImage As Long
End Type
Private Declare PtrSafe Function SHBrowseForFolder Lib "shell32.dll" Alias "SHBrowseForFolderA" _
(lpBrowseInfo As BROWSEINFO) As LongPtr
#Else
Private Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type
Private Declare Function SHBrowseForFolder Lib "shell32.dll" Alias "SHBrowseForFolderA" _
(lpBrowseInfo As BROWSEINFO) As Long
#End If
Private Const BIF_RETURNONLYFSDIRS = &H1
ShellExecute
Private Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _
ByVal hWnd As LongPtr, ByVal lpOperation As String, ByVal lpFile As String, _
ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As LongPtr
#Else
Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _
ByVal hWnd As Long, ByVal lpOperation As String, ByVal lpFile As String, _
ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
#End If
SHFileOperation
Type SHFILEOPSTRUCT
hWnd As LongPtr
wFunc As Long
pFrom As String
pTo As String
fFlags As Integer
fAborted As Boolean
hNameMaps As Longptr
sProgress As String
End Type
Declare PtrSafe Function SHFileOperation Lib "shell32.dll" Alias "SHFileOperationA" _
(lpFileOp As SHFILEOPSTRUCT) As LongPtr
#Else
Type SHFILEOPSTRUCT
hWnd As Long
wFunc As Long
pFrom As String
pTo As String
fFlags As Integer
fAborted As Boolean
hNameMaps As Long
sProgress As String
End Type
Declare Function SHFileOperation Lib "shell32.dll" Alias "SHFileOperationA" _
(lpFileOp As SHFILEOPSTRUCT) As Long
#End If
SHGetPathFromIDList
Private Declare PtrSafe Function SHGetPathFromIDList Lib "shell32.dll" Alias "SHGetPathFromIDListA" _
(ByVal pidl As LongPtr, ByVal pszPath As String) As Boolean
#Else
Private Declare Function SHGetPathFromIDList Lib "shell32.dll" Alias "SHGetPathFromIDListA" _
(ByVal pidl As Long, ByVal pszPath As String) As Boolean
#End If
SHGetSpecialFolderLocation
Private Declare PtrSafe Function SHGetSpecialFolderLocation Lib _
"shell32.dll" (ByVal hwndOwner As LongPtr, ByVal nFolder As Long, _
pidl As ITEMIDLIST) As LongPtr
Private Type SHITEMID
cb As LongPtr
abID As Byte
End Type
#Else
Private Declare Function SHGetSpecialFolderLocation Lib _
"shell32.dll" (ByVal hwndOwner As Long, ByVal nFolder As Long, _
pidl As ITEMIDLIST) As Long
Private Type SHITEMID
cb As Long
abID As Byte
End Type
#End If
Private Type ITEMIDLIST
mkid As SHITEMID
End Type
timeGetTime
Private Declare PtrSafe Function timeGetTime Lib "winmm.dll" () As Long
#Else
Private Declare Function timeGetTime Lib "winmm.dll" () As Long
#End If
URLDownloadToFile
Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" _
Alias "URLDownloadToFileA" (ByVal pCaller As LongPtr, _
ByVal szURL As String, ByVal szFileName As String, _
ByVal dwReserved As LongPtr, ByVal lpfnCB As LongPtr) As Long
#Else
Private Declare Function URLDownloadToFile Lib "urlmon" _
Alias "URLDownloadToFileA" (ByVal pCaller As Long, _
ByVal szURL As String, ByVal szFileName As String, _
ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
#End If
Which Longs should become LongPtr?
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:
32-bit:
ByVal wParam As Long, lParam As Any) As Long
64 bit:
ByVal wParam As LongPtr, 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:
Private Declare PtrSafe Function GetDeviceCaps Lib "gdi32" (ByVal hDC As LongPtr, ByVal nIndex As Long) As Long
#Else
Private Declare Function GetDeviceCaps Lib "gdi32" (ByVal hDC As Long, ByVal nIndex As Long) As Long
#End If
And then in the routine where this function is put to use:
Dim hDC As LongPtr
#Else
Dim hDC As Long
#End If
Dim lDotsPerInch As Long
'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!
Sorry for the truncations into 3 messages
George
Comment by: Jan Karel Pieterse (8-8-2013 21:03:19) deeplink to this comment
Hi George,
Thanks!!
Comment by: Jan Karel Pieterse (8-8-2013 21:04:11) deeplink to this comment
Hi George,
The best way is to find womeone with 64 bit Excel to test this.
Comment by: Daniel Isoje (29-12-2013 20:33:10) deeplink to this comment
Your post makes it all easy for me now. Before now the 32/64bit issues have been troubling my mind as an access developer.
Many thanks.
Comment by: Erwin Kalvelagen (19-3-2014 16:54:16) deeplink to this comment
Just as a warning: I believe the file Win32API_PtrSafe.TXT contains some errors wrt Excel 64 bit. I got a few "Microsoft Excel has stopped working errors". Most are related to strings.
Here are some details:
http://social.msdn.microsoft.com/Forums/office/en-US/9ad49156-0d29-4c0c-ae30-8e7c11ccd8cf/win32apiptrsafetxt-better-version-available?forum=exceldev
Comment by: Thomas Dette (30-7-2014 10:44:41) deeplink to this comment
Hi,
do you have a "64bit translation" for following APIs
Declare Function SleepEx Lib "kernel32" (ByVal dwMilliseconds As Long, ByVal bAlertable As Long) As Long
Declare Function MoveFileEx Lib "kernel32" Alias "MoveFileExA" (ByVal lpExistingFileName As String, ByVal lpNewFileName As String, ByVal dwFlags As Long) As Long
Declare Function sndPlaySound Lib "winmm.dll" snd dPlaySoundA" ByVal lpszSoundName As String, ByVal uFlags As Long) As Long
Declare Function WritePrivateProfileString& Lib "kernel32" Alias "WritePrivateProfileStringA" (ByVal lpApplicationName As String, ByVal lpKeyName As Any, ByVal lpString As Any, _
ByVal lplFileName As String)
Declare Function GetPrivateProfileString& Lib "kernel32" Alias "GetPrivateProfileStringA" (ByVal lpApplicationName As String, ByVal lpKeyName As String, ByVal lpDefault As String, ByVal lpReturnedString As String, ByVal nSize As Long, ByVal lpFileName As String)
Using ACCESS 2010 64bit.
Thanks for helping.
Thomas.
Comment by: Frank Barsnick (1-9-2014 13:37:24) deeplink to this comment
Hi,
do you have a "64bit translation" for following APIs for reading an writing INI-files:
Private Declare Function WritePrivateProfileString Lib "kernel32" Alias "WritePrivateProfileStringA" (ByVal lpApplicationName As String, lpKeyName As Any, lpString As Any, ByVal lplFileName As String) As Integer
Private Declare Function DeletePrivateProfileSection Lib "kernel32" Alias "WritePrivateProfileStringA" (ByVal Section As String, ByVal NoKey As Long, ByVal NoSetting As Long, ByVal FileName As String) As Long
Thx for your help,
Frank
Comment by: Jan Karel Pieterse (1-9-2014 14:29:45) deeplink to this comment
Hi Frank,
Sure:
Declare PtrSafe Function WritePrivateProfileString Lib "kernel32" Alias "WritePrivateProfileStringA" (ByVal lpApplicationName As String, ByVal lpKeyName As Any, ByVal lpString As Any, ByVal lpFileName As String) As Long
I was unable to find the Delete one though!
Comment by: Alexander (29-9-2014 12:32:47) deeplink to this comment
Error in declaration SHBrowseForFolder. To 64-bit returned type must be LongPtr:
Private Declare PtrSafe Function SHBrowseForFolder Lib "shell32.dll" Alias "SHBrowseForFolderA" _
(lpBrowseInfo As BROWSEINFO) As LongPtr
Comment by: Jan Karel Pieterse (29-9-2014 13:09:12) deeplink to this comment
Hi Alexander,
Thanks for pointing that out!
I have edited the page accordingly.
Comment by: LHEN (20-10-2014 10:20:12) deeplink to this comment
Hi,
do you have a "64bit translation" for following APIs?
Public Declare Function WSAGetLastError Lib "WSOCK32.DLL" () As Long
'Returns the socket error
Public Declare Function WSAStartup Lib "WSOCK32.DLL" _
(ByVal wVersionRequired As Long, lpWSADATA As WSADATA) As Long
'Initialize sockets
Public Declare Function WSACleanup Lib "WSOCK32.DLL" () As Long
'Clean up sockets
Public Declare Function gethostname Lib "WSOCK32.DLL" _
(ByVal szHost As String, ByVal dwHostLen As Long) As Long
'Get Host Name
Public Declare Function gethostbyname Lib "WSOCK32.DLL" _
(ByVal szHost As String) As Long
Many thanks :)
Comment by: Jan Karel Pieterse (20-10-2014 10:26:56) deeplink to this comment
Hi LHEN,
I'm sorry, but I don't.
Best to search MSDN for the functions and try to translate them yourself.
Comment by: Dave (14-11-2014 04:35:26) deeplink to this comment
Nice Work Jan!
Comment by: Nigel Heffernan (14-11-2014 16:41:48) deeplink to this comment
OpenProcess and GetExitCodeProcess would be useful: both are required for the widely-used ShellAndWait() function.
Note that they are quite tricky: while the handles and pointers are clearly LongPtr in the 64-bit environment, it's not clear whether the Process ID and the exit codes are Long or LongLong integers.
Comment by: Jan Karel Pieterse (17-11-2014 09:46:08) deeplink to this comment
Hi Nigel,
Added!
Comment by: Zak (19-11-2014 11:08:33) deeplink to this comment
Hi,
Hi to All,
Can anyone help me regarding my problem?
This is the scenario.
I have a .mdb file generated using Access 2010.
I tried to open the file using Access 2013.
After some modifications in the connections, I have a problem.
I have a browse button that when I click, resulted to this error.
Run-time Error '452'
Can't find DLL entry point 59 in msaccess.exe
When I debug, I think this portion have problem.
(ByVal strPath As String, ByVal strDrive As String, _
ByVal strDir As String, ByVal strFName As String, ByVal strExt As String)
*I just added the PtrSafe for 64-bit.
I will surely appreciate all your feedbacks.
Thank you very much.
Comment by: Dan (27-11-2014 13:18:44) deeplink to this comment
Hi,
I need to make my code available to 32-Bit & 64-Bit Excel users, with both VBA7 and earlier verisons.
Can I not just use the
I'm failing to see how this could be detrimental. Why should I be only converting certain Longs to LongPtr?
Any guidance is appreciated.
Comment by: Jan Karel Pieterse (27-11-2014 14:31:29) deeplink to this comment
Hi Dan,
To be honest, I'm not entirely sure what to answer here. My gut feeling says that the arguments you pass to a Win API need to be of the right type. I am not convinced LongPtr would be equally good as Long when the argument is supposed to be a Long. My gut feeling also whispers to me that this is the type of thing that lets vulnerabilities creep into software, but perhaps I'm paranoid :-) .
Comment by: Nigel Heffernan (21-1-2015 18:10:05) deeplink to this comment
Hi Jan -
Thanks for the code for OpenProcess and GetExitCodeProcess.
I notice that you're not using LongLong anywhere in these declarations: do you have examples of Win64 API functions that do require them?
This isn't a matter of idle curiosity: other VBA Gurus are peppering their API declarations with LongLong and I think that they are occasionally wrong - but I think they are safer doing that than taking the risk of receiving a LongLong resource identifier or parameter into a Long integer, and passing the truncated result into another API call. Windows might throw an exception that VBA can handle as an error; it might crash Excel; or it might truncate the value silently and perform some arcane API function on the wrong resource.
Also, there are environments where LongPtr is available, but LongLong isn't.
This means that GetWindowLong isn't the only API declaration that needs three separate segments of conditional compilation; take a look at this, and consider what it means if the only place I can put a breakpoint is
'lParam = 6432'
Public Sub TestEnv()
#If VBA7 And Win64 Then
' 64 bit Excel under 64-bit windows
' We can use LongPtr and LongLong
Dim hWnd As LongPtr
Dim lParam As LongLong
lParam = 6464
#ElseIf VBA7 Then
' 64 bit Excel in all environments
' We can use LongPtr
Dim hWnd As LongPtr
Dim lParam As Long
lParam = 6432
#Else
' 32 bit Excel
' Any length you like, as long as it's Long
Dim hWnd As Long
Dim lParam As Long
lParam = 32
#End If
End Sub
I think we've all underestimated the complexity of the task we face in upgrading VBA code to 64-bit.
Comment by: Nigel Heffernan (21-1-2015 19:41:16) deeplink to this comment
Dan,
You need to do some reading on Pointers, and why 'PtrSafe' matters. Google for 'Dangling Pointer' and see what you get.
What follows is my own understanding, in the language we use as VBA developers; and I am well aware that it contains oversimplifications that push it beyond mere errors and into the logical abyss that philosophers refer to as 'Not Even Wrong'.
A Pointer is the memory address of a resource - maybe the address of the Long integer you're using right now; maybe the address of an object which needs to be in memory when you need it, or a window handle; or, maybe, the address of a function that an API Timer has been told to call in sixty seconds' time.
When that address is incorrect, or when the resource at that address isn't what you expected, things can go badly wrong; so Microsoft have introduced new functions labelled 'Pointer-Safe', which use a special type of integer called 'LongPtr' for memory addresses; and a compiler that reads 'PtrSafe' and 'LongPtr' will create runtime code with new, improved, error-tolerant logic to manage these pointers and the resources in the memory addresses they point to.
You can still store addresses in a LongLong integer: a pointer is just a number. But the compiler won't do that new, improved pointer management on any old integer: you need to declare it as 'LongPtr'.
I do not know what will happen if you declare an everyday integer as a LongPtr, and use it in your code as (say) a loop counter.
My best guess is that things will run very slowly - every time the integer is changed, there will be a series of uneccessary checks to see if there's a memory address with a pointer already using 'your' number - and I would guess that an error-handler (or a complicated redirection and reallocation routine) will be called whenever your integer-declared-as-LngPtr is changed to a value that represents a pointer to memory that's already in use.
Let me know how your application works when you try that.
Comment by: John (19-3-2015 22:38:51) deeplink to this comment
Hello, I have a problem with system tray icon. Icon cannot show and after minimized nothing happened. Do you know how to update vba code to win7 64bit?
Best regards
Comment by: Jan Karel Pieterse (20-3-2015 13:42:45) deeplink to this comment
Hi John,
What code?
Comment by: Randy (28-4-2015 05:58:08) deeplink to this comment
How do you get VBA to recognize the VBA7 constant? I'm running Office 2010 64-bit Excel. I declared a DLL function as follows:
#IF VBA7 then
Declare PtrSafe Function ...
#ELSE
Declare Function ...
#END IF
The compiler flags the statement after the #ELSE as incorrect and won't compile.
Comment by: Jan Karel Pieterse (28-4-2015 08:39:36) deeplink to this comment
Hi Randy,
The VBA7 compile constant should be recognized "out of the box". When precisely do you get the compile error?
Comment by: Randy (28-4-2015 20:45:19) deeplink to this comment
Here's an example
#If VBA7 Then
Declare PtrSafe Function Foo Lib "Mylib" () As Double
#Else
Declare Function Foo lib "Mylib" () as Double
#End If
I can't move past the declaration following the #Else. A message box pops up saying "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."
Comment by: Jan Karel Pieterse (29-4-2015 07:40:28) deeplink to this comment
Hi Randy,
You can igonre those messages as far as I know (though you should not be getting them). You can avoid these silly compile error messages during editing by turning them off in the Editor options:
Tools, Options, uncheck "Auto syntax check".
Comment by: wareko (9-5-2015 04:02:32) deeplink to this comment
Hi, I have a question.
In the above API functios list, there are two declarations for each function.
For example,
GetWindow has two declarations.
Public Declare Function GetWindow Lib "USER32" (ByVal hWnd As Long, ByVal wCmd As Long) As Long
Public Declare PtrSafe Function GetWindow Lib "USER32" (ByVal hWnd As LongPtr, ByVal wCmd As LongPtr) As LongPtr
My question is,
Which constant VBA7 or WIn64 I should use?
For example of the two declarations below, which declaration is good?
I am confusing...
'Declaration using VBA7
#If VBA7 Then
Public Declare PtrSafe Function GetWindow Lib "USER32" (ByVal hWnd As LongPtr, ByVal wCmd As LongPtr) As LongPtr
#Else
Public Declare Function GetWindow Lib "USER32" (ByVal hWnd As Long, ByVal wCmd As Long) As Long
#End If
'Declaration using Win64
#If Win64 Then
Public Declare PtrSafe Function GetWindow Lib "USER32" (ByVal hWnd As LongPtr, ByVal wCmd As LongPtr) As LongPtr
#Else
Public Declare Function GetWindow Lib "USER32" (ByVal hWnd As Long, ByVal wCmd As Long) As Long
#End If
Comment by: Jan Karel Pieterse (9-5-2015 11:45:20) deeplink to this comment
Hi Wareko,
VBA7 suffices in almost all cases. In case there is an exception it is mentioned in the sample code above.
Comment by: wareko (9-5-2015 12:29:43) deeplink to this comment
Thank you for your quick reply!
Comment by: Frank (28-5-2015 14:11:31) deeplink to this comment
Hi,
I'm certainly out of my element here, and I hope you can help. I'm using Excel 2010, and I was trying to create a macro whereby a user would click a link, and it would pass the product number to a Windows Explorer search function. I found the code below online that opens the search dialogue box.
It worked to open the search dialogue window, but it wasn't quite what I was looking for, so I removed the code and module. However, the code is still there somewhere, because when I try to step through any other module, the search dialogue window pops back up, and I can't get rid of it.
I'm on a network, and when I undocked my computer and went home last night and worked, all was fine. But when I came back to work today and redocked the computer, the issue was back.
Can you give me an idea of what I've done and how to fix it, please? Thank you!
=====================================
'API declaration for the windows "Search Results" dialog
Private Declare Function ShellSearch& Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hwnd As Long, _
ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, _
ByVal lpDirectory As String, _
ByVal nShowCmd As Long)
Private Const SW_SHOWNORMAL = 1
Sub ShowWindowsSearchDialog_API()
' Specified drive to Search
Const szSDrive As String = "C:\"
ShellSearch 0, "find", szSDrive, "", "", SW_SHOWNORMAL
End Sub
Comment by: Jan Karel Pieterse (28-5-2015 14:57:31) deeplink to this comment
Hi Frank,
Probably the code is in a file that loads when you start Excel. You should be able to locate the code by looking at all open projects in the Project explorer of the VBA editor and doing a find on each of them?
Comment by: Frank (28-5-2015 15:35:52) deeplink to this comment
Hi Jan,
Thank you for getting back to me, and so quickly! Part of my conundrum is that there are NO files open. The only thing visible in the Project Explorer is the Personal.xlsb, and I've made sure that even that is empty (including Sheet1 and ThisWorkbook).
And why would this be happening when my computer is docked to the network, but when I undock it and take it home, everything is fine?
Thank you very much for considering my issue - it's VERY frustrating.
Frank
Comment by: Jan Karel Pieterse (28-5-2015 16:06:34) deeplink to this comment
Hi Frank,
Does it also do this when you open Excel in safe mode (press and hold control key during Excel's startup)?
Comment by: Frank (28-5-2015 22:12:56) deeplink to this comment
Hi again Jan,
I'm really at a loss for what happened, but I ended up restarting my computer this afternoon for an IT update, and when it booted back up, all was well with Excel. I had completely shut down my computer yesterday when I undocked, and I was glad it was ok when I got home. And I shut down again before coming back into work and redocking. And I was very surprised to see the issue had returned after redocking and booting back up. So I'm again surprised that it went away after restarting the computer this afternoon.
In the end, I'm glad things are back to normal, but I'd like to know what caused that.
Again, thank you very much for taking the time and interest in trying to help me resolve this issue.
Best regards,
Frank
Comment by: Alan Elston (23-6-2015 00:14:59) deeplink to this comment
Hi,
. I have only basic VBA Knowledge.
. I have a code that is working for me in 32 Bit Vista XL 2010
. The code does not work in an identical system but XL 2007
. Initially the problem appears to be these lines, which error in XL 2007 but not XL 2010
Declare PtrSafe Function GlobalLock Lib "kernel32" (ByVal hMem As Long) As Long
Declare PtrSafe Function GlobalLock Lib "kernel32" (ByVal hMem As Long) As Long
Declare PtrSafe Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, ByVal dwBytes As Long) As Long
Declare PtrSafe Function CloseClipboard Lib "User32" () As Long
Declare PtrSafe Function OpenClipboard Lib "User32" (ByVal hwnd As Long) As Long
Declare PtrSafe Function EmptyClipboard Lib "User32" () As Long
Declare PtrSafe Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, ByVal lpString2 As Any) As Long
Declare PtrSafe Function SetClipboardData Lib "User32" (ByVal wFormat As Long, ByVal hMem As Long) As Long
.
. Sorry I cannot give more detail but my knowledge of VBA is limited.
. Is there any obvious reason for my problem?
. Thanks
Alan
Comment by: Jan Karel Pieterse (23-6-2015 07:35:14) deeplink to this comment
Hi Alan,
The quick and dirty way to make this work on Excel 2007 is this:
Declare PtrSafe Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) As Long
Declare PtrSafe Function GlobalLock Lib "kernel32" (ByVal hMem As Long) As Long
Declare PtrSafe Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, ByVal dwBytes As Long) As Long
Declare PtrSafe Function CloseClipboard Lib "User32" () As Long
Declare PtrSafe Function OpenClipboard Lib "User32" (ByVal hwnd As Long) As Long
Declare PtrSafe Function EmptyClipboard Lib "User32" () As Long
Declare PtrSafe Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, ByVal lpString2 As Any) As Long
Declare PtrSafe Function SetClipboardData Lib "User32" (ByVal wFormat As Long, ByVal hMem As Long) As Long
#Else
Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) As Long
Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) As Long
Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, ByVal dwBytes As Long) As Long
Declare Function CloseClipboard Lib "User32" () As Long
Declare Function OpenClipboard Lib "User32" (ByVal hwnd As Long) As Long
Declare Function EmptyClipboard Lib "User32" () As Long
Declare Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, ByVal lpString2 As Any) As Long
Declare Function SetClipboardData Lib "User32" (ByVal wFormat As Long, ByVal hMem As Long) As Long
#End If
I haven't tried whether these are correct for 64 bit Office however!
Comment by: Alan Elston (23-6-2015 12:52:48) deeplink to this comment
Hi.
. Many thanks Jan Karel Pieterse. With your Mod the code works for me now in both XL 2007 and XL 2010. I was googling similar things but was way out of my depth to apply them. Thanks for giving me the final full solution. I note that in XL 2007 the first Declare PtrSafe lines still come up Red, indicating error, but the code works , by virtue , I think of the # which somehow works so that that code is done prior to the compiling.
. I cannot check if the code works in 64 bit. But I highly expect it as the original author has the original code working in 64 bit:
http://www.excelforum.com/tips-and-tutorials/1089404-posting-an-html-table-in-a-thread.html
Comment by: Alan Elston (23-6-2015 12:57:22) deeplink to this comment
. FYI. Jan, I have posted your modified code version and referenced you.
http://www.excelforum.com/tips-and-tutorials/1089404-posting-an-html-table-in-a-thread-2.html
…..and
Comment by: Alan Elston (23-6-2015 12:58:11) deeplink to this comment
…..and here
http://www.excelforum.com/the-water-cooler/1068075-just-testing-img-cannot-do-it-in-test-forum-as-img-is-off-there-no-reply-needed-2.html#post4110298
Hope that is OK.
Alan.
Comment by: Alan Elston (23-6-2015 17:06:35) deeplink to this comment
Hi
. Very sorry to mess up your site with my many replies here.
. Hope you catch them all and can edit them before you include them in your site:
- I was keen to acknowledge your help..
. The administrator has deleted the Posts where I acknowledged your help
. Hopefully they are still acknowledged here
http://www.excelforum.com/tips-and-tutorials/1089404-posting-an-html-table-in-a-forum-thread.html
Comment by: Alan Elston (23-6-2015 17:07:11) deeplink to this comment
......and here:
http://www.excelforum.com/the-water-cooler/1068075-just-testing-img-cannot-do-it-in-test-forum-as-img-is-off-there-no-reply-needed-2.html#post4110298
. Sorry for any confusion. Thanks again for the help
. Alan
Comment by: Tawn (6-8-2015 20:10:14) deeplink to this comment
I've downloaded the Microsoft Office Code Compatibility inspector add-in. I have MC Office Pro Plus 2010. The directions for use say to add the Developer Tab. I don't see the tab at File>Options>Customize Ribbon>Popular Commands>Main Tab.
I found one MVP that states there is not a Developer Tab for Access 2010.
Does the program need to be installed differently?
I have to get IT out to change installation and it would be helpful to know what I need to be able to see the Developer tab.
Thank you,
Tawn
Comment by: Jan Karel Pieterse (7-8-2015 14:20:54) deeplink to this comment
Hi Tawn,
I have googled for a bit, but probably found the same information you already found.
AFAIK, all you need to do to check for 64 bit compliance is:
1. search your code for windows API declarations (those all start with the keyword "Declare", so they are easily found using a project-wide Find in the VBA editor)
2. Check whether or not you have used any external activeX controls in your project, because those may be unavailable in 64 bit (e.g. from the Common Controls library comclt.ocx). Mostly these should be shown in your VBA projects list of references.
Comment by: les (17-12-2015 19:19:49) deeplink to this comment
I have a 32 bit version of Excel but have a client that has a 64 bit version. Where do I put the code to use the 64 bit if needed
Comment by: Jan Karel Pieterse (18-12-2015 11:52:08) deeplink to this comment
Hi Les,
Does your code have any API calls (Declare statements)?
If not, chances are quite good that your file works on 64 bit without any changes.
Comment by: Vladimir Nazarov (9-2-2016 21:24:48) deeplink to this comment
Dear Mr. Jan Karel Pieterse,
Could you please help me which Long becomes PtrLong in the following Type code in VBA7? Many thanks in advance.
Private Type KBDLLHOOKSTRUCT
vkCode As Long
scanCode As Long
Flags As Long
time As Long
dwExtraInfo As Long
End Type
Comment by: Jan Karel Pieterse (10-2-2016 07:27:11) deeplink to this comment
Hi Vladimir,
Which API funtion are you using?
Comment by: Jan Karel Pieterse (10-2-2016 07:28:54) deeplink to this comment
Hi Vladimir,
I expect:
vkCode As Long
scanCode As Long
Flags As Long
time As Long
dwExtraInfo As LongPtr
End Type
Comment by: Vladimir Nazarov (10-2-2016 14:07:28) deeplink to this comment
Dear Mr. Jan Karl Pieterse,
I have just found a very interesting text document containing a lot of API declarations in VBA7 format however without an older version of VBA (295 pages of A4 format):
http://www.cadsharp.com/docs/Win32API_PtrSafe.txt
' ----------------------------------------------------------
'
'Win32API_PtrSafe.TXT -- Declare statements for
'Visual Basic for Applications and Microsoft Office 2010
Comment by: Jan Karel Pieterse (10-2-2016 14:28:49) deeplink to this comment
Hi Vladimir,
Isn't that the same file as the one I refer to near the top of this article? (links section)
Comment by: Vladimir Nazarov (10-2-2016 15:38:20) deeplink to this comment
Dear Mr. Jan Karel Pieterse,
Could you recommend any websites / links to convert old Excel VBA code into the code for VBA7 for the following API declarations and corresponding Type statements?
Can MAPI32.DLL used for 64-bit Excel or another DLL should be used instead and which one and how to change the code?
============================================================
Declare Function MAPILogon Lib "MAPI32.DLL" (ByVal UIParam&, ByVal User$, ByVal Password$, ByVal flags&, ByVal Reserved&, Session&) As Long
Declare Function MAPILogoff Lib "MAPI32.DLL" (ByVal Session&, ByVal UIParam&, ByVal flags&, ByVal Reserved&) As Long
Declare Function RAWMAPISendMail Lib "MAPI32.DLL" Alias "MAPISendMail" (ByVal Session&, ByVal UIParam&, Message As RAWMAPIMessage, ByVal flags&, ByVal Reserved&) As Long
Comment by: Jan Karel Pieterse (10-2-2016 16:02:28) deeplink to this comment
Hi Vladimir,
It seems my page is one of the few that actually addresses this I'm afraid. And if a function is not in the winAPI.txt, then all you can do is try to find its definition on MSDN and then figure out what are the longptr's
Comment by: Elek (16-2-2016 16:30:21) deeplink to this comment
Hi,
do you have a "64bit translation" for following APIs?
Public Function SetFileDateTime(FileName As String, _
FileDateTime As Double, WhichDateToChange As FileDateToProcess, _
Optional NoGMTConvert As Boolean = False) As Boolean
Thanks,
Elek
Comment by: Jan Karel Pieterse (16-2-2016 17:44:24) deeplink to this comment
Hi elek,
I don't think that function is an API function so no 64 bit translation is needed. API functions are always declared using the "Declare" keyword. Any functuion NOT using that keyword is NOT a Windows API function and does not need 64 bit changes.
Comment by: Vladimir Nazarov (25-2-2016 16:52:28) deeplink to this comment
Dear Mr. Jan K. Pieterse,
How to make the following function LowLevelKeyboardProc to work both in VBA6 and in VBA7?
In VBA6 it looks like follows and it works OK for Excel VBA6:
Public Function LowLevelKeyboardProc(ByVal nCode As Long, _
ByVal wParam As Long, _
ByVal lParam As Long) As Long
In VBA7 it this function should looks like shown below:
Public Function LowLevelKeyboardProc(ByVal nCode As LongPtr, _
ByVal wParam As LongPtr, _
ByVal lParam As LongPtr) As LongPtr
If I remove both Long and LongPtr as shown below hoping to make it work with both Excel VBA6 and Excel VBA7, then Excel VBA6 crashes.
In VBA6 it looks like follows:
Public Function LowLevelKeyboardProc(ByVal nCode, _
ByVal wParam, _
ByVal lParam) As Long
Please note, that all API functions have been declared properly using #If VBA7 Then … #Else … #End If construction.
Thanks in advance for your help.
Vladimir Nazarov
Comment by: Jan Karel Pieterse (26-2-2016 14:26:37) deeplink to this comment
Hi Vladimir,
Can you please show the relevant declaration including the conditional compile stuff and the relevant pieces in your routine?
Comment by: Ramavatar (11-3-2016 07:47:17) deeplink to this comment
Thanks for information shared but i wanted to know which references lib to be selected in excel vba for handling windowsfind api. or similar kind.
Comment by: Jan Karel Pieterse (11-3-2016 10:26:35) deeplink to this comment
Hi Ramavatar,
Do you mean the FindWindow API? You can simply look it up in the table on this page. Windows API's do not need references, they only need the declare statement.
Comment by: Lorin Rowe (18-3-2016 21:30:22) deeplink to this comment
in 32 bit I called:
Set tdConnection = CreateObject("tdapiole80.tdconnection")
how should I reference "CreateObject" in a 64bit world? This is a 32 bit DLL.
Comment by: Jan Karel Pieterse (20-3-2016 19:03:52) deeplink to this comment
Hi Lorin,
I strongly suspect you simply cannot call a 32 bit dll form 64 bit VBA.
Comment by: Chris (20-3-2016 23:49:24) deeplink to this comment
Having some trouble with hooking a window proc in Access on 64-bit Office 2016, Windows 10. Still VBA 7, and I have 64-bit declarations, but every time I latch the window proc below, Access crashes after a few messages.
Thoughts?
#If VBA7 Then
Public Function sDragDropAttach(ByVal hwnd As LongPtr, _
ByVal Msg As Long, _
ByVal wParam As Long, _
ByVal lParam As Long) As LongPtr
#Else
Public Function sDragDropAttach(ByVal hwnd As Long, _
ByVal Msg As Long, _
ByVal wParam As Long, _
ByVal lParam As Long) As Long
#End If
Dim lngRet As Long, strTmp As String, intLen As Integer
Dim lngCount As Long, i As Long, strOut As String
Const cMAX_SIZE = 255
On Error Resume Next
If Msg = WM_DROPFILES Then
strTmp = String(255, vbNullChar)
lngCount = DragQueryFile(wParam, _
&HFFFFFFFF, _
StrPtr(strTmp), _
Len(strTmp))
For i = 0 To lngCount - 1
strTmp = String(cMAX_SIZE, vbNullChar)
intLen = DragQueryFile(wParam, _
i, _
StrPtr(strTmp), _
cMAX_SIZE)
strOut = strOut & left(StrConv(strTmp, vbUnicode), intLen) & "|"
Next i
Call DragFinish(wParam)
'I do my file processing here
Else
lngRet = CallWindowProc(ByVal lpPrevWndProc, _
ByVal hwnd, _
ByVal Msg, _
ByVal wParam, _
ByVal lParam)
End If
End Function
Comment by: Jan Karel Pieterse (21-3-2016 07:11:43) deeplink to this comment
Hi Chris,
I haven't used that function before, so I'm just making wild stabs here...
If you are creating hooks from API functions into your VBA code, are you properly releasing those hooks?
Are you sure there are no errors in your code, the on error resume next hides them.
Comment by: hilmi (2-6-2016 05:09:41) deeplink to this comment
i'm still not understand how to solve it. beginner user. should we copy all and save it in module?
Comment by: Jan Karel Pieterse (7-6-2016 13:43:34) deeplink to this comment
Hi Hilmi,
This is by far not something for a beginner user I'm afraid. I think it is best if you seek help from a more experienced VBA developer for this task.
Comment by: M Simms (23-6-2016 11:26:05) deeplink to this comment
This is from the Microsoft MSDN portal:
Existing 32-bit ActiveX controls, both third-party and Microsoft-supplied, are not compatible with the 64-bit version of Office 2010. For ActiveX controls and COM objects, there are three possible solutions:
If you have the source code, you can generate a 64-bit version yourself,
You can contact the vendor for an updated version,
You can search for an alternative solution.
Comment by: Jan Karel Pieterse (23-6-2016 12:27:30) deeplink to this comment
Hi M,
Yes correct. Another alternative is to build your own ActiveX-like control (if it is to be used on a userform) like this one:
www.jkp-ads.com/articles/treeview.asp
Comment by: M Simms (25-6-2016 15:14:15) deeplink to this comment
Can ALL "Long" references both parameters as well as return values be converted to "LongPtr"....or are there exceptions ?
This assumes Office 2011, 2013, and 2016.
Comment by: M Simms (25-6-2016 15:29:53) deeplink to this comment
Is the conversion below correct ?
I am worried about when to keep it Long vs. convert to LongPtr.
I take it that all handles and pointers should convert to LongPtr, correct ? Thanks for your help !!
--------------------------------------------------------
Private Declare Ptrsafe Function FtpCommand Lib "wininet.dll" Alias "FtpCommandA" _
(ByVal hConnect As LongPtr, _
ByVal fExpectResponse As Boolean, _
ByVal dwFlags As Long, _
ByVal lpszCommand As String, _
dwContext As LongPtr, phFtpCommand As LongPtr) As Boolean
' From the API documentation:
'BOOL FtpCommand(
' _In_ HINTERNET hConnect,
' _In_ BOOL fExpectResponse,
' _In_ DWORD dwFlags,
' _In_ LPCTSTR lpszCommand,
' _In_ DWORD_PTR dwContext,
' _Out_ HINTERNET *phFtpCommand
');
Comment by: Jan Karel Pieterse (27-6-2016 09:43:02) deeplink to this comment
Hi M Simmons,
No, you cannot simply replace all Longs woth LongPtrs, that will cause problems.
Have a look at the API viewer built by Dennis Walentin: http://www.rondebruin.nl/win/dennis/index.htm
Comment by: Jan Karel Pieterse (27-6-2016 09:45:33) deeplink to this comment
Hi M Simms,
The FtpCommand declaration looks correct to me. Best to test it thoroughly on both 32 and 64 bit Office systems.
Comment by: M Simms (28-6-2016 19:52:29) deeplink to this comment
Oh wow, that API Viewer looks terrific !!!
Does it also handle those "other" libraries like the FTP library ?
Comment by: gfmgin (22-8-2016 04:22:35) deeplink to this comment
I presume this would also helpful for me have less knowledge on MS Office compatibility in programming MS Access from 32bit to 64.
Thank U Very Much!
Comment by: Peter Straton (24-8-2016 00:13:38) deeplink to this comment
Shouldn't the last argument to the 64-bit declaration for GetWindow, wCmd, be a Long rather than a LongPtr since it is a numeric bit-code, consistent between both 32-bit and 64-bit Office, not a "pointer"?
Comment by: Jan Karel Pieterse (24-8-2016 09:18:14) deeplink to this comment
Hi Peter,
You're right and I've corrected the declaration. Thanks!
Comment by: Mick Webb (6-9-2016 18:16:37) deeplink to this comment
I assume that any wrappers where the result was Long but could now be either Long or LongPtr will have to have their return changed to Variant.
Comment by: Jan Karel Pieterse (7-9-2016 08:48:52) deeplink to this comment
Hi Mick,
I don't really know to be honest. Do you have any specific function in mind?
Comment by: Mick Webb (7-9-2016 13:18:22) deeplink to this comment
Hi,
For instance I have the FindWindow api in a wrapper to give me the handle of a MS UserForm.
Comment by: Jan Karel Pieterse (7-9-2016 16:47:46) deeplink to this comment
Hi Mick,
In such a case (e.g. when you're returning a value from a function), wrap the function header in a conditional compilation statement:
Function ReturnSomeValue() As LongPtr
#Else
Function ReturnSomeValue() As Long
#End If
'Code here which either assigns a Long or a LongPtr:
ReturnSomeValue = FindWindow(...)
End Function
Comment by: Mick Webb (9-9-2016 18:22:47) deeplink to this comment
Simples - thank you
Mick
Comment by: Marcos Ferreira (10-10-2016 19:20:49) deeplink to this comment
Esta línea de da error en vba power point 64 bits
Private Declare Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Cómo debería corregir para usar el comando sleep en Power Point?
Comment by: Jan Karel Pieterse (11-10-2016 07:05:54) deeplink to this comment
Buenos dias Marcos,
Yo no hablo Espanol, pero:
Comment by: atr (4-11-2016 08:10:18) deeplink to this comment
Hello.
I have a question.
Why do you use the "LongPtr" in "ByVal nBufferLength As longptr"?
In the "Long" is it bad?
Declare PtrSafe Function GetTempPath Lib "kernel32" _
Alias "GetTempPathA" (ByVal nBufferLength As longptr, _
ByVal lpbuffer As String) As Long
Comment by: Jan Karel Pieterse (14-11-2016 16:32:30) deeplink to this comment
Hi atr,
I must admit I don't know!
Comment by: Ramakrishnan S (29-11-2016 21:04:15) deeplink to this comment
I have a 64 bit machine & have my VBscript (.vbs) that is for mouse automation and mouse click events.
I am using sendkeys to get a specific cursor position, but unable to click at that location.
Let me know how to use the API calls on 64 bit?
Thanks
Ram.S
Comment by: Jan Karel Pieterse (30-11-2016 13:42:48) deeplink to this comment
Hi Ram,
You haven't mentioned which API functions you are using?
Comment by: Alan Elston (30-11-2016 16:09:05) deeplink to this comment
Hi Jan Karel Pieterse
Is it possible to get a URL Link to a particular Comment placed here?
I ask as I find it useful to referrence some of your answers here
Thanks
Alan
Comment by: Jan Karel Pieterse (30-11-2016 18:10:43) deeplink to this comment
Hi Alan,
That is actually a great idea! It will take me some time though, too much things going on right now.
Comment by: Alan Elston (30-11-2016 18:21:30) deeplink to this comment
Thanks ! :)
Alan
Comment by: Brett Bourg (30-11-2016 18:22:45) deeplink to this comment
Thank you for this page! It keeps helping me!
Comment by: Ram S (30-11-2016 19:54:00) deeplink to this comment
Hi Jan Karel,
Greetings!. Thanks for asking the question.
As of now i just have the Win32 APIs. Am researching to see if there are mouse APIs related to getCursorPos and setCursorPos and click at that position om Win 64.
Thanks
Ram.S
Comment by: Alan Elston (1-12-2016 15:08:21) deeplink to this comment
The new Comment direct URL links are working great, thanks again
Alan
Comment by: Jan Karel Pieterse (6-12-2016 10:09:12) deeplink to this comment
Hi Alan,
Please post your current declarations.
Comment by: loquat (29-12-2016 13:48:46) deeplink to this comment
how to use CallWindowProc in Win6 x64 office 32?
Comment by: Jan Karel Pieterse (30-12-2016 15:06:48) deeplink to this comment
Hi loquat,
I suggest you to do a google search for that API adding VBA to the search words.
Comment by: Richard Bates (1-3-2017 09:24:42) deeplink to this comment
Public Declare PtrSafe Function ReportEvent Lib "advapi32.dll" Alias "ReportEventA" (ByVal hEventLog As LongPtr, _
ByVal wType As Integer, ByVal wCategory As Integer, ByVal dwEventID As Long, ByVal lpUserSid As Any, _
ByVal wNumStrings As Integer, ByVal dwDataSize As Long, plpStrings As LongPtr, lpRawData As Any) As Long
'Public Declare PtrSafe Function ReportEvent Lib "advapi32.dll" Alias "ReportEventA" (ByVal hEventLog As LongPtr, _
ByVal wType As Long, ByVal wCategory As Long, ByVal dwEventID As Long, lpUserSid As Any, _
ByVal wNumStrings As Long, ByVal dwDataSize As Long, ByVal lpStrings As LongPtr, lpRawData As Any) As Long
'******************* N.B. This old code will work but it isn't PtrSafe **************************************
'Declare Function ReportEvent Lib "advapi32.dll" Alias "ReportEventA" (ByVal hEventLog As Long, _
ByVal wType As Integer, ByVal wCategory As Integer, ByVal dwEventID As Long, ByVal lpUserSid As Any, _
ByVal wNumStrings As Integer, ByVal dwDataSize As Long, plpStrings As Long, lpRawData As Any) As Boolean
'************************************************************************************************************
Comment by: Jan Karel Pieterse (1-3-2017 10:04:22) deeplink to this comment
Hi Richard,
Thanks for those!
Comment by: Bill (2-3-2017 20:15:29) deeplink to this comment
I'm having a problem with code to hide access behind forms. I'm in Access 2013 on Windows 7 64-bit. The code seems to work for others in different forums, but mine can't fine the function apiShowWindow even though I changed to ptrsafe. It compiles, so the syntax is right, but burps at execution. You clearly understand this stuff, but I'm out of my area of expertise - do I need to add some reference in the VBA window? Thanks in advance for any help/ideas you can share.
Option Compare Database
Option Explicit
Global Const SW_HIDE = 0
Global Const SW_SHOWNORMAL = 1
Global Const SW_SHOWMINIMIZED = 2
Global Const SW_SHOWMAXIMIZED = 3
Private Declare PtrSafe Function apiShowWindow Lib "user32" Alias "ShowWindow" (ByVal hwnd As Long, ByVal nCmdShow As Long) As Long
Function fSetAccessWindow(nCmdShow As Long)
Dim loX As Long
Dim loForm As Form
On Error Resume Next
Set loForm = Screen.ActiveForm
If Err <> 0 Then
loX = apiShowWindow(hWndAccessApp, nCmdShow)
Err.Clear
End If
If nCmdShow = SW_SHOWMINIMIZED And loForm.Modal = True Then
MsgBox "Cannot minimize Access with " _
& (loForm.Caption + " ") _
& "form on screen"
ElseIf nCmdShow = SW_HIDE And loForm.PopUp <> True Then
MsgBox "Cannot hide Access with " _
& (loForm.Caption + " ") _
& "form on screen"
Else
loX = apiShowWindow(hWndAccessApp, nCmdShow)
End If
fSetAccessWindow = (loX <> 0)
End Function
Comment by: Jan Karel Pieterse (3-3-2017 11:17:51) deeplink to this comment
Hi Bill,
Try whether this declaration works:
Comment by: jay (16-3-2017 23:09:11) deeplink to this comment
Just noticed, That in the List SendMessage wParam is LonpPtr.
Luckly I read "Which Longs should become LongPtr?"
and noticed this error.
Great site thanks and thanks to microsoft for all the new work.
Comment by: Charltsing (31-5-2017 17:57:47) deeplink to this comment
I developed a free tool for automatic conversion of windows api in vbe. (office,autocad,etc)
http://www.cnblogs.com/Charltsing/p/SmartIndenter64.html
this vbe addins support windows API convert (Declare <--> PtrSafe),the number of supported api functions is about 1550.
thank you for your test!
Charltsing@gmail.com
Comment by: Luis Cardenas (3-7-2017 02:51:30) deeplink to this comment
I'm having a problem with code to get a ciphered string from a C++ .dll I'm in Excel 2016 on Windows 10 64-bit. The code seems to work for strings no longer than 16 characters, because VB receives the result string as a LongPointer. Thanks in advance for any help/ideas you can share:
Option Explicit
Private Declare PtrSafe Function EncryptDecrypt Lib "F:\XLS\SegCrypt64.dll" (ByVal fEncrypt As Boolean, ByVal lpszInBuffer As String, ByRef lpszOutBuffer As LongPtr, ByRef dwOutBufferLen As Long) As Boolean
Function EncryptXLS(ByVal fEncrypt As Boolean, ByVal lpszInBuffer As String, ByRef lpszOutBuffer As LongPtr, ByRef dwOutBufferLen As Long) As Boolean
EncryptXLS = EncryptDecrypt(fEncrypt, lpszInBuffer, lpszOutBuffer, dwOutBufferLen)
End Function
Sub TestDll()
Dim Result As Variant ' Result
Dim StrIn As String 'String to Cipher
Dim strOut As LongPtr 'Ciphered String
Dim lOut As Long 'Lenght of StrCr
StrIn = "1234567890123456"
lOut = Len(StrIn)
strOut = StrPtr(String(lOut, " "))
Result = EncryptXLS(1, StrIn, strOut, lOut)
Result = Trim(CStr(strOut))
MsgBox (Hex(Result))
End Sub
Comment by: Jan Karel Pieterse (3-7-2017 10:32:30) deeplink to this comment
Hi Luis,
This is a problem related to how C++ handles strings and how VBA handles strings and how to ensure you get the information accross between them. I googled a bit and found this article on stacj exchange. Hope it helps:
https://stackoverflow.com/questions/39404028/passing-strings-from-vba-to-c-dll
Comment by: J. W. Raper (27-9-2017 23:40:05) deeplink to this comment
I am a novice trying to learn Excel VBA; you have personally helped me before and this web page continues that help. Above is the statement WIN64: True if your Office installation is 64 bit, false for 32 bit. I am confused because everywhere I look the compiler constant Win64 has little or nothing to do with the active office version, it simply indicates whether or not the office installation platform environment is 64-bit compatible. It would assist me if an Excel VBA professional would help me understand. Thank you in advance for your assistance.
Comment by: Jan Karel Pieterse (28-9-2017 07:36:02) deeplink to this comment
Hi JW,
I agree the WIN64 compile constant is confusing, it should have read OFF64 if you ask me. This little routine returns TRUE *only* if your Office innstallation is 64 bit:
#If Win64 Then
AmI64 = True
#End If
End Function
Comment by: Chris B (19-10-2017 17:20:54) deeplink to this comment
Thanks for the excellent documentation.
In your example for GetOpenFileName you have
#If VBA7 Then
...
Public Type OPENFILENAME
...
lCustData As Long
But the Microsoft documentation in file "Win32API_PtrSafe.TXT" has
Type OPENFILENAME
lCustData As LongPtr
Comment by: Jan Karel Pieterse (19-10-2017 17:44:09) deeplink to this comment
Hi Chris,
Thanks!
Well spotted and I have corrected the error now.
Comment by: Martin L (2-11-2017 06:18:28) deeplink to this comment
Working and tested function that could be added:
#If VBA7 Then
Public Declare PtrSafe Function DrawMenuBar Lib "user32" (ByVal hWnd As LongPtr) As Long
#Else
Public Declare Function DrawMenuBar Lib "user32" (ByVal hWnd As Long) As Long
#End If
Comment by: Jan Karel Pieterse (2-11-2017 10:33:06) deeplink to this comment
Thank you Martin, I have added it to the list.
Comment by: Manuel (19-12-2017 12:29:05) deeplink to this comment
sConnect = "ODBC;UID=ADM124;PWD=ADM124;DATABASE="
If Base_Abierta = False Then
Set GdbCurrentDB = OpenDatabase("adm124", False, False, sConnect)
Error: Run-time error '429'
ActiveX component can't créate object.
How to solve this problem or error?
atte.
Manuel Araya Peña
Comment by: Jan Karel Pieterse (19-12-2017 13:01:25) deeplink to this comment
Hi Araya,
In what application are you programming? MSAccess?
Comment by: Manuel (19-12-2017 13:04:39) deeplink to this comment
No, with ODBD connection to Oracle.
atte.
Map
Comment by: Jan Karel Pieterse (19-12-2017 14:06:23) deeplink to this comment
Hi Manuel,
I suggest you ask your question at www.eileenslounge.com
Comment by: Roy (26-12-2017 08:39:58) deeplink to this comment
Hi, i cant seem to fix my Occupancy calculator. it works fine on office 2010, but its not working on office 2013. i already declared it safe but cant make it work. Appreciate your help.
below is the VBA code under module 1. let me know if you need the actual file that im having trouble with.
------------------------------------
Option Explicit
#If Win64 Then
Private Declare PtrSafe Function Erlcf Lib "erl32.dll" Alias "_Erlcf@16" _
(ByRef hours As Double, ByRef hold As Double, ByRef fract As Double, slsec As Double) As Double
#Else
Private Declare Function Erlcf Lib "erl32.dll" Alias "_Erlcf@16" _
(ByRef hours As Double, ByRef hold As Double, ByRef fract As Double, slsec As Double) As Double
#End If
Comment by: Jan Karel Pieterse (27-12-2017 11:27:42) deeplink to this comment
Hi Roy,
Are you using 64 bit Office?
Is this dll a third-party dll by any chance? I cannot find any reference to it when I Google for it. If so, get hold of its maker, ask if there is a 64 bit version.
Comment by: Roy (27-12-2017 16:10:18) deeplink to this comment
Hi there!
Thanks for your reply.
Yes it is 64bit Ms Excel. And yes, its a third party Dll file.
Comment by: Jan Karel Pieterse (28-12-2017 12:21:16) deeplink to this comment
Hi Roy,
In that case you will need to find a 64 bit version of that dll. 32 bit dll's cannot be accessed by a 64 bit Office installation.
ALternatively, uninstall 64 bit Office and install the 32 bit version (the dafault).
Comment by: Roy (28-12-2017 14:16:33) deeplink to this comment
Thanks for your response. I appreciate it.
Comment by: Andrija Vrcan (8-1-2018 20:50:28) deeplink to this comment
Which way i can open folder using Shell API in 64 bit win,similar to:
Thank you in advance.
Comment by: Jan Karel Pieterse (9-1-2018 08:09:15) deeplink to this comment
Hi Andrija,
No need for a Windows API, there is a built-in method for it:
Application.FileDialog:
https://msdn.microsoft.com/en-us/vba/excel-vba/articles/application-filedialog-property-excel
Comment by: Andrija Vrcan (9-1-2018 10:04:31) deeplink to this comment
Thank you Jan, but I don't need 'select' folder to be display, but only display specified folder. Cheers
Comment by: Jan Karel Pieterse (9-1-2018 10:53:17) deeplink to this comment
Hi Andrija,
Apologies, I misunderstood!
What is wrong with the Shell method you already showed?
Comment by: Andrija Vrcan (9-1-2018 11:05:53) deeplink to this comment
works only in 32 bit win.
Comment by: Jan Karel Pieterse (9-1-2018 12:50:47) deeplink to this comment
Hi Andrijja,
The code works just fine on my 64 bit Excel 2010.
Comment by: Andrija Vrcan (10-1-2018 09:38:39) deeplink to this comment
Thank you Jan! The code did not work for my mistake (I accidentally commented part of the previous code).
Comment by: Chuck Minarik (19-1-2018 13:20:33) deeplink to this comment
Thanks! This is just what I needed after converting from Office 32 to Office 64.
Comment by: Martin L (7-2-2018 23:05:38) deeplink to this comment
I've seen the function SetWindowPos a lot in some forum posts that don't always use the appropriate declaration method,so I thought it would be good to add it here. I've made the adjustment myself and and tested the version below on my computer.
#If VBA7 Then
Private Declare PtrSafe Function SetWindowPos Lib "user32" ( _
ByVal hwnd As LongPtr, ByVal hWndInsertAfter As LongPtr, _
ByVal X As Long, ByVal Y As Long, ByVal cx As Long, _
ByVal cy As Long, ByVal wFlags As Long) As Long
#Else
Private Declare Function SetWindowPos Lib "user32" ( _
ByVal hwnd As Long, ByVal hWndInsertAfter As Long, _
ByVal X As Long, ByVal Y As Long, ByVal cx As Long, _
ByVal cy As Long, ByVal wFlags As Long) As Long
#End If
Comment by: Jan Karel Pieterse (8-2-2018 06:49:41) deeplink to this comment
Hi Martin,
Thanks, I've added it to the list!
Comment by: Lorne (3-3-2018 14:14:55) deeplink to this comment
Thanks for an valuable reference resource.
Why is the SendMessage function referred to by the alias of SEndMessageA? Also, the same example declaration at he foot of this page declares the wParam as Long instead of LongPtr. The Declaration returns LongPtr but the MS Win32API_PtrSafe.Txt states it is a Long. The same text file states that the GetClassName declaration for nMaxCount should be a Long, Not LongPtr.
Comment by: Jan Karel Pieterse (5-3-2018 10:24:24) deeplink to this comment
Hi Lorne,
Good catch, I have updated the page to match the declaration in the txt file.
Comment by: Martin L (22-3-2018 06:08:48) deeplink to this comment
I'm afraid that Lorne's comment was not completely right regarding SendMessage. I double checked in Win32API_PtrSafe.Txt and the return value for SendMessage actually requires LongPtr.
However, the comment was right regarding wParam which means that the following sentence: "The arguments wMsg and wParam are used to pass data, so they can be Long in both 32-bit and 64-bit."
should exclude the mention of wParam.
Comment by: Jan Karel Pieterse (22-3-2018 08:12:55) deeplink to this comment
Hi Martin,
I have changed the declaration, thanks!
Comment by: Vishal kirve (9-4-2018 13:08:09) deeplink to this comment
I want click on open button of notification bar of ie11 using vba code
Comment by: Jan Karel Pieterse (9-4-2018 16:36:39) deeplink to this comment
Hi Vishal,
If you know the URL of the download, perhaps you can use the URLDownloadTofile function?
Comment by: Arjun (1-6-2018 20:33:40) deeplink to this comment
Hi All,
i am missing something here
can anyone please edit the below 32 bit code for 64 bit excel
Private Declare PtrSafe Sub mouse_event Lib "user32" (ByVal dwFlags As LongPtr, _
ByVal dx As Long, _
ByVal dy As Long, _
ByVal cButtons As Long, _
ByVal dwExtraInfo As Long)
Private Const MOUSEEVENTF_MOVE = &H1 ' mouse move
Private Const MOUSEEVENTF_LEFTDOWN = &H2 ' left button down
Private Const MOUSEEVENTF_LEFTUP = &H4 ' left button up
Private Const MOUSEEVENTF_RIGHTDOWN = &H8 ' right button down
Private Const MOUSEEVENTF_RIGHTUP = &H10 ' right button up
Private Const MOUSEEVENTF_MIDDLEDOWN = &H20 ' middle button down
Private Const MOUSEEVENTF_MIDDLEUP = &H40 ' middle button up
Private Const MOUSEEVENTF_WHEEL = &H800 ' wheel button rolled
Private Const MOUSEEVENTF_ABSOLUTE = &H8000 ' absolute move
Private Type POINTAPI
X As Long
Y As Long
End Type
Sub Click()
Dim pt As POINTAPI
Dim X As Long
Dim Y As Long
'(0,0) = top left
'(65535,65535) = bottom right
X = 29000 / 2
Y = 29000 / 2
LeftClick X, Y
End Sub
Sub LeftClick(X As Long, Y As Long)
'Move mouse
mouse_event MOUSEEVENTF_ABSOLUTE + MOUSEEVENTF_MOVE, X, Y, 0, 0
'Press left click
mouse_event MOUSEEVENTF_ABSOLUTE + MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
'Release left click
mouse_event MOUSEEVENTF_ABSOLUTE + MOUSEEVENTF_LEFTUP, 0, 0, 0, 0
End Sub
Comment by: Jan Karel Pieterse (8-6-2018 19:40:30) deeplink to this comment
Hi Arjun,
I have added it to the list.
Comment by: Jan Karel Pieterse (3-7-2018 14:34:40) deeplink to this comment
Hi Giancarlo,
Thanks. But the txt file which I refer to at the start of this article says:
Declare PtrSafe Function GetWindowLong Lib "user32" Alias "GetWindowLongA" (ByVal hwnd As LongPtr, ByVal nIndex As Long) As Long
Comment by: Jamie (22-8-2018 16:12:40) deeplink to this comment
This is a great page! The declaration for GetWindowLong has two errors as it's shown as GetWindowLongPtr in two places. Here are a few more:
Public Declare PtrSafe Function UnhookWindowsHookEx Lib "user32" (ByVal hHook As LongPtr) As Long
Public Declare PtrSafe Function SetWindowsHookEx Lib "user32" Alias "SetWindowsHookExA" (ByVal idHook As Long, ByVal lpFn As LongPtr, ByVal hmod As LongPtr, ByVal dwThreadId As Long) As LongPtr
Public Declare PtrSafe Function CallNextHookEx Lib "user32" (ByVal hHook As LongPtr, ByVal nCode As Long, ByVal wParam As LongPtr, lParam As Any) As LongPtr
Public Declare PtrSafe Function GetModuleHandle Lib "kernel32" Alias "GetModuleHandleA" (ByVal lpModuleName As String) As LongPtr
Public Declare PtrSafe Function GetCurrentThreadId Lib "kernel32" () As Long
Private Declare Function SendDlgItemMessage Lib "user32" Alias "SendDlgItemMessageA" (ByVal hDlg As LongPtr, ByVal nIDDlgItem As Long, ByVal wMsg As Long, ByVal wParam As LongPtr, ByVal lParam As LongPtr) As LongPtr
Comment by: Jan Karel Pieterse (23-8-2018 14:50:29) deeplink to this comment
Hi Jamie,
Thanks. What you posted, do you think those are the correct declarations?
Comment by: 123hemanth.singh@gmail.com (14-9-2018 22:56:34) deeplink to this comment
Public Declare PtrSafe Function Sub Sleep Lib "Kernel32" (ByVal dwMilliseconds As LongPtr)
Not able to run this module.
Comment by: Jan Karel Pieterse (17-9-2018 10:40:21) deeplink to this comment
Hi Hemanth (?),
When do you get this error?
Comment by: Tony Matyas (23-10-2018 13:25:19) deeplink to this comment
Just a hint to avoid possible confusion after corrected code line: In the section "Which Longs should become LongPtr?" (after the declaration list block)
you corrected the SendMessageA argument's wParam type to LongPtr due to comments, but left the 3rd line in the following paragraph unchanged:
"The arguments wMsg and wParam (!) are used to pass data, so they can be Long in both (!) 32-bit and 64-bit."
Comment by: Jan Karel Pieterse (23-10-2018 17:18:30) deeplink to this comment
Hi Tony,
Corrected, thanks!
Comment by: Tony Matyas (23-10-2018 19:42:50) deeplink to this comment
ad) 1. GetWindowLong: Suppose the last #Else statement (AFTER WIN64/Win32 differentiation), i.e. referring to >VBA6< should be
2. Similarly the last SetWindowLong declaration referring to elder Office Versions:
Comment by: Jan Karel Pieterse (24-10-2018 09:42:21) deeplink to this comment
Hi Tony,
Corrected, thanks!
Comment by: Joe (29-10-2018 07:13:00) deeplink to this comment
Thanks to the information on this page, I've had some success in getting a personal application working in a mix of 32/64 worlds. The Windows API calls are all working...
I'm still stuck though: I have a third party 32bit DLL which just contain math and file functions.
On my Win7(x64) with Excel 2010 (32bit) it all works as it should. On a Win10(x64) with Excel 2016 (64bit) even using Public Declare PtrSafe... I get cannot find entry point, or sometimes a DLL not found error!
#If VBA7 Then
Private Declare PtrSafe Function LearningRate Lib "crunch.dll" (ByVal nIndex As Long, ByVal nWeight As Long, ByVal nFocus as Long) As Double
#Else
Private Declare Function LearningRate Lib "crunch.dll" (ByVal nIndex As Long, ByVal nWeight As Long, ByVal nFocus as Long) As Double
#End If
I have copies of the .dll file in both C:\Windows\System32 and C:\Windows\SysWOW64 although I'm not sure if the file needs to be in both locations? (My understanding is that System32 is (confusingly) for the 64bit DLLs and SysWOW64 is where the 32bit DLLs are kept?)
Can I call the functions from a "simple" 32bit DLL from 64bit VBA?
Any help would really be appreciated.
Joe.
Comment by: Jan Karel Pieterse (29-10-2018 11:10:27) deeplink to this comment
Hi Joe,
In theory you cannot call 32 bit dll's from 64 bit VBA. But I seem to recall there is a convoluted win API work-around for it. Unfortunately I was not able to locate the demo file I thought I had on my system somewhere :-(
Comment by: A K (9-11-2018 03:17:12) deeplink to this comment
Thank you for the great compilation. However, please note that most of the 64 bit declarations in the table are incorrect. Most of the declarations only need PtrSafe added to them. Only when actual pointers are being passed as an argument, they will need to be converted from Long to LongPtr or other pointer type. Otherwise, ByRef Long types (eventhough they are defined as pointers in the function documentation) still need to be declared as Long, not LongPtr. Also, Long returned values remain Long, and should not be converted to LongPtr. If you have access to 64 bit Office, please test these declarations and update accordingly, since apparently they have not been tested. I have tested the above information in this comment and they are correct.
Comment by: Jan Karel Pieterse (9-11-2018 11:30:00) deeplink to this comment
Hi A K,
Most of these have been tested. Many of them come directly from the mentioned documentation by Microsoft.
I'm afraid I must disagree with your comment that most of them are wrong, I think they are correct. If any of these actually fail on 64 bit Office I'd like to know.
Comment by: Daniel (29-4-2019 20:11:00) deeplink to this comment
Your link to 'Visual Basic Win32 API Declarations' no longer works
Comment by: Jan Karel Pieterse (30-4-2019 09:08:00) deeplink to this comment
Hi Daniel,
Thanks for letting me know, I have replaced the link to point to an alternative source.
Comment by: Ryan (6-6-2019 19:37:00) deeplink to this comment
Hello,
I have a situation where users have a 64 bit machine, and they have office 365 64 bit installed for excel.
The following code is what i changed to based on the great walk through above...my question to you is, do you see any issues with the code not working on a 64 x 64 computer?
'Declare a function to get the data within the INI file
Public Declare PtrSafe Function GetPrivateProfileString Lib "kernel32" Alias "GetPrivateProfileStringA" (ByVal lpApplicationName As String, ByVal lpKeyName As String, ByVal lpDefault As String, ByVal lpReturnedString As String, ByVal nSize As LongPtr, ByVal lpFileName As String) As LongPtr
#Else
'Declare a function to get the data within the INI file
Public Declare Function GetPrivateProfileString Lib "kernel32" Alias "GetPrivateProfileStringA" (ByVal lpApplicationName As String, ByVal lpKeyName As String, ByVal lpDefault As String, ByVal lpReturnedString As String, ByVal nSize As Long, ByVal lpFileName As String) As Long
#End If
Comment by: Jan Karel Pieterse (7-6-2019 10:27:00) deeplink to this comment
Hi Ryan,
Accoring to the Win32API_PtrSafe.TXT file this is the correct declaration for 64 bit (the part in the True part of your #If statement):
Comment by: Marek (11-6-2019 13:59:00) deeplink to this comment
Hello,
I have also used the following code:
#If VBA7 Then
'Declare a function to get the data within the INI file
Public Declare PtrSafe Function GetPrivateProfileString Lib "kernel32" Alias "GetPrivateProfileStringA" (ByVal lpApplicationName As String, ByVal lpKeyName As String, ByVal lpDefault As String, ByVal lpReturnedString As String, ByVal nSize As LongPtr, ByVal lpFileName As String) As LongPtr
#Else
'Declare a function to get the data within the INI file
Public Declare Function GetPrivateProfileString Lib "kernel32" Alias "GetPrivateProfileStringA" (ByVal lpApplicationName As String, ByVal lpKeyName As String, ByVal lpDefault As String, ByVal lpReturnedString As String, ByVal nSize As Long, ByVal lpFileName As String) As Long
#End If
However it still shows an error in the Else clause (highlights in red and won't work). Any idea why?
thanks!
Comment by: Jan Karel Pieterse (11-6-2019 15:27:00) deeplink to this comment
Hi Marek,
You can simply ignore the fact that part of the declaration becomes red, as long as t is the part that is irrelevant to the bit-ness of your Excel. So if the Else part is red, but you are using 64 bit Excel, there is no problem with the Else part of the API declaration as it is ignored by the compiler.
Comment by: Jayan (19-6-2019 08:05:00) deeplink to this comment
Hello,
I'm trying to make a moving bubble chart.
I have a 64 bit machine with office 365 64 bit installed and using VBA7.1version.
However, when I run the macro no movements happening to the bubble. Would you help me to get the correct code.
----------
Public Declare PtrSafe Sub Sleep Lib "Kernel32" (ByVal dwMilliseconds As LongPtr)
Declare PtrSafe Function GetActiveWindow Lib "user64" () As Long
Sub Button1_Click()
Dim i As Integer
For i = 0 To 10:
Range("C1").Value = i
Application.Calculate
Sleep (1200)
Next
End Sub
Comment by: Jan Karel Pieterse (19-6-2019 09:29:00) deeplink to this comment
Hi Jayan,
Try adding DoEvents immediately after the Sleep statement.
Comment by: William Brennan (24-7-2019 13:46:00) deeplink to this comment
I cannot use ShellExecute in my MS Access code. I've tried everything suggested. After one of our recent Windows 10 Updates, ShellExecute doesn't work.
Any suggestions?
Thank you.
Comment by: Jan Karel Pieterse (24-7-2019 13:51:00) deeplink to this comment
Hi William,
Can you please share some sample code which should normally work stand-alone (doesn't require any other code or data)?
Comment by: Taka Yoshida (6-8-2019 18:08:00) deeplink to this comment
Do you have 64bit version of the below?
Public Declare Function ShowWindow Lib "USER32" (ByVal hWnd As Long, ByVal nCmdSHow As Long) As Long
Thank you.
Comment by: Jan Karel Pieterse (7-8-2019 09:56:00) deeplink to this comment
Hi Taka,
As shown in the document I link to above (http://www.microsoft.com/en-us/download/confirmation.aspx?id=9970):
Comment by: Andy Barfknecht (19-8-2019 22:03:00) deeplink to this comment
I do not understand the example URLDownloadToFile. Why are not pCaller and lpfnCB required to be type LongPtr for 64bit? They are pointers to locations in memory, are they not?
Comment by: Jan Karel Pieterse (27-8-2019 10:45:00) deeplink to this comment
Hi Andy,
Fair question. Reading the docs does make me think both should be LongPtr as they are pointers. However, I'v ebeen using this version of them without any complaints for over 5 years now :-)
Comment by: Mhlanga (3-9-2019 06:38:00) deeplink to this comment
Can you please help me with an API that removes the Title bar of the userform (i.e. the portion where we display the userform caption together with the close button).
The API should work on both 64 and 32 bit.
I have seen some posting similar APIs which are hard to decode. Most of them don't work. I'm using excel 365 and would like to remove the title bar without distorting the shape of the userform. This is very important.
By shape I mean special effect. My userform has a special effect flat. I would like to retain the smooth flat userform after applying the API, not raised or bump special effect. What I have seen is that most of the API out there result in a doomed or raised userform and that affects the outlook of my userform.
Can you help with this?
Comment by: Jan Karel Pieterse (3-9-2019 10:27:00) deeplink to this comment
Hi Mhlanga,
I'd like to refer you to this utility for which I've tried to mimick precisely that behaviour: https://jkp-ads.com/download.asp#Compare2Tables
Does that come close: Note that I haven't got round to making it 64 bit compatible yet.
Comment by: Eduardo Becerra (13-10-2019 05:27:00) deeplink to this comment
I have an old dll named MexCurves.dll
I made a Macro and is not running in new WIndows. Tried to fix it using "PtrSafe"
Declare PtrSafe Sub setTIIECurve Lib "C:\MexCurves12.dll" _
(ByVal priceDateSerial As Long, ByVal overnightRate As Double, ByVal spotTIIE As Double, ratesArray())
When running it from Excel I get error:
"C:\MexCurves12.dll" not found. Can you help please. Thanks Eduardo
Comment by: Jan Karel Pieterse (14-10-2019 10:03:00) deeplink to this comment
Hi Eduardo,
I'm afraid that will not make a difference. Your dll is clearly a 32 bit dll so it will only work on 32 MS Office. I suspect you installed 64 bit Office instead. To use your dll you must uninstall you 64 bit Office and install the 32 bit version.
Comment by: Frederick (16-10-2019 15:53:00) deeplink to this comment
Do you have 64bit version of the below?
Private Declare Sub Sleep Lib "kernel32" Alias "sleep" (ByVal dwMilliseconds As Long)
Declare Function CloseHandle Lib "kernel32" Alias "Closehandle" (ByVal hObject As Long) As Long
Comment by: Jan Karel Pieterse (16-10-2019 18:20:00) deeplink to this comment
Sure:
Declare PtrSafe Function CloseHandle Lib "kernel32" Alias "CloseHandle" (ByVal hObject As LongPtr) As Long
Comment by: Romain (20-10-2019 01:31:00) deeplink to this comment
Hi,
Would you happen to have the 64bit version for the below?
Declare PtrSafe Function ChooseColorA Lib "Comdlg32" (lpChooseColor As udtCColor) As Long
Declare PtrSafe Function FindWindowA Lib "User32" _
(ByVal lpClassName As Any, ByVal lpWindowName As String) As Long
Comment by: Jan Karel Pieterse (21-10-2019 17:00:00) deeplink to this comment
Hi Romain,
Accoring to the link provided at the top it is:
lStructSize As Long
hwndOwner As LongPtr
hInstance As LongPtr
rgbResult As Long
lpCustColors As LongPtr
flags As Long
lCustData As LongPtr
lpfnHook As LongPtr
lpTemplateName As String
End Type
Declare PtrSafe Function ChooseColor Lib "comdlg32.dll" Alias "ChooseColorA" (pChoosecolor As CHOOSECOLOR) As Long
Comment by: Dutch (25-2-2020 09:45:00) deeplink to this comment
I believe this could be the full list, including proper type declarations:
https://www.cadsharp.com/docs/Win32API_PtrSafe.txt
Comment by: Jan Karel Pieterse (25-2-2020 10:02:00) deeplink to this comment
Hi Dutch,
Thanks, that is exactly the same file as the one I point to at the top of the article, linked under "Office 2010 Help Files: Win32API_PtrSafe with 64-bit Support" with this link:
http://www.microsoft.com/en-us/download/confirmation.aspx?id=9970
Comment by: Mario Bérubé (25-3-2020 00:53:00) deeplink to this comment
In the table «Declarations by API function» above, I think the right column title should be:
Declarations (64 bit followed by 32 bit)
Comment by: Jan Karel Pieterse (25-3-2020 09:50:00) deeplink to this comment
Hi Mario,
I agree the table caption is confusing as I have not been consistent in the order of the declarations. I have updated the column title accordingly :-)
Comment by: Antonio Elinon (31-5-2020 15:48:00) deeplink to this comment
For VBA7 & not Win64, can you confirm "GetWindowLongA() as LongPtr" above.
Other websites say it is only Long-
https://answers.microsoft.com/en-us/msoffice/forum/all/office-64-bit-code-compatibility/f6ae23f2-2610-4887-a7f4-21b52c1c1323
Comment by: Jan Karel Pieterse (2-6-2020 11:29:00) deeplink to this comment
Hi Antonio,
I have several add-ins all using the declaration shown here, without issues.
Comment by: Martin L (9-7-2020 19:29:00) deeplink to this comment
Regarding Antonio's comment, it doesn't matter if LongPtr or Long is used because LongPtr will always compile to Long when Win64 is "False" (since we would be under a 32-bit environment in that case).
Comment by: Jim Meehan (28-7-2020 18:20:00) deeplink to this comment
In GlobalAlloc, you've got the wflags as longptr. Wouldn't these just be small indicators (Long)?
Comment by: Jan Karel Pieterse (28-7-2020 18:58:00) deeplink to this comment
Hi Jim,
Having read the documentation once more I expect you are correct.
Comment by: yongZhen Li (22-8-2020 05:42:00) deeplink to this comment
Declaring API functions in 64 bit Office:
GetWindow
GetWindowText
GetForegroundWindow
FindWindowLike
Comment by: Robert Marshall (22-8-2020 19:38:00) deeplink to this comment
Is the code for GetWindowLong correct? The second declaration seems to be incorrect and should be identical to the third declaration.
Comment by: Jan Karel Pieterse (24-8-2020 10:26:00) deeplink to this comment
Hi Robert,
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) deeplink to this comment
Hi yongZhen,
Did you mean to ask a question about those declarations?
Comment by: Jim Meehan (30-11-2020 23:05:00) deeplink to this comment
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) deeplink to this comment
Hi Jim,
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) deeplink to this comment
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) deeplink to this comment
Hi Andy,
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) deeplink to this comment
Hello Jan Karel Pieterse
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) deeplink to this comment
Hi Andrii,
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
Comment by: Luis Moraga (6-7-2021 18:20:00) deeplink to this comment
Very useful page! Solved my problem.
(I just spotted a typo in your "I give permission to process ...." line: says "accoring" where it should say "according").
Comment by: Jan Karel Pieterse (7-7-2021 10:58:00) deeplink to this comment
Hi Luis,
Well spotted, thank you!!!
Comment by: Dan Wallace (21-7-2021 17:20:00) deeplink to this comment
Hello
Thank you for this wonderful resource. I return to it regularly.
I wanted to ask if you knew of the 64bit declarations for the bcrypt and GDIPlus libraries. I am self-studying my way through VBA, and am currently working with alot of VB6 materials (which do not have to convert declarations into 64bit). As regards BCrypt, I am trying to convert the following into VBA: https://www.vbforums.com/showthread.php?862103-VB6-Simple-AES-256-bit-password-protected-encryption
And for GDIPlus, I have some declarations but cannot seem to find all of them.
Any guidance would be greatly appreciated.
Comment by: Jan Karel Pieterse (21-7-2021 17:44:00) deeplink to this comment
Hi Dan,
I'm afraid I don't have any of those!
What I have done for the ones that are not in the txt download that is mentioned near the top of this page, is simply google for the function's name and the string docs.microsoft.com.
That usually gets you a link to the documentation of the function which states the argument types. Here's an example:
https://docs.microsoft.com/en-us/windows/win32/api/bcrypt/nf-bcrypt-bcryptopenalgorithmprovider
You can see from the docs that the first three arguments are pointers, so those need to be LongPtr. The last one is a flag, which can be represented by a simple Long. Finally, that function returns a status code so I'm relatively confident that a Long return type suffices.
So that is the (hard) way to translate these from 32 to 64 bit I'm afraid.
Would be nice if you can share the ones you have tried and tested so far, so I might include them here.
Comment by: Dan (24-7-2021 23:54:00) deeplink to this comment
Hello
Thank you kindly for taking the time to respond to my query. I suspected that this might be the case. Thank you also for your guidance on how to go about converting the declarations. I will, of course, share with you / post on Github any declarations I manage to convert.
I am, separately, working on an add-in to parse code in a selected open workbook or a text file for any 32-bit declarations and either replace the code with the 64-bit equivalent or add it into a conditional declaration/compilation portion of the code. The brick wall I've come up against is making sure that any dimensioned variables are also converted. I'll share with you/all a beta version of where I'm up to once I've knocked it into some form that isn't too embarrassing.
I am, again, grateful to you for your help and indeed for this website.
Kind regards, Dan
Comment by: Jan Karel Pieterse (26-7-2021 10:16:00) deeplink to this comment
Hi Dan,
You're welcome! I'm certainly interested to see what your tool will look like.
Comment by: Ron Cook (13-8-2021 17:18:00) deeplink to this comment
Where are the updated virtual key codes listed?
-- Verify we are using the latest syntax
Best Regards...
Comment by: Jan Karel Pieterse (13-8-2021 17:36:00) deeplink to this comment
Hi Ron,
Which function are you referring to please?
Does this link help:
https://docs.microsoft.com/en-us/windows/win32/inputdev/virtual-key-codes
Comment by: Dan Wallace (21-10-2021 23:09:00) deeplink to this comment
Hello, I left a message on this board back in July, and now I'm back partly to ask another question and partly to give an update!
Question: I understand that the where the entry point ends with a 'A' or a 'W', that designates the ANSI and Unicode versions of the API, and I've read somewhere that the W variant are to be preferred, but the MS document (Win32api.txt) is limited to the A variant. I'd appreciate it if you could let me know if it's simply a matter of swapping the A for the W when making the declaration - or might there also be differences in the parameters? The one W variant in my slowly growing list of declarations is
Comment by: Jan Karel Pieterse (22-10-2021 14:24:00) deeplink to this comment
Hi Dan,
It looks like you only have to add the additional argument and figure out what the type of that argument needs to be, or remove an obsolete argument. For GetStringTypeA/W, the only difference is the first argument. LCID is in the A version and is missing from the W version.
To get the help information of a function, just google for the name of the function, followed by docs.microsoft. That usually gets you the most relevant hit.
Comment by: Vegard Leithe (2-11-2021 08:45:00) deeplink to this comment
Hello
I have tried to get out list items from a listbox from a 3rd party software. Are not able to figure it out. Have no issues controlling the software through edit windows, button windows etc. Have tried google for a week no. No success.
Are not able to figure it out. Was interesting to read about the LongPtr that need to be changed for a 64 bits. Tried that as well since I am sitting on a 64 bits win 10 machine.
Any hints for me?
I can share the code?
Regards
Vegard
Comment by: Jan Karel Pieterse (2-11-2021 09:42:00) deeplink to this comment
Hi Vegard,
You can share the code which is on this page if that is what you are asking?
But if you have a problem with your own code, feel free to post a question here, or consider asking on a dedicated forum, such as https://https://www.mrexcel.com/board/
Comment by: Diana (6-12-2021 21:34:00) deeplink to this comment
how can I fix this declaration to when running 64 bit
32-bit API declarations
Declare Function SHGetPathFromIDList Lib "shell32.dll" _
Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) As Long
Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long
Comment by: Jan Karel Pieterse (7-12-2021 10:02:00) deeplink to this comment
Hi Diana,
Both functions are listed in the article, can you indicate what your issue is please?
Comment by: Belaki (3-3-2022 17:01:00) deeplink to this comment
O365 64-bit VBA Declarations for File Search API Calls:
Public Declare PtrSafe Function FindClose Lib "kernel32" ( _
ByVal hFindFile As LongPtr _
) As Long
Public Declare PtrSafe Function FindFirstFile Lib "kernel32" Alias "FindFirstFileA" ( _
ByVal lpFileName As String, _
lpFindFileData As WIN_32_FIND_DATA _
) As LongPtr
Public Declare PtrSafe Function FindNextFile Lib "kernel32" Alias "FindNextFileA" ( _
ByVal hFindFile As LongPtr, _
lpFindFileData As WIN_32_FIND_DATA _
) As LongPtr
Public Declare PtrSafe Function PathMatchSpec Lib "Shlwapi" Alias "PathMatchSpecW" ( _
ByVal pszFileParam As LongPtr, _
ByVal pszSpec As LongPtr _
) As Long
Comment by: Jan Karel Pieterse (3-3-2022 17:03:00) deeplink to this comment
Thanks Blake! Odd enough, the API docs have a different type for the FindNextFile function (it says it returns a Long). I'm inclined to think that is a typo and it should indeed be a LongPtr
Comment by: Belaki (3-3-2022 17:41:00) deeplink to this comment
Yup, I almost fell out of my chair when I got it to work, after several days of scratching my head...
Comment by: John Burger (5-4-2022 00:21:00) deeplink to this comment
It appears that GlobalAlloc has the VBA7 and Else declarations swapped. I believe it should be as below:
#If VBA7 Then
Private Declare PtrSafe Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, ByVal dwBytes As LongPtr) As LongPtr
#Else
Private Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, ByVal dwBytes As Long) As Long
#End If
If not and it's correct as-is, then I recommend an explanation for why it otherwise seems backwards.
Comment by: Jan Karel Pieterse (5-4-2022 09:52:00) deeplink to this comment
Hi John,
You're right of course, I'll update the page ASAP, thanks!
Comment by: PAUL H HOSSLER (10-5-2022 13:35:00) deeplink to this comment
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/longptr-data-type
"LongPtr is not a true data type because it transforms to a Long in 32-bit environments, or a LongLong in 64-bit environments. Using LongPtr enables writing portable code that can run in both 32-bit and 64-bit environments. Use LongPtr for pointers and handles."
It would seem that using PtrSafe and LongPtr would eliminate the need to use conditional compilation for many API calls in 32 vs 64 bit office. Is this correct?
Comment by: Jan Karel Pieterse (10-5-2022 14:41:00) deeplink to this comment
Hi Paul,
I think you are correct, but I've never tried. But as long as Office 2007 is still being used, you risk (an ever smaller) group of users that get a compile error.
Comment by: Simon (10-10-2022 11:56:00) deeplink to this comment
Hi,
Why doesn't URLDownloadToFile use LongPtr for arguments and return type ?
I have found references where it has been used
e.g. https://stackoverflow.com/questions/59681699/download-file-from-url-in-excel-2019-it-works-on-excel-2007
Comment by: Jan Karel Pieterse (10-10-2022 13:17:00) deeplink to this comment
Hi Simon,
I suspect you are correct. I have updated this page.
Comment by: Axel de Valroger (6-1-2023 18:00:00) deeplink to this comment
Thx for this huge work !
I need win64 declaration of an API you don't have there :
GetWindowTextLengthA...
Looking for it elsewhere...
But if you find it before me, I'd appreciate the info...
Cheers
Axel
(France)
Comment by: Jan Karel Pieterse (9-1-2023 11:21:00) deeplink to this comment
Hi Axel,
I've added it:
https://jkp-ads.com/articles/apideclarations.asp#GetWindowTextLength
Comment by: Cristina Ghinea (30-1-2023 12:38:00) deeplink to this comment
It works to run the macro but I have the following error:
Compile error:User defined type not defined.
Do you know what can I do to not have this error?
Thank you!
Comment by: Jan Karel Pieterse (30-1-2023 14:48:00) deeplink to this comment
Hi Cristina,
Which macro are you referring to please?
Comment by: Thomas Rauner (27-3-2023 23:03:00) deeplink to this comment
Tested with Office 2010 x86 and Office 2019 x64:
#If VBA7 Then
Private Declare PtrSafe Function OleTranslateColor Lib "oleaut32.dll" _
(ByVal lOleColor As Long, ByVal lHPalette As Long, lColorRef As Long) As Long
#Else
Private Declare Function OleTranslateColor Lib "olepro32.dll" Alias "OleTranslateColor" _
(ByVal lOleColor As Long, ByVal lHPalette As Long, ByRef lColorRef As Long) As Long
#End If
Comment by: Jan Karel Pieterse (28-3-2023 10:32:00) deeplink to this comment
Hi Thomas,
Thanks, I've added it to this page:
https://jkp-ads.com/articles/apideclarations.asp#OleTranslateColor
Comment by: Will T (13-6-2023 22:12:00) deeplink to this comment
SetLayeredWindowAttributes for VBA7?
Comment by: Jan Karel Pieterse (14-6-2023 11:55:00) deeplink to this comment
Hi Will,
I think this should be the correct declaration:
Public Declare PtrSafe Function SetLayeredWindowAttributes Lib "user32" ( _
ByVal hWnd As LongPtr, _
ByVal crKey As LongPtr, _
ByVal bAlpha As Byte, _
ByVal dwFlags As Long) As Long
#Else
Public Declare Function SetLayeredWindowAttributes Lib "user32" ( _
ByVal hWnd As Long, _
ByVal crKey As Long, _
ByVal bAlpha As Byte, _
ByVal dwFlags As Long) As Long
#End If
Comment by: Dave (25-8-2023 05:06:00) deeplink to this comment
How about RegOpenKeyEx? I can't seem to find an example of how that should be declared for 64bit
Comment by: Jan Karel Pieterse (25-8-2023 16:50:00) deeplink to this comment
Hi Dave,
Perhaps this helps?
https://stackoverflow.com/questions/20479965/using-regopenkeyex-to-enumerate-through-registry-on-64bit-office-64-bit-windows
Comment by: GG (27-9-2023 09:59:00) deeplink to this comment
I want to know why MS document Win32api.txt is limited to the A variant.Doesn't need the W variant to change 32bit to 64bit migration.
Sorry for my bad question,please give me advice.Thanks you.
Comment by: Jan Karel Pieterse (27-9-2023 11:58:00) deeplink to this comment
Hi GG,
Whether to use the A or W variants depends whether you are using the Windows code page or Unicode, see:
https://learn.microsoft.com/en-us/windows/win32/intl/conventions-for-function-prototypes
I suspect the declarations can be easily translated from A to W simply by replacing the A with the W in the declaration line. It is best to read the documentation. Tip: To get to the docs of the w variant, simply replace the a with the w in the url of the documentation page.
Comment by: Henrique Schumacher (19-11-2023 20:33:00) deeplink to this comment
The "A" variants use the active code page for the system and usually accepts codepoints as 1 byte each, in pratice because windows uses UTF-16 internally, most "A" APIs will involve converting to UTF-16 internally
The "W" variants use UTF-16 in modern windows (and used to use UCS-2, which is compatible with UTF-16) which uses 2 bytes for each codepoint, while VB6 and VBA strings are directly compatible with this, VB6 converts any API declaration with "As String" to a string in the system code page, and i believe VBA probably does the same.
Which means any API call with strings will probably :
1- Make VB6/VBA convert the string to the system active code page with 1 byte for each codepoint
2- The A Api will internally convert back to UTF-16 with 2 bytes for each codepoint
In VB6 i used to solve this by either declaring the API in a typelib (which enabled me to use "As String" directly as a UTF-16 string without any implicit conversion) or treat it as a Long/LongPtr and use StrPtr(MyStringVariable) instead
So that's probably the reason they are limited to the A variant, it's because VB6 and VBA are probably doing a implicit conversion
Comment by: Jan Karel Pieterse (20-11-2023 09:54:00) deeplink to this comment
Hi Henrique,
Thanks for that clarification, very helpful!
Comment by: Andy Smith (16-7-2024 20:06:00) deeplink to this comment
I have three environments I'm trying to manage:
- Windows 10 / Office 2019/32
- Windows 10 / Office 365/64
- Windows 11 / Office 365/64
I've changed many Office/VBA artifacts with Windows API declarations by adding an #If VBA7 block, putting the old declarations AFTER the #Else part and putting the PtrSafe/LongPtr declarations BEFORE the #Else.
But VBA7 is true in all three environments! Isn't there a "VBA8" to determine Office 365? In any case I changed all the #If blocks to #If Win64 instead. Is that correct?
Comment by: Jan Karel Pieterse (16-7-2024 20:29:00) deeplink to this comment
Hi Andy,
If you don't expect anyone to be using an Excel version older than 2010(!), basically you can simply use the ptrSafe declarations without conditional compilation. The only exceptions are the ones on this page that include a separate clause with win64.
Comment by: Andy Smith (16-7-2024 20:49:00) deeplink to this comment
You wrote:
"Comment by: Jan Karel Pieterse (16-7-2024 20:29:00) deeplink to this comment
Hi Andy,
If you don't expect anyone to be using an Excel version older than 2010(!), basically you can simply use the PtrSafe declarations without conditional compilation. The only exceptions are the ones on this page that include a separate clause with win64."
Wait - 32-bit Office and 64-bit Office BOTH have LongPtrs and LongLongs? All the stuff I've read on the web about converting mentions conditional compilation using VBA7, not Win64. I'm pretty confused now, but rest assured Office 2019 and Windows 10 are the oldest versions of those products we use!
Comment by: Jan Karel Pieterse (16-7-2024 23:22:00) deeplink to this comment
As of Excel 2010, VBA is version 7, which knows to use the LongPtr type as a Long in 32 bit Office, or as a Long Long in 64 bit. That is why you can use almost all ptrSafe versions of the APIs, except for this one for example: GetWindowLong, which needs the #If win64 bit. . See above.
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
All comments about this page:
Comment by: Alexander Wolff (28-1-2010 12:01:43) deeplink to this comment
On a first glance:
- PtrSafe before "Function"
- LongPtr instead of "Long" (in some cases)
IsCharAlphaNumericA: "#Else" is probably not intended to be there?
Comment by: Jan Karel Pieterse (29-1-2010 02:40:08) deeplink to this comment
Hi Alexander,
Thanks for spotting the anomalies. Care to tell me which Long's must become LongPtr's?
Comment by: Ron Anderson (3-5-2010 17:13:21) deeplink to this comment
I am using Harbour Minigui and getting an error when writing data to Excel 2007. The error is DISP_E_BADPARAMCOUNT:ADD The routine works on my laptop but not at the office PC using Vista and Office 2007. Is there something that maybe set at the office PC either Vista or Office 2007 that maybe stopping this routine to work. Any help would greatly appreciated.
Comment by: Jan Karel Pieterse (4-5-2010 00:36:23) deeplink to this comment
Hi Ron,
Unfortunately, I have never used that application before, so I am not in the position to advise. I would suggest to contact the manufacturer about your problem.
Comment by: Anders Hauge (20-5-2010 10:35:46) deeplink to this comment
An updated version of WINAPI.txt is availble for download here: http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=035b72a5-eef9-4baf-8dbc-63fbd2dd982b
Notice that the new file is called Win32API_PtrSafe.TXT and by default is installed to: C:\Office 2010 Developer Resources\
Comment by: Jan Karel Pieterse (21-5-2010 01:37:45) deeplink to this comment
Thanks Anders!!!
Comment by: Yuhong Bao (6-6-2010 00:23:09) deeplink to this comment
"Private Declare PtrSafe Function GetWindowLongptr Lib "USER32" Alias "GetWindowLongA" (ByVal hWnd As LongPtr, ByVal nIndex As Long) As LongPtr"
"Private Declare PtrSafe Function GetWindowLongptr Lib "USER32" Alias "GetWindowLongA" (ByVal hWnd As LongPtr, ByVal nIndex As Long) As LongPtr"
Wrong, sorry. I think the best bet here is to use Win64 instead of VBA7 and use Alias "GetWindowLongPtrA" and Alias "SetWindowLongPtrA". That is how it is defined in the headers.
Comment by: Jan Karel Pieterse (6-6-2010 23:09:30) deeplink to this comment
Hi Yuhong,
Thanks. Could you perhaps show the proper declarations in full?
Comment by: Yuhong Bao (6-6-2010 23:14:06) deeplink to this comment
Private Declare PtrSafe Function GetWindowLongPtr Lib "USER32" Alias "GetWindowLongPtrA" (ByVal hWnd As LongPtr, ByVal nIndex As Long) As LongPtr
Private Declare PtrSafe Function SetWindowLongPtr Lib "USER32" Alias "SetWindowLongPtrA" (ByVal hWnd As LongPtr, ByVal nIndex As Long) As LongPtr
Note that this Declare should be conditialized on Win64 instead of VB7, because Get/SetWindowLongPtr do not exist as an export on 32-bit Windows.
Comment by: Jan Karel Pieterse (7-6-2010 00:21:24) deeplink to this comment
Hi Yuhong,
Thanks!
Comment by: Yuhong Bao (7-6-2010 13:15:58) deeplink to this comment
The Declare for SetWindowLongPtr above is wrong. Here is the correct one:
Private Declare PtrSafe Function SetWindowLongPtr Lib "USER32" Alias "SetWindowLongPtrA" (ByVal hWnd As LongPtr, ByVal nIndex As Long, ByVal dwNewLong As LongPtr) As LongPtr
Comment by: Clarence (14-7-2010 08:43:57) deeplink to this comment
What if your targeted users are using both 32 and 64 bit versions, do you need to create two versions of the document?
Comment by: Jan Karel Pieterse (14-7-2010 10:58:42) deeplink to this comment
Hi Clarence,
No, but you will need code like the following:
#If VBA7 Then
Declare PtrSafe Function GetKeyState Lib "USER32" (ByVal vKey As Long) As Integer
#Else
Declare Function GetKeyState Lib "USER32" (ByVal vKey As Long) As Integer
#End If
Comment by: Villable (16-8-2010 10:53:06) deeplink to this comment
I realised that my macros programed at 32 bits are slower at 64 bit office. Specially one macro, with two nested loops, that could generate a report in 15 seconds and now takes an hour.
Why?
How can i do faster my macros at 64 bits?.
Thanks
Comment by: Jan Karel Pieterse (16-8-2010 11:16:35) deeplink to this comment
Hi Villable,
Depends on the code. Could you post some of it?
Comment by: Villable (16-8-2010 15:38:09) deeplink to this comment
I think is not the code. The same code runned at 32 bits works fine and very fast (the report takes only about 15 seconds). Here is the code:
----------------
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Union(Target, Range("L5")).Address = _Range("L5").Address Then Call Calendario
End Sub
---------------
Private Sub Calendario()
ActiveSheet.Unprotect ("BCG")
f = Cells(1, 1) 'f=fila de comienzo
ff = Cells(1, 2) 'ff=fila final
m = Range("L5").Value 'm = mes
If ff > f Then Range("A" & f & ":A" & ff).EntireRow.Delete shift:=xlUp 'delete existing report
'New report
For o = Hoja3.Cells(1, 1) To Hoja3.Cells(1, 2)
Range("A2:A3").EntireRow.Copy'copy line
Rows(f).EntireRow.Insert shift:=xlDown 'insert line
Range("A" & f & ":A" & f + 1).EntireRow.Hidden = False 'show line
Cells(f, 1) = Hoja3.Cells(o, 1)
Cells(f, 2) = Hoja3.Cells(o, 2)
Hoja2.Range("AP3").Value = Hoja3.Cells(o,1)
f = f + 1
p = Hoja2.Cells(1, 1)
ff = Hoja2.Cells(1, 2) - 25
While p < ff
If Hoja2.Cells(p - 1 + m * 2, 42).Value > 0 Then
Rows(4).EntireRow.Copy
Rows(f).EntireRow.Insert shift:=xlDown
Rows(f).EntireRow.Hidden = False
Cells(f, 1) = Hoja3.Cells(o, 1)
Cells(f, 2) = Hoja2.Cells(p, 1).Value
Cells(f, 3) = (p - 5) / 26
Cells(f, 4) = Hoja2.Cells(p + m * 2, 42)
Cells(f, 6) = Hoja2.Cells(p - 1 + m * 2, 42) / Hoja2.Cells(p - 1 + m * 2, 36) * 100
f = f + 1
End If
p = p + 26
Wend
f = f + 1
Cells(f, 1).Select
Next
FIN:
Cells(Cells(1, 1), 1).Select
ActiveSheet.Protect ("BCG")
Beep
End Sub
----------------
Thanks
Comment by: Jan Karel Pieterse (16-8-2010 23:12:21) deeplink to this comment
Hi Villable,
I see no obvious reason for the slowness of your code on 64 bit. But you may benefit from turning off screenupdating and calculation when the code starts:
Application.Calculation = xlCalculationManual
'Your code goes here
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Comment by: Villable (17-8-2010 01:01:53) deeplink to this comment
I tried what you said but the result was very little faster. So i prefer the report could see while is creating.
Variable 'o' goes from 1 to 20 and variable 'p' goes from 1 to 42. So in 64 bits it takes, at least, AN HOUR while in 32 bits it takes 15 SECONDS!!!
I don't know which is the difference. I think is a bug of Office 2010 64bits, and i'm thinking about install 32 bits to get that problems off.
Thank you anyway.
Comment by: Doug Drada (11-10-2010 07:46:38) deeplink to this comment
I have 20 working and tested function declarations that are not on the list. Can I send you a text file with then or would you like me to paste one at a time in this box.
Comment by: Jan Karel Pieterse (11-10-2010 07:58:19) deeplink to this comment
Hi Doug,
Please post them!
Can't you combine them into one post?
If unsure, please go ahead and send me an email (see bottom of page).
Comment by: Boomer57 (26-11-2010 16:17:06) deeplink to this comment
Hi,
Great site!
Since I'm cutting and pasting this code into VBA7, wrapping each api call with the:
#If VBA7 Then
.... 'New 64-bit declarations here...
#Else
.... 'Legacy 32-bit declarations here...
#End If
would be appreciated.
Thank you!!!
Comment by: Excelo (11-12-2010 12:01:34) deeplink to this comment
This works fine ..thsnks
Comment by: Tomas (15-12-2010 11:45:56) deeplink to this comment
Hi,
As I'am no expert in this matter i feel the need to ask for help on this matter.
I cant get these Api declarations to work with a 64bit office version. The 32bit declarations work fine...
thanks in advance
Tomas
Option Explicit
#If VBA7 Then
Private Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr
Private Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
(ByVal hWnd1 As LongPtr, ByVal hWnd2 As LongPtr, ByVal lpsz1 As String, ByVal lpsz2 As String) As IntPtr
Private Declare PtrSafe Function SendMessage Lib "user32" Alias "SendMessageA" _
(ByVal hwnd As LongPtr, ByVal wMsg As Long, ByVal wParam As LongPtr, ByVal lParam As Any) As LongPtr
#Else
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
(ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
(ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Integer, ByVal lParam As Any) As Long
#End If
Comment by: Jan Karel Pieterse (16-12-2010 01:22:18) deeplink to this comment
Hi Tomas,
The type IntPtr does not exist (second declare statement in your code)
Comment by: Tomas (16-12-2010 12:15:34) deeplink to this comment
Thanks for the help!!
Comment by: Satyendra (3-2-2011 08:04:12) deeplink to this comment
Hi,
Can you help me, to place LongPtr insted of Long in delarations. I am posting all my declarations in 2 or 3 posts. Because here I can't post more then 2000 chars in one post
Public Declare PtrSafe Function WSAStartup Lib "wsock32.dll" (ByVal wVersionRequired&, lpWSADATA As WSADATA) As Long
Public Declare PtrSafe Function WSACleanup Lib "wsock32.dll" () As Long
Public Declare PtrSafe Function gethostbyname Lib "wsock32.dll" (ByVal host_name As String) As Long
Public Declare PtrSafe Function gethostname Lib "wsock32.dll" (ByVal host_name As String, ByVal namelen As Integer) As Integer
Public Declare PtrSafe Function setsockopt Lib "wsock32.dll" (ByVal S As Long, ByVal level As Long, ByVal optname As Long, optval As Any, ByVal optlen As Long) As Long
Public Declare PtrSafe Function w_socket Lib "wsock32.dll" Alias "socket" (ByVal lngAf As Long, ByVal lngType As Long, ByVal lngProtocol As Long) As Long
Public Declare PtrSafe Function w_closesocket Lib "wsock32.dll" Alias "closesocket" (ByVal socketHandle As Long) As Long
Public Declare PtrSafe Function w_bind Lib "wsock32.dll" Alias "bind" (ByVal SOCKET As Long, Name As SOCKADDR_IN, ByVal namelen As Long) As Long
Public Declare PtrSafe Function w_connect Lib "wsock32.dll" Alias "connect" (ByVal SOCKET As Long, Name As SOCKADDR_IN, ByVal namelen As Long) As Long
Public Declare PtrSafe Function w_send Lib "wsock32.dll" Alias "send" (ByVal SOCKET As Long, buf As Any, ByVal length As Long, ByVal Flags As Long) As Long
Public Declare PtrSafe Function w_sendTo Lib "wsock32.dll" Alias "sendto" (ByVal SOCKET As Long, buf As Any, ByVal length As Long, ByVal Flags As Long, remoteAddr As SOCKADDR_IN, ByVal remoteAddrSize As Long) As Long
Public Declare PtrSafe Function w_recv Lib "wsock32.dll" Alias "recv" (ByVal SOCKET As Long, buf As Any, ByVal length As Long, ByVal Flags As Long) As Long
Comment by: Jan Karel Pieterse (3-2-2011 11:38:52) deeplink to this comment
Hi Satyendra,
I'm sorry I can't help you with that on short notice, I'm way too busy to do this for you.
I suggest you to find the information on each of the functions you mention on the Microsoft MSDN site:
http://msdn.microsoft.com/en-us/library/aa383749(v=vs.85).aspx
Like I mentioned above:
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.
Comment by: Satyendra (4-2-2011 01:24:48) deeplink to this comment
Hi,
Thanks for your quick response. I tried to find those functions in your given MSDN site. But I didn't find any one of them. Can you help me sort this issue or suggest some good material where I can get all these function syntaxes.
Satyendra
Comment by: Jan Karel Pieterse (4-2-2011 03:28:51) deeplink to this comment
Hi Satyendra,
I think you'll find your information here:
http://msdn.microsoft.com/en-us/library/ms741394(VS.85).aspx
Comment by: Satyendra (7-2-2011 10:14:42) deeplink to this comment
Hi,
I modified all the declarations, except the select statement. Can you please help to modify it.
Public Declare PtrSafe Function w_select Lib "wsock32.dll" Alias "select" (ByVal nfds As Long, readFds As fd_set, writeFds As fd_set, exceptFds As fd_set, timeout As timeval) As Long
Satyendra
Comment by: Maria (4-4-2011 11:40:45) deeplink to this comment
Hello. I cut an pasted your code in the GetLastInputInfo from the https://jkp-ads.com/articles/apideclarations.asp web page (or any other dual 32 / 64 bit example)
I still get compile errors on the 32 bit declares. What am I missing?
I am using Excel 64 bit (14.0.5128.5000 64 bit) and Windows 7 Professional (64 bit).
Comment by: Jan Karel Pieterse (4-4-2011 23:23:49) deeplink to this comment
Hi Maria,
Can you post your code and indicate what line gets highlighted when the compile error is shown?
Comment by: Maria (7-4-2011 09:08:45) deeplink to this comment
Below is the code in question. I have indicated the line that will not compile. Thank you.
---------------------------------------------
#If VBA7 Then
Private Type LASTINPUTINFO
cbSize As LongPtr
dwTime As LongPtr
End Type
Private Declare PtrSafe Sub GetLastInputInfo Lib "USER32" (ByRef plii As LASTINPUTINFO)
#Else
Private Type LASTINPUTINFO
cbSize As Long
dwTime As Long
End Type
'>>>>>>> The next line is the line that will not compile <<<<<<<<<<<<<
Private Declare Sub GetLastInputInfo Lib "USER32" (ByRef plii As LASTINPUTINFO)
#End If
Comment by: Pablo (7-4-2011 16:36:54) deeplink to this comment
Hey man, thanks a lot. This means money for me.
Comment by: Jan Karel Pieterse (8-4-2011 02:39:40) deeplink to this comment
Hi Maria,
Odd, as those line should never be reached in 64 bit Excel and they do not give a compile error on my 32 bit Excel 2010. Do you happen to have any references indicated as missing?
Comment by: Maria (8-4-2011 06:08:08) deeplink to this comment
Jan,
The following references are checked:
- Visual Basic For Applications
- Microsoft Excel 14.0 Object Library
- OLE Automation
- Microsoft Office 14.0 Object Library
I quickly scrolled through the entire reference list and did not see anything marked "Missing".
Comment by: Maria (9-4-2011 10:07:17) deeplink to this comment
I uninstalled and reinstalled Office 2010. I can now compile the
32 bit declares. I guess one of the libraries was corrupted. Thank
you for your time.
Comment by: Jan Karel Pieterse (9-4-2011 10:08:39) deeplink to this comment
Hi Maria,
Thanks for letting me know, great you could solve your problem.
NB: in the references list, the checked ones are always at the top and those are also the only ones that might be marked "MISSING", so there is no need to scroll down.
Comment by: Ulrik (11-5-2011 22:30:56) deeplink to this comment
There is a document from MS which I think covers most API 64-bits declarations and constants:
Win32API_PtrSafe.txt contains the following:
32-bit (x86) and 64-bit (x64) compatible Declare statements for the Windows API functions that were included in the original Win32API.txt file.
Global constant declarations for the constants that the provided Declare statements use.
Type declarations for the user-defined types (structures) that the provided Declare statements use.
http://www.microsoft.com/downloads/en/confirmation.aspx?FamilyID=035b72a5-eef9-4baf-8dbc-63fbd2dd982b&displaylang=en
Comment by: Jan Karel Pieterse (12-5-2011 00:56:41) deeplink to this comment
Hi Ulrik,
Thanks for the link!
NB: what isn't mentioned is that the executable unzips the information to this folder on your hard drive:
C:\Office 2010 Developer Resources\Documents\Office2010Win32API_PtrSafe
Comment by: Gregory Morse (8-6-2011 02:25:19) deeplink to this comment
I disagree with WPARAM (such as in SendMessage) as it is defined as a UINT_PTR which is defined as 64 bit pointer on 64 bit platforms and a 32 bit pointer on 32 bit platforms per MSDN and windef.h:
typedef UINT_PTR WPARAM;
#if defined(_WIN64)
typedef unsigned __int64 UINT_PTR;
#else
typedef unsigned int UINT_PTR;
#endif
Comment by: Jan Karel Pieterse (8-6-2011 07:54:53) deeplink to this comment
Hi Gregory,
Thanks!
Maybe you're right, I'll have to check.
Comment by: Karen (29-6-2011 10:24:36) deeplink to this comment
Do you know how to modify CreateProcessA to work with the 64 bit operating system?
Comment by: Jan Karel Pieterse (30-6-2011 00:19:11) deeplink to this comment
Hi Karen,
I've included the function, see top of the table on this page.
Comment by: Martin Orlicky (20-7-2011 08:21:45) deeplink to this comment
Hi, I've got MS Excel 2003 file with VBA macros, but I already have MS 2010 with 64bit rate. So, I cannot run macros and get the VBA respond "Compile error: Can't find object or library". Then I Reset VBA macro and went to References-VBAProjects via Tools. I found there MISSING: Microsoft Office Web Components 11.0. My IT colleague told me that owc11 is 32bit version only. Can do I sonething with this or replace owc11 with some other file?
Concrete, these two commands don't work:
1. Set mymenu = Application.CommandBars.Add(Name:="Test_Check_Toolbar", Position:=msoBarBottom, Temporary:=True)
2. Function APR_calc(RIV As Double, Payment_Frequency As Integer, term As Integer, grace_period As Integer, first_pmt As Double, weekly_pmt As Double, final_pmt As Double)
May you please help somehow? Thanks, Martin
Comment by: Jan Karel Pieterse (22-7-2011 07:32:49) deeplink to this comment
Hi Martin,
Unfortunately, you cannot use 32 bit controls in 64 bit Office and the Office Web controls are strictly 32 bit.
So you will have to revert to a different method entirely.
If you want to be sure an Excel application works on both 32 and 64 bit, use only the built-in Office controls.
I'm a bit surprised the 1st command does not work, but I strongly suspect this is due to the missing reference.
Comment by: Rx_ (30-9-2011 08:35:48) deeplink to this comment
Re: http://msdn.microsoft.com/en-us/library/ee691831.aspx
My client uses Citrix to distribut my MS Access application that uses Windows API declare statements.
The article describes Office 2007 32/64 bit. And most users are probably running from Windows 7. Can you please exapand on a situation for migrating to Windows Server 2008 R2 and the 32 Bit of Office 2010? Is the PrtSafe required for this or could there be other factors?
Moving a working application over to the new server this week, the API functions fail.
New server: Windows Server 2008 R2 Office 2010 32 Bit
The exact same Access code is still working fine on the Windows Server 2003 using Office 2007.
Best Regards
Rx
Tesla 3D, Denver
Comment by: Jan Karel Pieterse (2-10-2011 23:46:11) deeplink to this comment
Hi Rx,
As long as your users are on 32 bit Office your API declarations should work unchanged.
Comment by: Rx_ (3-10-2011 08:02:05) deeplink to this comment
Took the same code to home. The DLL works perfectlly on another XP SP3 Workstation with Office 2007.
On a Windows 7 (64 bit) with Office 2010, the error message that the DLL must be updated with PrtSafe is required (as we all expected.
Back at the customer site, the same copy was run on a new Windows 7 Enterprise (32 bit) with Office 2010. It still fails. Setting the MS Access 2010 exe running as administrator in Compatibility Mode for XP sp 3 - it still fails.
Another site recommend checking the Err.LastDllError at various points in the code. And, suggested that there are some DLL for Windows 7 that may have to be upgraded.
Rx Tesla 3D, Denver
Comment by: Jan Karel Pieterse (4-10-2011 04:32:45) deeplink to this comment
I run Windows Server 2008 R2 64 bit and Ofice 2010 32 bit. I have not had to update any of my API function declarations on my system in order for them to work.
The files I tried work on any Office version since Office 97 up to and including 2010.
Are you SURE this isn't a 64 bit version of Excel?
Comment by: Ger (12-10-2011 12:52:57) deeplink to this comment
I'm just starting down this road and I believe you need to talk more about the need to use the Win64 constant.
My own scenario is: Office 2007 (32 bit), Office 2010 (32 bit) and Office 2010 (64 bit). Code and declarations need to work on all of them.
I cant just use #if VBA7 then use LongLong and LngPtr declarations, because if I do this it will fail to compile on Office 2010 32 bit with a "User Defined Type Not defined" and highlighting the line that contains LongLong
Granted, the compiler statement works fine on Office 2010 64 bit.
The #Else part of the conditional statement catches the compiler for Office 2007 32 bit and there arent any issues there.
But to get declarations to work correct across the three platforms, I believe I need to use the win64 constant, as per the MS 2010 Help file:
' Conditional Compilation Example
#If Vba7 Then
' Code is running in the VBA7 editor.
#If Win64 Then
' Code is running in 64-bit development environment.
#Else
' Code is not running in 64-bit development environment.
#End If
#Else
' Code is not running in the VBA7 editor.
#End If
It makes the declarations awfully long!!!!
Of course, I could be doing something wrong too. :-)
Appreciate the feedback. I can post my current declarations if you wish to see them to cover all 3 platforms (GetActiveWindow, ChooseColorDlg).
Ger
Comment by: Jan Karel Pieterse (12-10-2011 23:48:43) deeplink to this comment
Hi Ger,
Thanks for the comments. All declarations shown here should work on those versions, but I guess there may be situations where the win64 compile constant is needed. If you have an example, please post it here and I will include it in the article text!
Comment by: Ger Plante (13-10-2011 02:39:19) deeplink to this comment
Great - thanks email sent to webmaster@jkp-ads.com... the sample exceeded the 2000 character limit, as I wanted to explain/build up the issue I encountered (and resolved!)
Let me know if you need me to email it to a different address.
Best Regards
Ger
Comment by: Jan Karel Pieterse (13-10-2011 04:29:07) deeplink to this comment
Hi Ger,
Email received and replied-to, thanks.
Comment by: Jochen Wezel (20-10-2011 02:40:04) deeplink to this comment
In following 2 nice detection functions for general usage (but don't know if the VBA version check works the same way as coded here for all Office versions in past and future):
Option Explicit
Option Compare Database
Public Enum OfficePlatformVersion
Office64 = 2
Office32 = 1
End Enum
Public Function DetectOfficeVbaVersion() As Integer
#If VBA10 Then
DetectOfficeVbaVersion = 10
#ElseIf VBA9 Then
DetectOfficeVbaVersion = 9
#ElseIf VBA8 Then
DetectOfficeVbaVersion = 8
#ElseIf VBA7 Then
DetectOfficeVbaVersion = 7
#ElseIf VBA6 Then
DetectOfficeVbaVersion = 6
#ElseIf VBA5 Then
DetectOfficeVbaVersion = 5
#ElseIf VBA4 Then
DetectOfficeVbaVersion = 4
#ElseIf VBA3 Then
DetectOfficeVbaVersion = 3
#ElseIf VBA2 Then
DetectOfficeVbaVersion = 2
#ElseIf VBA1 Then
DetectOfficeVbaVersion = 1
#Else
DetectOfficeVbaVersion = 0
#End If
End Function
Public Function DetectOfficePlatformVersion() As OfficePlatformVersion
#If Win64 Then
DetectOfficePlatformVersion = OfficePlatformVersion.Office64
#Else
DetectOfficePlatformVersion = OfficePlatformVersion.Office32
#End If
End Function
Comment by: Yvette (24-10-2011 11:20:58) deeplink to this comment
Ger,
Please also email the code you created for working with Office 2007 (32 bit), Office 2010 (32 bit) and Office 2010 (64 bit). I have the exact same scenario
Comment by: Rx_ (24-10-2011 11:39:14) deeplink to this comment
Thanks for the code example above. It validated my code. My Office 2010 is 32 bit, OS is 32 bit, VBA is 6.
The last DLL error is 87
It It consistantlly works on XP Access 2007 but not on Windows 7 Access 2010.
Comment by: BERNARDO FIAUX (25-10-2011 17:48:55) deeplink to this comment
Hi. Im having problem with the following line:
Declare Function impliedBS Lib "c:\dll\option.dll" (ByVal flag As Double, ByVal prazo As Double, ByVal spot As Double, ByVal strike As Double, ByVal taxa As Double, ByVal mercado As Double, ByVal dividend As Double) As double
i tried to add the ptrsafe, like this:
Declare PtrSafe Function impliedBS Lib "c:\dll\option.dll" (ByVal flag As Double, ByVal prazo As Double, ByVal spot As Double, ByVal strike As Double, ByVal taxa As Double, ByVal mercado As Double, ByVal dividend As Double) As Long
but the function is still not working, im wondering why...any idea?
tks
Comment by: Jan Karel Pieterse (26-10-2011 02:59:52) deeplink to this comment
Hi Yvette,
I advised Ger to replace all LongLong's with LongPtr. Have not heard from him whether that was sufficient.
Comment by: Jan Karel Pieterse (26-10-2011 03:01:46) deeplink to this comment
HI BERNARDO,
I'm sorry, but I don't appear to have that dll, so it is not possible for me to answer your question!
NB: if you are on Office 64 bit and the dll is a 32 bit dll, then it will never work.
Comment by: NEX-5 (11-11-2011 18:56:26) deeplink to this comment
This really solved my problem, thank you!
Comment by: @Alex (20-11-2011 10:57:47) deeplink to this comment
Very fine example and excellent explanation.
@Alex
Comment by: enzo (21-11-2011 04:16:47) deeplink to this comment
(working and tested!!!) ChooseColor function??
here a sample already modified for 64 bit but crash al line:
CC.lpCustColors = StrConv(aCustomColors, vbUnicode)
(Version 32 bit works properly)
*** ERROR: Runtime 13 Conversion type error
#If VBA7 Then
Dim lReturn As LongLong
Dim aCustomColors(0 To 16 * 4 - 1) As Long
Dim i As Integer
Dim lpCC As LongPtr
For i = LBound(aCustomColors) To UBound(aCustomColors)
aCustomColors(i) = 0
Next i
lpCC = StrConv(aCustomColors, vbUnicode)
Dim CC As CHOOSECOLOR
CC.lStructSize = CLng(Len(CC))
CC.hWndOwner = Application.hwnd
CC.hInstance = CLng(0)
CC.lpCustColors = StrConv(aCustomColors, vbUnicode)
CC.flags = CLng(0)
lReturn = ChooseColor(CC)
If lReturn <> 0 Then
Me.Caption = "RGB Value User Chose: "& str$(CC.rgbResult)
Me.idColore = CC.rgbResult
Else
MsgBox "No color selected"
End If
#Else
Dim CC As CHOOSECOLOR
Dim lReturn As Long
Dim aCustomColors(0 To 16 * 4 - 1) As Byte
Dim i As Integer
For i = LBound(aCustomColors) To UBound(aCustomColors)
aCustomColors(i) = 0
Next i
CC.lStructSize = Len(CC)
CC.hWndOwner = Me.hwnd
CC.hInstance = 0
CC.lpCustColors = StrConv(aCustomColors, vbUnicode)
CC.flags = 0
lReturn = ChooseColor(CC)
If lReturn <> 0 Then
Me.Caption = "RGB Value User Chose: " & str$(CC.rgbResult)
Me.idColore = CC.rgbResult
Else
MsgBox "No color selected"
End If
#End If
Comment by: Jan Karel Pieterse (21-11-2011 04:56:46) deeplink to this comment
Hi enzo,
Why are you declaring aCustomColors as a Long in the VBA7 case? I'd expect both declares should be of type Byte.
Since I see no differences in the VBA7 case or the Else pare, the entire conditional compilation is not needed?
Comment by: ENZO (21-11-2011 08:35:22) deeplink to this comment
ok thanks for the answer.
You are right I've changed type to Long for #VA7
The difference is in function and TYPE CHOOSECOLOR declarations (not wrote above)
here are the two different declarations:
if #Vba7 then
Type CHOOSECOLOR
lStructSize As Long
hWndOwner As LongPtr
hInstance As LongPtr
rgbResult As Long
lpCustColors As LongPtr
flags As Long
lCustData As LongPtr
lpfnHook As LongPtr
lpTemplateName As String
End Type
Declare PtrSafe Function ChooseColor Lib "comdlg32.dll" Alias "ChooseColorA" (pChoosecolor As CHOOSECOLOR) As Long
#else
Type CHOOSECOLOR
lStructSize As Long
hWndOwner As Long
hInstance As Long
rgbResult As Long
lpCustColors As Long
flags As Long
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type
Declare Function ChooseColor Lib "comdlg32.dll" Alias "ChooseColorA" (pChoosecolor As CHOOSECOLOR) As Long
#end if
Comment by: Jan Karel Pieterse (21-11-2011 10:16:53) deeplink to this comment
Hi Enzo,
I still think the aCustomeColors array should remain an array of type Byte.
Comment by: enzo (21-11-2011 11:33:06) deeplink to this comment
OK, changed in byte, but the problem is on instruction:
CC.lpCustColors = StrConv(aCustomColors, vbUnicode)
Color dialog box don't appears
Comment by: Jan Karel Pieterse (21-11-2011 22:21:44) deeplink to this comment
Can you email me a copy of your file?
Comment by: enzo (9-12-2011 03:49:36) deeplink to this comment
' 32 BIT VERSION works properly (under 32 bit):
Option Compare Database
Option Explicit
Declare Function ChooseColor Lib "comdlg32.dll" _
Alias "ChooseColorA" (pChoosecolor As CHOOSECOLOR_TYPE) _
As Long
Type CHOOSECOLOR_TYPE
lStructSize As Long
hwnd As Long
hInstance As Long
rgbResult As Long
lpCustColors As String
Flags As Long
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type
' ===============================================
Sub TestGetColor()
Dim c
c = GetColor()
If c > 0 Then
MsgBox "SELECTED COLOR: " & c
Else
MsgBox "NO COLOR SELECTED"
End If
End Sub
Function GetColor()
Dim CustomColors() As Byte ' dynamic (resizable) array holds the RGB values
Dim cc As CHOOSECOLOR_TYPE ' structure to pass/receive information
Dim getC As Long ' return value of function
Dim rVal As Long ' return value of function
ReDim CustomColors(0 To 16 * 4 - 1) As Byte ' resize the array
Dim i As Integer ' counter variable
For i = LBound(CustomColors) To UBound(CustomColors)
CustomColors(i) = 0 ' set all custom color choices to black (RGB = 0,0,0)
Next i
cc.hwnd = Application.hWndAccessApp
cc.lpCustColors = StrConv(CustomColors, vbUnicode) ' convert custom color array
cc.Flags = &H80
cc.lStructSize = Len(cc) ' the size of the structure
getC = ChooseColor(cc) ' open the dialog box
If getC <> 0 Then ' if the user successfully chose a color
CustomColors = StrConv(cc.lpCustColors, vbFromUnicode) ' save custom colors
rVal = cc.rgbResult
Else
rVal = -1
End If
GetColor = rVal
End Function
Comment by: Jan Karel Pieterse (9-12-2011 05:18:43) deeplink to this comment
Hi Enzo,
Thanks.
Comment by: Ali (27-2-2012 05:57:23) deeplink to this comment
Hi,
thanks for the solution but could you please take me through it step by step as I am an amateur in VB. My whole office is struggling with the 32bit-64bit compatibility issue.
Regards,
Ali
Comment by: Jan Karel Pieterse (5-3-2012 05:14:12) deeplink to this comment
Hi Ali,
If your whole Office needs help with this, perhaps it is a better idea if you get in touch so we can arrange to start a project on this? (see my email address at the bottom of the screen)
Comment by: Macarius (22-3-2012 23:29:37) deeplink to this comment
CopyMemory API: is there a 64-bit version of the API?
Running Windows 7 x64, Office 2007 (32-bit, I believe).
CopyMemory API crashes Excel. Need this ASAP for work. Would most appreciate your thoughts/solutions.
Respectfully,
Macarius
Comment by: Jan Karel Pieterse (23-3-2012 03:58:42) deeplink to this comment
Hi Macarius,
Look at this page:
http://msdn.microsoft.com/en-us/library/windows/desktop/aa366535(v=vs.85).aspx
It tells us the first two arguments are pointers, so they need to be changes to LongPtr for 64 bit.
Comment by: Theo Vroom (5-5-2012 09:19:35) deeplink to this comment
Hi,
I am developping in 32 bit Office 2003 and a number of users run 32 bit office. We now have the first Windows 7 64 bit users and I installed Office 2010 64 bit.
How can I (maybe with a conditional compiler constant, but at least office 2003 does not have eg vba7 or 64bit) continue to develop in 32 bit office, also for use in 64 bit office.
I cannot upgrade to 64 bit myself now.
The easy solution, I believe, would be to install 32 bit office on w7 64 bit, but I like the challenge
Comment by: Jan Karel Pieterse (6-5-2012 11:11:43) deeplink to this comment
Hi Theo,
I expect all code that works on 32 bit Office 2010 also to work on 64 bit, except for API declarations. Also, 32 bit ActiveX controls that are not part of Office will not work.
I'd install 32 bit Office and have a virtual machine with 64 bit Office for testing purposes.
Comment by: Natalie (31-5-2012 19:49:56) deeplink to this comment
Hello, I was wondering how can a person store a value without having to access the worksheet itself? Here is what works but only accessing the worksheet - which I perfer not to do:
Do
PreviousValue=[a1]
CurrentValue = ComPortInput
PreviousValue = CurrentValue
[a1]=PreviousValue
Loop
If you can, please respond with email, thank you.
Comment by: Jan Karel Pieterse (1-6-2012 10:41:51) deeplink to this comment
Hi Natalie,
Can you try to explain what you are trying to achieve? Where would that value you want to store come from?
Perhaps this page gives you some ideas:
https://jkp-ads.com/Articles/DistributeMacro.asp
Comment by: Natalie (1-6-2012 14:29:21) deeplink to this comment
Thank you for your response, I don't quite remember the code I used last time but it's something like this:
Do
PreviousData=[a1]
CurrentData=ComPortInput
DeltaData=CurrentData-PreviousData
PreviousData=CurrentData
[a1]=PreviousData
Loop
I would like to be able to store the PreviousData without having to go to the worksheet cell A1 (or any cell). The data coming in (CurrentData) is from the serial RS232 CommPort. I would like to be able to do this, store the data, perhaps 3,4 times back, i.e.: PreviousData4 without having to store and retrieve from the worksheet. Again, thanks.
Comment by: Jan Karel Pieterse (1-6-2012 14:50:14) deeplink to this comment
Hi,
I think I understand now.
Declare your variable at the top of the module:
Dim PreviousValue As Variant 'Or another appropiate type
'All Subs and functions follow below
Comment by: Natalie (1-6-2012 15:10:40) deeplink to this comment
Thanks again for your reply. Setting the DIM alone doesn't work, at least in my case because it's not being "stored" for later retrieval as the data changes. With my knowledge, I had to "store" it in the worksheet and then retrieve it. I was hoping there was someway, in the module to store it, or even using some other module. By the way I do have it DIM as String but that doesn't store it, that I can see. I appreciate your time and info.
Comment by: Jan Karel Pieterse (1-6-2012 19:15:14) deeplink to this comment
Dim-ing the variable *above* the sub in the declaration area ensures the information is persisted for as long as the workbook stays open. Of course the very first time the perv value will be empty. And of course you have to omit the PreviousData=[A1]
Comment by: Natalie (2-6-2012 17:37:35) deeplink to this comment
ah.... I only applied the Dim at the immediate location of the code, ie near HDG and not in the declaration area - ugh, thanks. I tested it with this:
Dim Eacc As Integer
Sub RunIt()
DoEvents
RunNow = Now + TimeSerial(0, 0, 1)
Application.OnTime RunNow, "RunIt", , True
HDG = [a1]
Eacc = Eacc + HDG
[b1] = Eacc
DoEvents
End Sub
Comment by: David McIntosh (4-6-2012 16:33:21) deeplink to this comment
In your table entry for CreateProcess, for 64bit declarations, first two members of PROCESS_INFORMATION should be LongPtr, not Long
Comment by: Jan Karel Pieterse (4-6-2012 18:15:37) deeplink to this comment
Hi David,
Well spotted, thank you!
Page updated.
Comment by: David I. McIntosh (4-6-2012 19:32:37) deeplink to this comment
first parameter to SECURITY_ATTRIBUTES (nLength) should be Long, not LongPtr
Comment by: David I. McIntosh (4-6-2012 19:35:34) deeplink to this comment
This list seems to be fairly comprehensive, and so far I've found no mistakes in it:
http://www.microsoft.com/en-us/download/details.aspx?id=9970
Comment by: David I. McIntosh (4-6-2012 20:54:56) deeplink to this comment
Gregory Morse (6/8/2011 2:25:19 AM) is correct: the data type WPARAM is, in effect, a LongPtr, inspite of the name (the name "WPARAM" if it is take to mean "WORD PARAMETER" would imply it isn't even a long!)
Thus, the declaration for SendMessage in 64bit should be
Public Declare PtrSafe Function SendMessageA Lib "user32"(ByVal hWnd As LongPtr, ByVal wMsg As Long, ByVal wParam As LongPtr, lParam As Any) As LongPtr
and note that is is in fact what is in the Microsoft doc. (SendMessage was, unfortunately, the worst function to pick as an example!!!)
Comment by: Jan Karel Pieterse (5-6-2012 08:44:26) deeplink to this comment
Hi David,
Thanks for looking through the article and spotting the errors (it is easy to miss an item to convert from Long to LongPtr -or not-), much appreciated!
Comment by: Yves BENOIT (14-6-2012 01:54:53) deeplink to this comment
Hello
is there a way to play sounds in 64 bits VBA, now that the 32 bits calls to Playsound and others are obsolete?
Thank you
Yves
Comment by: Jan Karel Pieterse (14-6-2012 08:19:07) deeplink to this comment
Hi Yves,
Does it work if you declare the function like this:
Lib "winmm.dll" _
Alias "sndPlaySoundA" ( _
ByVal lpszSoundName As String, _
ByVal uFlags As Long) As Long
Comment by: Banana (28-6-2012 00:14:22) deeplink to this comment
FWIW - there is a wiki that tries to list some common APIs in VBA7 syntax:
http://www.utteraccess.com/wiki/index.php/Category:API
Comment by: Jan Karel Pieterse (28-6-2012 11:08:22) deeplink to this comment
Hi banana,
Thanks!
Comment by: Peter van Loosbroek (28-6-2012 14:40:42) deeplink to this comment
Dear Jan Karel,
I used the GetOpenFileName methode in 32 bit verions of MS Office. Working well. Now I converted to 64 bit and use it in MS Office 64 bit, but the dialog box won't open. I don't know what I am doing wrong. I've rearched the web but can't solve the problem. Please can you help me? I would be very greatfull!
Thanks, Peter (Netherlands)
Public Declare PtrSafe Function GetOpenFileName Lib "comdlg32.dll" Alias _
"GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long
Public Type OPENFILENAME
lStructSize As Long
hwndOwner As LongPtr '
hInstance As LongPtr '
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As LongPtr '
lpTemplateName As String
End Type
Public Function TestGetFile(strTitle As String) As String
Dim OpenFile As OPENFILENAME
Dim lReturn As LongPtr
Dim sFilter As String
OpenFile.lStructSize = Len(OpenFile)
OpenFile.hwndOwner = 0
OpenFile.lpstrFile = String(257, 0)
OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
OpenFile.lpstrFileTitle = OpenFile.lpstrFile
OpenFile.nMaxFileTitle = OpenFile.nMaxFile
OpenFile.lpstrInitialDir = "C:\"
OpenFile.lpstrTitle = strTitle
OpenFile.flags = 0
lReturn = GetOpenFileName(OpenFile)
If lReturn = 0 Then
TestGetFile = ""
Else
TestGetFile = Trim(Left(OpenFile.lpstrFile, InStr(1, OpenFile.lpstrFile, vbNullChar) - 1))
End If
End Function
Comment by: Jan Karel Pieterse (29-6-2012 11:39:35) deeplink to this comment
Hi Peter,
Just curious: why are you using the API function to start with, there's an Application.GetOpenFileName available too?
Comment by: Peter van Loosbroek (29-6-2012 12:02:47) deeplink to this comment
Dear Karel Jan,
The code I used in a standard module in MS Access. In a 32 bit version it works well (of course without using PtrSafe and LongPtr). The function TestGetFile returns the filename. You can test is in the debug window.
I also learned that the VB function Len (in TestGetFile) should be LenB in a 64 bit version.
I hope you can help me (and more people because everyon can use this function).
Regards, Peter, The Netherlands
Comment by: Jan Karel Pieterse (29-6-2012 14:47:51) deeplink to this comment
Hi Peter,
Odd that it does not work. I have no idea why though. Perhaps looking through the MSDN documentation fo the function gives you pointers?
http://msdn.microsoft.com/en-us/library/windows/desktop/ms646927(v=vs.85).aspx
Comment by: Jon Peltier (3-7-2012 18:28:57) deeplink to this comment
VBA7 only says whether you're running in Office 2010. To tell whether you're running in 64 bit you need
#If VBA7 And Win64 Then
Comment by: Jan Karel Pieterse (3-7-2012 21:47:56) deeplink to this comment
Hi Jon,
True, but as far as I know, in almost all cases the VBA7 test suffices, unless there are specific reasons to test for bitness.
Comment by: technolust (11-7-2012 21:35:30) deeplink to this comment
Yup, usage of LenB func works for GetOpenFileName.
Comment by: Sisyphus (21-8-2012 20:21:11) deeplink to this comment
I found this site extremely helpful - as far as it goes. I the found this site which seems more complete than this site could hope to be.. It has a complete lst of 64 bit API functions.
http://www.microsoft.com/en-us/download/details.aspx?id=9970
The site acts like it were supply an executable file. However, if you acceded to all requests, all you get are 3 very useful documents.
Comment by: MK (21-9-2012 11:11:50) deeplink to this comment
Good article, it provided much feedback with converting MS Excel files macros. Thanks!!
Comment by: Dharmendra (8-10-2012 18:54:16) deeplink to this comment
Hi,
We have Excel spredsheet with VBA code to get data from user to our system using CyberLynx approach. Here are lines that declare the said object..
Dim cyberlynxobj
Set cyberlynxobj = New CyberLynx
It is working just fine on Windows 32 or Window 64 with Office 32 bit version but we have new team using Window 64 and Office 64bit getting following error in the above line as:
"License information for this omponent not found. You do not have an appropriate license to use this functionality in the design environment"
Any suggestion/help? Thanks in advance.
Comment by: Jan Karel Pieterse (9-10-2012 10:24:50) deeplink to this comment
Hi Dharmendra,
This means that the Cyberlynx system you refer to is not a 64 bit system and cannot be called from 64 bit Excel.
You must contact the vendor and ask if they have a 64 bit COM enabled version of the system that can be called from Excel VBA.
Comment by: Gyula (5-11-2012 03:08:52) deeplink to this comment
Can I use Any in the Declare?
or do I need to use something like:
Thanks a lot!
Comment by: Jan Karel PIeterse (5-11-2012 08:44:32) deeplink to this comment
Hi Gyula,
I suggest you just try!
VBA seems to allow it, but I don't know if it'll work in the code at runtime?
Comment by: Peter Sierek (4-12-2012 05:15:03) deeplink to this comment
Hello,
maybe I will not get an answer from you. I want to query in Excel VBA, which has bit version of the computer. How does this line are for 64-bit?
Declare Function InternetGetConnectedState Lib "wininet.dll" (ByRef K As Long, ByVal Rola As Long) As Long
In the hope of getting an answer, I remain, with
friendly greetings
Peter Sierek
Comment by: Jan Karel Pieterse (4-12-2012 08:51:01) deeplink to this comment
Hi Peter,
The bitness of windows can be detected like so (taken from http://www.freevbcode.com/ShowCode.asp?ID=9043 and adjusted to cater for 64 bit Office):
Part 1, declarations:
#If VBA7 Then
Private Declare Function GetProcAddress Lib "kernel32" _
(ByVal hModule As LongPtr, _
ByVal lpProcName As String) As LongPtr
Private Declare Function GetModuleHandle Lib "kernel32" _
Alias "GetModuleHandleA" _
(ByVal lpModuleName As String) As LongPtr
Private Declare Function GetCurrentProcess Lib "kernel32" _
() As LongPtr
Private Declare Function IsWow64Process Lib "kernel32" _
(ByVal hProc As LongPtr, _
bWow64Process As Boolean) As Long
#Else
Private Declare Function GetProcAddress Lib "kernel32" _
(ByVal hModule As Long, _
ByVal lpProcName As String) As Long
Private Declare Function GetModuleHandle Lib "kernel32" _
Alias "GetModuleHandleA" _
(ByVal lpModuleName As String) As Long
Private Declare Function GetCurrentProcess Lib "kernel32" _
() As Long
Private Declare Function IsWow64Process Lib "kernel32" _
(ByVal hProc As Long, _
bWow64Process As Boolean) As Long
#End If
Comment by: Jan Karel Pieterse (4-12-2012 08:51:29) deeplink to this comment
Hi Peter,
Part 2, Function:
Dim handle As Long, bolFunc As Boolean
' Assume initially that this is not a Wow64 process
bolFunc = False
' Now check to see if IsWow64Process function exists
handle = GetProcAddress(GetModuleHandle("kernel32"), _
"IsWow64Process")
If handle > 0 Then ' IsWow64Process function exists
' Now use the function to determine if
' we are running under Wow64
IsWow64Process GetCurrentProcess(), bolFunc
End If
Is64bit = bolFunc
End Function
Comment by: dave (29-12-2012 06:58:35) deeplink to this comment
i have a workbook that i built with various formulas i want to keep protected. I want to use a web extension i found to be able to issue licenses for those i wish to approve of to use the excel file i built.
below is the link to the webpage extension i want to use that requires my excel file to request the appropriate license key through an API from each end user's computer to prevent unauthorized distribution of the file. how do i do this and how do i connect to this api??????
http://wcdocs.woothemes.com/user-guide/extensions/software-add-on/
thanks for your help!
PS. please keep in mind that my file has no macros and what i am trying to protect is the formulas i have built and the data interface i have designed to collect and analyze the data each user would be generating. i built a 40mb file with nothing but formulas and tables, etc....
Comment by: Jan Karel Pieterse (29-12-2012 16:51:33) deeplink to this comment
Hi Dave,
"Connecting" to this API will require VBA programming.
Unfortunately, the content of Excel workbooks is poorly protected. Anyone can get quick access to your model by running just one simple macro that removes all passwords.
The only way might be to create an on-line version of your model, but that likely requires:
1. An Office 365 subscription (not sure if that suffices)
2. A (possibly drastic) redesign of your model to make it work on-line using the Excel web app.
Comment by: Patrick Headley (31-1-2013 19:39:40) deeplink to this comment
Maybe the conditional compiler directives work in other versions of Access or in Excel but I have a Windows 7 64 bit installation with Office 2010 64 bit installed. Even when using the compiler directives the code will not compile or run. The message always appears stating that the declares need to be updated and PrtSafe needs to be added. It looks to me like the only solution is to creaet two different versions of the application. Please refer to the post by Jan Karel Pieterse (12/4/2012 8:51:01 AM) for sample code.
Comment by: Jan Karel Pieterse (1-2-2013 15:08:15) deeplink to this comment
Hi Patrick,
What code does your Excel fail on?
Comment by: fabrice CONSTANS (4-2-2013 16:42:04) deeplink to this comment
Hi Jk !
I have a heterogeneous Park: windows xp, vista, 7 and 8 in 32 or 64 bit Excel 2003 to 2010 in 32 or 64 bit.
What should i use as a compile-time directive? Win64 and vba7, both?
Should I deliver for 2003 to 2007 and 2010 32/64 versions?
My code is :
#If Win64 Then
Private Type structHH_FTS_QUERY
cbStruct As Long
fUniCodeStrings As Long
pszSearchQuery As String
iProximity As Long
fStemmedSearch As Long
fTitleOnly As Long
fExecute As Long
pszWindow As String
End Type
Private Declare PtrSafe Function HtmlHelp Lib "hhctrl.ocx" Alias "HtmlHelpA" _
(ByVal hwndCaller As LongPtr, _
ByVal pszFile As String, _
ByVal uCommand As HH_COMMAND, _
dwData As Any) As LongPtr
#Else
Private Type structHH_FTS_QUERY
cbStruct As Long
fUniCodeStrings As Long
pszSearchQuery As String
iProximity As Long
fStemmedSearch As Long
fTitleOnly As Long
fExecute As Long
pszWindow As String
End Type
Private Declare Function HtmlHelp Lib "hhctrl.ocx" Alias "HtmlHelpA" _
(ByVal hwndCaller As Long, _
ByVal pszFile As String, _
ByVal uCommand As HH_COMMAND, _
dwData As Any) As Long
#End If
Public Sub Show(NewFile As String, Optional WindowPane As String, Optional ContextID)
Dim Fichier As String
Fichier = NewFile
...
#If Win64 Then
Call HtmlHelp(0, Fichier, HH_HELP_CONTEXT, ByVal CLng(ContextID))
#Else
Call HtmlHelp(0, Fichier, HH_HELP_CONTEXT, ByVal CLngLng(ContextID))
#End If
End If
End Sub
Thank you.
Fabrice CONSTANS MVP
Comment by: Jan Karel Pieterse (5-2-2013 17:37:43) deeplink to this comment
Hi Fabrice,
As far as I know, VBA7 suffices UNLESS there is a specific API which behaves different on a Win64 machine then on a Win32 machine (e.g. is in a different dll or has a difference in arguments). The ones listed here seem to work just fine with just VBA7.
Comment by: Mireille DOUGUET (11-3-2013 12:13:09) deeplink to this comment
Hi,
I am from France, and your article was very usefull for me.
But I did'nt success in declaring the function GetPrivateProfileString in my vba code (access 2010 module under Windows Server 2008)...
I couldn't declare the return value to LongPtr because it made a program crash.
When declaring it to Long, with the key PtrSafe, then I could have the return value (the number of characters read)
But I could'nt retrieve the string read...
Here is my code (not crashing, but not working...) :
Thank you for your feedback.
Best regards
M. DOUGUET
'Read
Private Declare PtrSafe Function GetPrivateProfileString Lib "kernel32" Alias "GetPrivateProfileStringA" _
(ByVal lpApplicationName As String, _
ByVal lpKeyName As String, _
ByVal lpDefault As String, _
ByVal lpReturnedString As String, _
ByVal nSize As Long, _
ByVal lpFileName As String) As Long
Function Startup()
...
Dim iResult As LongPtr
iResult = GetPrivateProfileString("ETAT", "NOM_ETAT", "", sParamNomEtat, 255, "D:\BTIA_CONF\LanceAccess.INI")
....
Comment by: Jan Karel Pieterse (11-3-2013 15:32:52) deeplink to this comment
Bonjour Mireille,
The reason is that the GetPrivateProfileString function returns a Long, not a LongPtr.
So your Dim statement must indeed declare as Long:
Comment by: Zac (12-3-2013 16:03:28) deeplink to this comment
Morning,
I had used the PtrSafe expression below yesterday with great results, but today it no longer works... I am trying to get an calculation box to work. It is labeled as Text227. Yesterday when I used the PtrSafe expression I placed it on the very top line, for example Option PtrSafe Compare Database.
Any ideas?
Thank you for your help!
Option Compare Database
Option Explicit
' Copyright Lebans Holdings 1999 Ltd.
' Color only the Active/Current TAB and
' not the background of the Tab control
' Var of type Tab class
Private TB As clsTabsCurrentPage
' Temp var for the Class
Dim blRet As Boolean
Dim lngRet As Long
Private Sub Form_Load()
DoCmd.MoveSize 0, 0, 6650, 5200
' Create an instance of our FormatByCriteria class
Set TB = New clsTabsCurrentPage
' You MUST set the CriteriaControl prop
' Set the first TAB Page as the current page
Me.TabCtl.Value = 0
TB.TabControl = Me.TabCtl
' You MUST set the BackGround control
' used to display the current pages background color
TB.BGControl = Me.RecBG
' Parent Form
TB.TabForm = Me
' Set the desired Rotation amount
' For this class it should remain at Zero as
' the class only renders the Text when it is colored.
TB.RotateDegree = 0
' Create the Tabs
' For this sample we only want the Current TAB itself colored
' not the background of the TAB control.
TB.ColorTabsOnly = True
TB.MakeTabsCurrentPage Me.TabCtl.Value
End Sub
Private Sub Form_Unload(Cancel As Integer)
Set TB = Nothing
End Sub
Private Sub Text227_BeforeUpdate(Cancel As Integer)
End Sub
Comment by: Jan Karel Pieterse (12-3-2013 16:27:47) deeplink to this comment
Hi Zac,
I see no API declarations in the code you posted?
Comment by: Mireille DOUGUET (13-3-2013 11:27:19) deeplink to this comment
Hi, and thank you for your reply for my question on 3/11/2013.
But the problem is that I can retrieve the number of bytes, ok, but I can't retrieve the string that the GetPrivateProfileString must return to me...
Perhaps the definition of the lpReturnedString parameter, or the way it is passed to the function...
Best regards
M. DOUGUET
Comment by: Jan Karel Pieterse (13-3-2013 16:32:29) deeplink to this comment
Bonjour Mireille,
According to the microsoft documentation, this is the correct 64 bit declaration:
Comment by: ChanthornLaiy (1-4-2013 18:06:13) deeplink to this comment
Thanks for develop this useful page
Comment by: Neil Rogers (18-4-2013 02:45:15) deeplink to this comment
I have found the above a very useful reference list, but (and I suppose you get a lot of "buts") I would have found your website additionally beneficial to have a list of Declarations by API Sub routines (eg my search for Private Declare Sub Sleep). I was guided to your site by google because your article description "apideclarations" did not distinguish between function and procedure.
Comment by: Jan Karel Pieterse (18-4-2013 13:27:51) deeplink to this comment
Hi Neil,
You're right of course.
I started building this list and adding Functions as I happen to have the need form them in tools that require porting to 64 bit Office.
As it happens I have not encountered the need to add Sub declarations yet.
As soon as I encounter one (or if you send me some) I'll add them.
Comment by: mic (10-5-2013 16:51:54) deeplink to this comment
I have developed code in vba 6 using office 2007 at that time I was on same platform as my users XP. I now have windows 7 with office 2010 64bit installed on my machine and my users are still on office 2007 32bit. Which compiler options do I choose to have my code backward compatiable for office 2007 users?
Comment by: Jan Karel Pieterse (10-5-2013 17:17:41) deeplink to this comment
Hi Mic,
Basically, as long as you use the method shown here, you should be fine for older Office versions.
Of course you will have to test your code on all Office versions that it will be used.
Comment by: wwwilli (15-5-2013 12:10:26) deeplink to this comment
EnumWindows?
Comment by: Jan Karel Pieterse (15-5-2013 12:44:34) deeplink to this comment
Hi wwwilly,
That is a very concise question :-). Care to elaborate a bit?
Comment by: wwwilli (15-5-2013 13:05:59) deeplink to this comment
Sorry, the question was, are there any amendments to make in
Public Declare Function EnumWindows Lib "user32" (ByVal lpEnumFunc As Long _
, ByVal lParam As Long) As Long
?
Comment by: Jan Karel Pieterse (15-5-2013 13:55:54) deeplink to this comment
Hi wwwilly,
The correct 64 bit declaration is:
Comment by: wwwilli (17-5-2013 21:17:04) deeplink to this comment
Thank you.
And bedankt for the whole list.
Comment by: Swap (24-5-2013 14:35:56) deeplink to this comment
HI,
I have written code in vba to open regisry and get handel of some application, it is working fine on XP, office2003 but on windows7(64 bit), office 2010(32bit not sure) code is not working for, RegOpenKeyEx command. Here is the code.
Please help.
Public Declare Function RegCloseKey Lib "ADVAPI32" (ByVal hKey&) As Long
Public Declare Function RegOpenKeyEx Lib "ADVAPI32" Alias "RegOpenKeyExA" (ByVal hKey&, ByVal lpSubKey$, ByVal ulOptions&, ByVal samDesired&, phkResult&) As Long
Public Declare Function RegQueryValueEx Lib "ADVAPI32" Alias "RegQueryValueExA" (ByVal hKey&, ByVal lpValueName$, ByVal lpReserved&, ByVal lpType&, ByVal lpData$, lpcbData&) As Long
Declare Function WaitForSingleObject Lib "kernel32" (ByVal hHandle As Long, ByVal dwMilliseconds As Long) As Long
Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long
Public Const HKEY_CLASSES_ROOT As Long = &H80000000
Public Const HKEY_CURRENT_USER As Long = &H80000001
Public Const HKEY_LOCAL_MACHINE As Long = &H80000002
Public Const HKEY_USERS As Long = &H80000003
Public Const ERROR_SUCCESS As Long = &H0
Global Const SYNCHRONIZE As Long = &H100000
Global Const INFINITE As Long = &HFFFFFFFF
'define
Public Const MAX_PATH As Long = 260
Public Const FILE_TYPE_XLS As Long = 0
Public Const FILE_TYPE_CSV As Long = 1
Sub check()
Ret = RegOpenKeyEx(HKEY_LOCAL_MACHINE, "SOFTWARE\XYZ_App", 0, 1, hKey)
Ret = RegQueryValueEx(hKey, "InstallPath", 0, 0, RegValue, MAX_PATH)
End sub
Comment by: Jan Karel Pieterse (24-5-2013 14:51:57) deeplink to this comment
Hi Swap,
Can you perhaps give a more complete example with variable content in place that does work on XP/2003 and returns a proper value? Otherwise this is a bit hard to test.
Comment by: Anon (12-6-2013 05:00:47) deeplink to this comment
I want to convert this to 64 bit. I can not find this on your page.
Private Declare Function SendDlgItemMessage _
Lib "user32" Alias "SendDlgItemMessageA" ( _
ByVal hDlg As Long, _
ByVal nIDDlgItem As Long, _
ByVal wMsg As Long, _
ByVal wParam As Long, _
ByVal lParam As Long) _
As Long
Comment by: Jan Karel Pieterse (12-6-2013 08:05:38) deeplink to this comment
Hi Anon,
From the download link shown at the top of this article:
http://www.microsoft.com/downloads/en/confirmation.aspx?FamilyID=035b72a5-eef9-4baf-8dbc-63fbd2dd982b&displaylang=en
Comment by: Anon (13-6-2013 05:09:23) deeplink to this comment
Thank you for that.
I tried the MS download but I get a "no result" when searching for this function in VBA help after installing?
I am now looking for URLDownloadToFile. I can not find that by searching the help either.
Comment by: Jan Karel Pieterse (13-6-2013 08:47:56) deeplink to this comment
Hi Anon,
It is not obvious, but the API declarations are in the txt file.
Comment by: Emiel Nijhuis (20-6-2013 21:31:15) deeplink to this comment
Hi Jan Karel,
When looking for 64 bit API calls to manipulate the clipboard I came across this sample:
https://korpling.german.hu-berlin.de/p/projects/excelfalkoplugin/repository/revisions/321/entry/Components/mdlApi.bas
I tested the sample and it functions without error.
It is a 'translation' of the following MSDN example:
http://msdn.microsoft.com/en-us/library/office/ff192913.aspx
Might be useful for your list,
Regards,
Emiel Nijhuis
Comment by: Jan Karel Pieterse (21-6-2013 12:01:24) deeplink to this comment
Hi Emiel,
Thanks!
Comment by: Mathys Walma (23-7-2013 01:04:30) deeplink to this comment
I installed the Win32API_PtrSafe.txt. The function below seems wrong:
Declare PtrSafe Function SetupComm Lib "kernel32" Alias "SetupComm" (ByVal hFile As Long, ByVal dwInQueue As Long, ByVal dwOutQueue As Long) As Long
hFile is LongPtr in a lot of other functions.
Comment by: George Marcovits (3-8-2013 09:20:22) deeplink to this comment
I have been using for years now with Excel 2000 and Windows XP the following two sets of codes. The first one adds a new line every 30 seconds to a spreadsheet:
ByVal hwnd As Long, _
ByVal nIDEvent As Long, _
ByVal uElapse As Long, _
ByVal lpTimerFunc As Long) As Long
Public Declare Function KillTimer Lib "user32" ( _
ByVal hwnd As Long, _
ByVal nIDEvent As Long) As Long
Public TimerID As Long
Public TimerSeconds As Single
Sub StartTimer30T()
Windows("Test.xls").Activate
Sheets("First").Select
Application.Run "Test.xls!AtTheStart"
TimerSeconds = 30
TimerID = SetTimer(0&, 0&, TimerSeconds * 1000&, AddressOf TimerProc)
End Sub
Sub EndTimer30T()
On Error Resume Next
Sheets("First").Select
Range("A1").Select
KillTimer 0&, TimerID
End Sub
I am planning to use them on a computer with Windows 7 and Office 2013 all 64 bit. Will the above codes work and if not any directions on what to change will be highly appreciated.
Thank you very much in advance,
George
Comment by: George Marcovits (3-8-2013 09:24:22) deeplink to this comment
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
Private Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long) As Long
Private Const GWL_STYLE = (-16)
Private Const WS_CAPTION = &HC00000
Private Const WS_MAXIMIZEBOX = &H10000
Private Const WS_MINIMIZEBOX = &H20000
Private Const WS_SYSMENU = &H80000
Private Declare Function SetWindowPos Lib "user32" (ByVal hwnd As Long, ByVal hWndInsertAfter As Long, ByVal x As Long, ByVal y As Long, ByVal cx As Long, ByVal cy As Long, ByVal wFlags As Long) As Long
Private Enum ESetWindowPosStyles
SWP_SHOWWINDOW = &H40
SWP_HIDEWINDOW = &H80
SWP_FRAMECHANGED = &H20
SWP_NOACTIVATE = &H10
SWP_NOCOPYBITS = &H100
SWP_NOMOVE = &H2
SWP_NOOWNERZORDER = &H200
SWP_NOREDRAW = &H8
SWP_NOREPOSITION = SWP_NOOWNERZORDER
SWP_NOSIZE = &H1
SWP_NOZORDER = &H4
SWP_DRAWFRAME = SWP_FRAMECHANGED
HWND_NOTOPMOST = -2
End Enum
Private Declare Function GetWindowRect Lib "user32" (ByVal hwnd As Long, lpRect As RECT) As Long
Private Type RECT
Left As Long
Top As Long
Right As Long
Bottom As Long
End Type
Comment by: George Marcovits (3-8-2013 09:24:53) deeplink to this comment
VB]Sub ShowTitleBar(bShow As Boolean)
Dim lStyle As Long
Dim tRect As RECT
Dim sWndTitle As String
Dim xlhnd
sWndTitle = "Microsoft Excel - " & ActiveWindow.Caption
xlhnd = FindWindow(vbNullString, sWndTitle)
GetWindowRect xlhnd, tRect
If Not bShow Then
lStyle = GetWindowLong(xlhnd, GWL_STYLE)
lStyle = lStyle And Not WS_SYSMENU
lStyle = lStyle And Not WS_MAXIMIZEBOX
lStyle = lStyle And Not WS_MINIMIZEBOX
lStyle = lStyle And Not WS_CAPTION
Else
lStyle = GetWindowLong(xlhnd, GWL_STYLE)
lStyle = lStyle Or WS_SYSMENU
lStyle = lStyle Or WS_MAXIMIZEBOX
lStyle = lStyle Or WS_MINIMIZEBOX
lStyle = lStyle Or WS_CAPTION
End If
SetWindowLong xlhnd, GWL_STYLE, lStyle
SetWindowPos xlhnd, 0, tRect.Left, tRect.Top, tRect.Right - tRect.Left, tRect.Bottom - tRect.Top, SWP_NOREPOSITION Or SWP_NOZORDER Or SWP_FRAMECHANGED
End Sub