Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Speed up your file

FastExcel
The best tool to optimise your Excel model!
Home > English site > Articles > API declarations

Declaring API functions in 64 bit Office

Introduction

With the introduction of Windows 7 and Office 2010 VBA developers face a new challenge: ensuring their applications work on both 32 bit and 64 bit platforms.

This page is meant to become the first stop for anyone who needs the proper syntax for his API declaration statement in Office VBA.

Many of the declarations were figured out by Charles Williams of www.decisionmodels.com when he created the 64 bit version of our Name Manager.

Links

Of course Microsoft documents how to do this. There is an introductory article on Microsoft MSDN:

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)

Declarations by API function

Function name Declarations (32 bit followed by 64 bit)
CreateProcess We start off with 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

     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


    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

'Full example shown below, including the necessary structures
#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
FindWindow
Private Declare Function FindWindow Lib "USER32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Private
Declare PtrSafe Function FindWindow Lib "USER32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr
FindWindowEx
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 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
GdipCreateBitmapFromFile
Private Declare Function GdipCreateBitmapFromFile Lib "GDIPlus" (ByVal filename As Long, bitmap As Long) As Long

Private Declare PtrSafe Function GdipCreateBitmapFromFile Lib "GDIPlus" (ByVal filename As LongPtr, bitmap As LongPtr) As LongPtr
GdipCreateHBITMAPFromBitmap
Private Declare Function GdipCreateHBITMAPFromBitmap Lib "GDIPlus" (ByVal bitmap As Long, hbmReturn As Long, ByVal background As Long) As Long

Private Declare PtrSafe Function GdipCreateHBITMAPFromBitmap Lib "GDIPlus" (ByVal bitmap As LongPtr, hbmReturn As LongPtr, ByVal background As Long) As LongPtr
GdipDisposeImage
Private Declare Function GdipDisposeImage Lib "GDIPlus" (ByVal image As Long) As Long

Private Declare PtrSafe Function GdipDisposeImage Lib "GDIPlus" (ByVal image As LongPtr) As LongPtr
GdiplusShutdown
Private Declare Function GdiplusShutdown Lib "GDIPlus" (ByVal token As Long) As Long

Private Declare PtrSafe Function GdiplusShutdown Lib "GDIPlus" (ByVal token As LongPtr) As LongPtr
GdiplusStartup
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
    
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
GetClassName
Public Declare Function GetClassName Lib "USER32" Alias "GetClassNameA" _
                                     (ByVal hWnd As Long, ByVal lpClassName As String, _
                                      ByVal nMaxCount As Long) As Long

Public Declare PtrSafe Function GetClassName Lib "USER32" Alias "GetClassNameA" _
                                     (ByVal hWnd As LongPtr, ByVal lpClassName As String, _
                                      ByVal nMaxCount As LongPtr) As Long
getDC
Private Declare Function GetDC Lib "USER32" (ByVal hWnd As Long) As Long

Private
Declare PtrSafe Function GetDC Lib "USER32" (ByVal hWnd As LongPtr) As LongPtr
GetDesktopWindow
Public Declare Function GetDesktopWindow Lib "USER32" () As Long

Public Declare PtrSafe Function GetDesktopWindow Lib "USER32" () As LongPtr
getDeviceCaps
Private Declare Function GetDeviceCaps Lib "gdi32" (ByVal hDC As Long, ByVal nIndex As Long) As Long

Private
Declare PtrSafe Function GetDeviceCaps Lib "gdi32" (ByVal hDC As LongPtr, ByVal nIndex As Long) As Long
GetDriveType
Private Declare Function GetDriveType Lib "kernel32" Alias _
                        "GetDriveTypeA" (ByVal sDrive As String) As Long

Private Declare PtrSafe Function GetDriveType Lib "kernel32" Alias _
                                "GetDriveTypeA" (ByVal sDrive As String) As LongPtr
GetForegroundWindow
Declare Function GetForegroundWindow Lib "user32.dll" () As Long

Declare PtrSafe Function GetForegroundWindow Lib "user32.dll" () As LongPtr
getFrequency
Declare Function getFrequency Lib "kernel32" Alias "QueryPerformanceFrequency" (cyFrequency As Currency) As Long

