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.

Most of the declarations placed here when I first wrote the article were figured out by Charles Williams of www.decisionmodels.com when he created the 64 bit version of our Name Manager (to be published soon).

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

Declarations by API function

Function name	Declarations (32 bit followed by 64 bit)
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
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
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
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
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
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
GetWindowLongptr	Private Declare Function GetWindowLongptr Lib "USER32" Alias "GetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long) As Long Private Declare PtrSafe Function GetWindowLongptr Lib "USER32" Alias "GetWindowLongA" (ByVal hWnd As LongPtr, ByVal nIndex As Long) As LongPtr
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
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
SetWindowLongPtr	Private Declare Function SetWindowLongPtr Lib "USER32" Alias "SetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long Private Declare PtrSafe Function SetWindowLongPtr Lib "USER32" Alias "SetWindowLongA" (ByVal hWnd As LongPtr, ByVal nIndex As Long, ByVal dwNewLong As LongPtr) As LongPtr
timeGetTime	Private Declare Function timeGetTime Lib "winmm.dll" () As Long Private Declare PtrSafe Function timeGetTime Lib "winmm.dll" () As Long

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

Showing last 8 comments of 18 in total (Show All Comments):

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.

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.