Private
Declare PtrSafe Function getFrequency Lib "kernel32" Alias "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
GetKeyState
Declare Function GetKeyState Lib "USER32" (ByVal vKey As Long) As Integer

Declare
PtrSafe Function GetKeyState Lib "USER32" (ByVal vKey As Long) As Integer
GetLastInputInfo
#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
    Private Declare Sub GetLastInputInfo Lib "USER32" (ByRef plii As LASTINPUTINFO)
#End If
GetOpenFileName
Option Explicit

#If VBA7 Then
    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
  
#Else

    Public Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
            "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long
      
    Public 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    //
'/////////////////////////////////


Public 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 Function GetSystemMetrics Lib "USER32" (ByVal nIndex As Long) As Long

Private
Declare PtrSafe Function GetSystemMetrics Lib "USER32" (ByVal nIndex As Long) As Long
GetTempPath
Declare Function GetTempPath Lib "kernel32" _
                             Alias "GetTempPathA" (ByVal nBufferLength As Long, _
                                                   ByVal lpbuffer As String) As Long

Declare PtrSafe Function GetTempPath Lib "kernel32" _
                             Alias "GetTempPathA" (ByVal nBufferLength As longptr, _
                                                   ByVal lpbuffer As String) As Long
getTickCount
Private Declare Function getTickCount Lib "kernel32" Alias "QueryPerformanceCounter" (cyTickCount As Currency) As Long

Private
Declare PtrSafe Function getTickCount Lib "kernel32" Alias "QueryPerformanceCounter" (cyTickCount As Currency) As Long
    '
getTime
Private Declare Function timeGetTime Lib "winmm.dll" () As Long

Private
Declare PtrSafe Function timeGetTime Lib "winmm.dll" () As Long
GetWindow
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
GetWindowLong This is one of the few API functions that requires the Win64 compile constant:
#If VBA7 Then
    #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 GetWindowLongPtr Lib "USER32" Alias "GetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long) As Long
#End If
GetWindowsDirectory
Declare Function GetWindowsDirectory& Lib "kernel32" Alias _
                                      "GetWindowsDirectoryA" (ByVal lpbuffer As String, _
                                                              ByVal
nSize As Long)

Declare PtrSafe Function GetWindowsDirectory& Lib "kernel32" Alias _
                                      "GetWindowsDirectoryA" (ByVal lpbuffer As String, _
                                                              ByVal
nSize As LongPtr)
GetWindowText
Public Declare Function GetWindowText Lib "USER32" Alias "GetWindowTextA" _
                                      (ByVal hWnd As Long, ByVal lpString As String, _
                                       ByVal cch As Long) As Long

Public Declare PtrSafe Function GetWindowText Lib "USER32" Alias "GetWindowTextA" _
                                      (ByVal hWnd As LongPtr, ByVal lpString As String, _
                                       ByVal cch As LongPtr) As Long
InternetGetConnectedState
Public Declare Function InternetGetConnectedState _
        Lib "wininet.dll" (lpdwFlags As Long, _
        ByVal dwReserved As Long) As Boolean

Public Declare PtrSafe Function InternetGetConnectedState _
        Lib "wininet.dll" (lpdwFlags As LongPtr, _
        ByVal dwReserved As long) As Boolean
IsCharAlphaNumericA
Private Declare Function IsCharAlphaNumericA Lib "USER32" (ByVal byChar As Byte) As Long

Private
Declare PtrSafe Function IsCharAlphaNumericA Lib "USER32" (ByVal byChar As Byte) As Long
OleCreatePictureIndirect
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

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
ReleaseDC
Private Declare Function ReleaseDC Lib "USER32" (ByVal hWnd As Long, ByVal hDC As Long) As Long

Private
Declare PtrSafe Function ReleaseDC Lib "USER32" (ByVal hWnd As LongPtr, ByVal hDC As LongPtr) As Long
SendMessage
Public Declare Function SendMessageA Lib "user32" (ByVal hWnd As Long, ByVal wMsg As Long, _
                                                   ByVal wParam As Long, lParam As Any) As Long
Public Declare PtrSafe Function SendMessageA Lib "user32" (ByVal hWnd As LongPtr, ByVal wMsg As Long, _
                                                           ByVal wParam As LongPtr, lParam As Any) As LongPtr
SetActiveWindow
Declare Function SetActiveWindow Lib "user32.dll" (ByVal hWnd As Long) As Long

Declare PtrSafe Function SetActiveWindow Lib "user32.dll" (ByVal hWnd As LongPtr) As LongPtr
SetCurrentDirectory
Private Declare Function SetCurrentDirectoryA Lib "kernel32" (ByVal lpPathName As String) As Long

Private
Declare PtrSafe Function SetCurrentDirectoryA Lib "kernel32" (ByVal lpPathName As String) As Long
SetWindowLongPtr This is one of the few API functions that requires the Win64 compile constant:
#If VBA7 Then
    #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 SetWindowLongPtr Lib "USER32" Alias "SetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
#End If
SHBrowseForFolder
#If VBA7 Then
    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 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

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
SHFileOperation
#If VBA7 Then
    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 Function SHGetPathFromIDList Lib "shell32.dll" Alias "SHGetPathFromIDListA" _
        (ByVal pidl As Long, ByVal pszPath As String) As Boolean

Private Declare PtrSafe Function SHGetPathFromIDList Lib "shell32.dll" Alias "SHGetPathFromIDListA" _
        (ByVal pidl As LongPtr, ByVal pszPath As String) As Boolean
SHGetSpecialFolderLocation
    Private Declare Function SHGetSpecialFolderLocation Lib _
        "shell32.dll" (ByVal hwndOwner As Long, ByVal nFolder As Long, _
        pidl As ITEMIDLIST) As Long

    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 Long
        abID As Byte
     End Type
     Private Type ITEMIDLIST
        mkid As SHITEMID
     End Type
timeGetTime
Private Declare Function timeGetTime Lib "winmm.dll" () As Long

Private
Declare PtrSafe Function timeGetTime Lib "winmm.dll" () As Long

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:

Public Declare Function SendMessageA Lib "user32" (ByVal hWnd As Long, ByVal wMsg As Long, _
                                                   ByVal wParam As Long, lParam As Any) As Long

64 bit:

Public Declare PtrSafe Function SendMessageA Lib "user32" (ByVal hWnd As LongPtr, ByVal wMsg As Long, _
                                                           ByVal wParam As Long, 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 arguments wMsg and wParam are used to pass data, so they can be Long in both 32-bit and 64-bit.

How to determine what is a memory address and what is data? You just have to read the MSDN documentation for the API functions (the C++ version) and it will tell you. Anything called a handle, pointer, brush or any other object type will require a LongPtr in 64-bit. Anything that is strictly data can stay as Long.

Conditional compiling

If your code needs to run on both 32 bit and 64 bit Excel, then another thing to do is add conditional compilation to your VBA.

Microsoft devised two compile constants to handle this:

VBA7: True if you're using Office 2010, False for older versions

WIN64: True if your Office installation is 64 bit, false for 32 bit.

Since the 64 bit declarations also work on 32 bit Office 2010, all you have to test for is VBA7:

#If VBA7 Then
    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:

#If VBA7 Then
    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!


Comments

All comments about this page:


Comment by: Alexander Wolff (1/28/2010 12:01:43 PM)

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 (1/29/2010 2:40:08 AM)

Hi Alexander,

Thanks for spotting the anomalies. Care to tell me which Long's must become LongPtr's?

 


Comment by: Ron Anderson (5/3/2010 5:13:21 PM)

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 (5/4/2010 12:36:23 AM)

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 (5/20/2010 10:35:46 AM)

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 (5/21/2010 1:37:45 AM)

Thanks Anders!!!

 


Comment by: Yuhong Bao (6/6/2010 12:23:09 AM)

"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 11:09:30 PM)

Hi Yuhong,

Thanks. Could you perhaps show the proper declarations in full?

 


Comment by: Yuhong Bao (6/6/2010 11:14:06 PM)

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 (6/7/2010 12:21:24 AM)

Hi Yuhong,

Thanks!

 


Comment by: Yuhong Bao (6/7/2010 1:15:58 PM)

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 (7/14/2010 8:43:57 AM)

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 (7/14/2010 10:58:42 AM)

Hi Clarence,

No, but you will need code like the following:

'Declare API
#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 (8/16/2010 10:53:06 AM)

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 (8/16/2010 11:16:35 AM)

Hi Villable,

Depends on the code. Could you post some of it?

 


Comment by: Villable (8/16/2010 3:38:09 PM)

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 (8/16/2010 11:12:21 PM)

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.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    'Your code goes here
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

 


Comment by: Villable (8/17/2010 1:01:53 AM)

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 (10/11/2010 7:46:38 AM)

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 (10/11/2010 7:58:19 AM)

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 (11/26/2010 4:17:06 PM)

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 (12/11/2010 12:01:34 PM)

This works fine ..thsnks

 


Comment by: Tomas (12/15/2010 11:45:56 AM)

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 (12/16/2010 1:22:18 AM)

Hi Tomas,

The type IntPtr does not exist (second declare statement in your code)

 


Comment by: Tomas (12/16/2010 12:15:34 PM)

Thanks for the help!!

 


Comment by: Satyendra (2/3/2011 8:04:12 AM)

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 (2/3/2011 11:38:52 AM)

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 (2/4/2011 1:24:48 AM)

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 (2/4/2011 3:28:51 AM)

Hi Satyendra,

I think you'll find your information here:

http://msdn.microsoft.com/en-us/library/ms741394(VS.85).aspx

 


Comment by: Satyendra (2/7/2011 10:14:42 AM)

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 AM)

Hello. I cut an pasted your code in the GetLastInputInfo from the http://www.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 11:23:49 PM)

Hi Maria,

Can you post your code and indicate what line gets highlighted when the compile error is shown?

 


Comment by: Maria (4/7/2011 9:08:45 AM)

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 (4/7/2011 4:36:54 PM)

Hey man, thanks a lot. This means money for me.

 


Comment by: Jan Karel Pieterse (4/8/2011 2:39:40 AM)

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 (4/8/2011 6:08:08 AM)

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 (4/9/2011 10:07:17 AM)

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 (4/9/2011 10:08:39 AM)

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 (5/11/2011 10:30:56 PM)

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 (5/12/2011 12:56:41 AM)

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 (6/8/2011 2:25:19 AM)

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 (6/8/2011 7:54:53 AM)

Hi Gregory,

Thanks!

Maybe you're right, I'll have to check.

 


Comment by: Karen (6/29/2011 10:24:36 AM)

Do you know how to modify CreateProcessA to work with the 64 bit operating system?

 


Comment by: Jan Karel Pieterse (6/30/2011 12:19:11 AM)

Hi Karen,

I've included the function, see top of the table on this page.

 


Comment by: Martin Orlicky (7/20/2011 8:21:45 AM)

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 (7/22/2011 7:32:49 AM)

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_ (9/30/2011 8:35:48 AM)

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 (10/2/2011 11:46:11 PM)

Hi Rx,

As long as your users are on 32 bit Office your API declarations should work unchanged.

 


Comment by: Rx_ (10/3/2011 8:02:05 AM)

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 (10/4/2011 4:32:45 AM)

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 (10/12/2011 12:52:57 PM)

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 (10/12/2011 11:48:43 PM)

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 (10/13/2011 2:39:19 AM)

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 (10/13/2011 4:29:07 AM)

Hi Ger,

Email received and replied-to, thanks.

 


Comment by: Jochen Wezel (10/20/2011 2:40:04 AM)

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 (10/24/2011 11:20:58 AM)

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_ (10/24/2011 11:39:14 AM)

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 (10/25/2011 5:48:55 PM)

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 (10/26/2011 2:59:52 AM)

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 (10/26/2011 3:01:46 AM)

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 6:56:26 PM)

This really solved my problem, thank you!

 


Comment by: @Alex (11/20/2011 10:57:47 AM)

Very fine example and excellent explanation.
@Alex

 


Comment by: enzo (11/21/2011 4:16:47 AM)

(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 (11/21/2011 4:56:46 AM)

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 (11/21/2011 8:35:22 AM)

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 (11/21/2011 10:16:53 AM)

Hi Enzo,

I still think the aCustomeColors array should remain an array of type Byte.

 


Comment by: enzo (11/21/2011 11:33:06 AM)

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 (11/21/2011 10:21:44 PM)

Can you email me a copy of your file?

 


Comment by: enzo (12/9/2011 3:49:36 AM)


' 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 (12/9/2011 5:18:43 AM)

Hi Enzo,

Thanks.

 


Comment by: Ali (2/27/2012 5:57:23 AM)

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 (3/5/2012 5:14:12 AM)

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 (3/22/2012 11:29:37 PM)

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 (3/23/2012 3:58:42 AM)

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 9:19:35 AM)

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 (5/6/2012 11:11:43 AM)

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 (5/31/2012 7:49:56 PM)

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 (6/1/2012 10:41:51 AM)

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:

http://www.jkp-ads.com/Articles/DistributeMacro08.asp

 


Comment by: Natalie (6/1/2012 2:29:21 PM)

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 (6/1/2012 2:50:14 PM)

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 (6/1/2012 3:10:40 PM)

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 (6/1/2012 7:15:14 PM)

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 (6/2/2012 5:37:35 PM)

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 (6/4/2012 4:33:21 PM)

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 (6/4/2012 6:15:37 PM)

Hi David,

Well spotted, thank you!
Page updated.

 


Comment by: David I. McIntosh (6/4/2012 7:32:37 PM)

first parameter to SECURITY_ATTRIBUTES (nLength) should be Long, not LongPtr

 


Comment by: David I. McIntosh (6/4/2012 7:35:34 PM)

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 (6/4/2012 8:54:56 PM)

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 (6/5/2012 8:44:26 AM)

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 (6/14/2012 1:54:53 AM)

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 (6/14/2012 8:19:07 AM)

Hi Yves,

Does it work if you declare the function like this:

Public Declare PtrSafe Function sndPlaySound32 _
    Lib "winmm.dll" _
    Alias "sndPlaySoundA" ( _
        ByVal lpszSoundName As String, _
        ByVal uFlags As Long) As Long

 


Comment by: Banana (6/28/2012 12:14:22 AM)

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 (6/28/2012 11:08:22 AM)

Hi banana,

Thanks!

 


Comment by: Peter van Loosbroek (6/28/2012 2:40:42 PM)

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 (6/29/2012 11:39:35 AM)

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 (6/29/2012 12:02:47 PM)

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 (6/29/2012 2:47:51 PM)

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 (7/3/2012 6:28:57 PM)

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 (7/3/2012 9:47:56 PM)

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 (7/11/2012 9:35:30 PM)

Yup, usage of LenB func works for GetOpenFileName.

 


Comment by: Sisyphus (8/21/2012 8:21:11 PM)

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 (9/21/2012 11:11:50 AM)

Good article, it provided much feedback with converting MS Excel files macros. Thanks!!

 


Comment by: Dharmendra (10/8/2012 6:54:16 PM)

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 (10/9/2012 10:24:50 AM)

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 (11/5/2012 3:08:52 AM)

Can I use Any in the Declare?

Private Declare PtrSafe Function lstrlenW Lib "kernel32" (lpString As Any) As Long


or do I need to use something like:

Private Declare PtrSafe Function lstrlenW Lib "kernel32" (lpString As LongPtr) As Long


Thanks a lot!

 


Comment by: Jan Karel PIeterse (11/5/2012 8:44:32 AM)

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 (12/4/2012 5:15:03 AM)

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 (12/4/2012 8:51:01 AM)

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:

Option Explicit

#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 (12/4/2012 8:51:29 AM)

Hi Peter,

Part 2, Function:

Public Function Is64bit() As Boolean
    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 (12/29/2012 6:58:35 AM)

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 (12/29/2012 4:51:33 PM)

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 (1/31/2013 7:39:40 PM)

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 (2/1/2013 3:08:15 PM)

Hi Patrick,

What code does your Excel fail on?

 


Comment by: fabrice CONSTANS (2/4/2013 4:42:04 PM)

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 (2/5/2013 5:37:43 PM)

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 (3/11/2013 12:13:09 PM)

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 (3/11/2013 3:32:52 PM)

Bonjour Mireille,

The reason is that the GetPrivateProfileString function returns a Long, not a LongPtr.

So your Dim statement must indeed declare as Long:

Dim iResult As Long

 


Comment by: Zac (3/12/2013 4:03:28 PM)

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 (3/12/2013 4:27:47 PM)

Hi Zac,

I see no API declarations in the code you posted?

 


Comment by: Mireille DOUGUET (3/13/2013 11:27:19 AM)

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 (3/13/2013 4:32:29 PM)

Bonjour Mireille,

According to the microsoft documentation, this is the correct 64 bit declaration:

Declare PtrSafe Function GetPrivateProfileString Lib "kernel32" Alias "GetPrivateProfileStringA" (ByVal lpApplicationName As String, ByVal lpKeyName As Any, ByVal lpDefault As String, ByVal lpReturnedString As String, ByVal nSize As Long, ByVal lpFileName As String) As Long

 


Comment by: ChanthornLaiy (4/1/2013 6:06:13 PM)

Thanks for develop this useful page

 


Comment by: Neil Rogers (4/18/2013 2:45:15 AM)

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 (4/18/2013 1:27:51 PM)

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 (5/10/2013 4:51:54 PM)

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 (5/10/2013 5:17:41 PM)

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 (5/15/2013 12:10:26 PM)

EnumWindows?

 


Comment by: Jan Karel Pieterse (5/15/2013 12:44:34 PM)

Hi wwwilly,

That is a very concise question :-). Care to elaborate a bit?

 


Comment by: wwwilli (5/15/2013 1:05:59 PM)

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 (5/15/2013 1:55:54 PM)

Hi wwwilly,

The correct 64 bit declaration is:

Declare PtrSafe Function EnumWindows Lib "user32" (ByVal lpEnumFunc As LongPtr, ByVal lParam As LongPtr) As Long

 


Comment by: wwwilli (5/17/2013 9:17:04 PM)

Thank you.
And bedankt for the whole list.

 


Comment by: Swap (5/24/2013 2:35:56 PM)

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 (5/24/2013 2:51:57 PM)

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 (6/12/2013 5:00:47 AM)

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 (6/12/2013 8:05:38 AM)

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


Declare PtrSafe 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: Anon (6/13/2013 5:09:23 AM)

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 (6/13/2013 8:47:56 AM)

Hi Anon,

It is not obvious, but the API declarations are in the txt file.

 


Comment by: Emiel Nijhuis (6/20/2013 9:31:15 PM)

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 (6/21/2013 12:01:24 PM)

Hi Emiel,

Thanks!

 


Comment by: Mathys Walma (7/23/2013 1:04:30 AM)

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 (8/3/2013 9:20:22 AM)

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:

Public Declare Function SetTimer Lib "user32" ( _
                                 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 (8/3/2013 9:24:22 AM)

Option Explicit
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 (8/3/2013 9:24:53 AM)

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


Sorry for the truncations into 3 messages

George

 


Comment by: Jan Karel Pieterse (8/8/2013 9:03:19 PM)

Hi George,

Thanks!!

 


Comment by: Jan Karel Pieterse (8/8/2013 9:04:11 PM)

Hi George,

The best way is to find womeone with 64 bit Excel to test this.

 


Comment by: Daniel Isoje (12/29/2013 8:33:10 PM)

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 (3/19/2014 4:54:16 PM)

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 (7/30/2014 10:44:41 AM)

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 (9/1/2014 1:37:24 PM)

Hi,
do you have a "64bit translation" for following APIs for reading an writing INI-files:

Private Declare Function GetPrivateProfileString Lib "kernel32" Alias "GetPrivateProfileStringA" (ByVal lpApplicationName As String, lpKeyName As Any, ByVal lpDefault As String, ByVal lpReturnedString As String, ByVal nSize As Integer, ByVal lpFileName As String) As Integer

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 (9/1/2014 2:29:45 PM)

Hi Frank,

Sure:

Declare PtrSafe Function GetPrivateProfileString Lib "kernel32" Alias "GetPrivateProfileStringA" (ByVal lpApplicationName As String, ByVal lpKeyName As Any, ByVal lpDefault As String, ByVal lpReturnedString As String, ByVal nSize As Long, ByVal lpFileName As String) As Long

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 (9/29/2014 12:32:47 PM)

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 (9/29/2014 1:09:12 PM)

Hi Alexander,

Thanks for pointing that out!
I have edited the page accordingly.

 


Comment by: LHEN (10/20/2014 10:20:12 AM)

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 (10/20/2014 10:26:56 AM)

Hi LHEN,

I'm sorry, but I don't.
Best to search MSDN for the functions and try to translate them yourself.

 


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.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].