Excel VBA How To Make An API To Run On Both 32-bit and 64-bit

[kashif.special2005]

Hi,

I am using below API in Excel 32-bit and it was running fine until then the use has 32-bit, but now the problem is that some user is running 32-bit and some 64-bit, and below API works only on 32-bit.

'Data Processing Workflow Tool version 1.1_New API




Private Declare Function FindWindow Lib "user32.dll" Alias "FindWindowA" ( _
                                ByVal lpClassName As String, _
                                ByVal lpWindowName As String) As Long


                                
Private Declare Function GetClassName Lib "user32" Alias "GetClassNameA" ( _
                                ByVal hwnd As Long, ByVal lpClassName As String, _
                                ByVal nMaxCount As Long) As Long


                                
Private Declare Function SetForegroundWindow Lib "user32.dll" ( _
                                ByVal hwnd As Long) As Long


'Client Reporting Workflow Tool Manager API


Private Declare Function GetTimeZoneInformation Lib "kernel32" _
    (lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long


Private Declare Sub GetSystemTime Lib "kernel32" _
    (lpSystemTime As SYSTEMTIME)

How can I convert above API's, so that it ca work in both 32-bit and 64-bit, please help me on this.

Thanks
Kashif

 

[jkpieterse]

In https://jkp-ads.com/articles/apideclarations.asp I have included many examples

 

[kashif.special2005]

Hi Jan,

Thank you so much for reply, however I am very new in vba API and don't know what to change so that work correctly, kindly request to you please make necessary changes in above API so that it work in 32 bit and 64 bit.

That would be really helpful.

Thanks in advance.

Thanks
Kashif

 

[jkpieterse]

Replace your current declarations with:

#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 GetClassName Lib "USER32" Alias "GetClassNameA" _
                                     (ByVal hWnd As LongPtr, ByVal lpClassName As String, _
                                      ByVal nMaxCount As LongPtr) As Long
Private Declare PtrSafe Function SetForegroundWindow Lib "user32.dll" ( _
                                ByVal hwnd As LongPtr) As LongPtr
Private Declare PtrSafe Function GetTimeZoneInformation Lib "kernel32" Alias "GetTimeZoneInformation" (lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long
Private Declare PtrSafe Sub GetSystemTime Lib "kernel32" Alias "GetSystemTime" (lpSystemTime As SYSTEMTIME)

#Else 
Private Declare Function FindWindow Lib "USER32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function GetClassName Lib "USER32" Alias "GetClassNameA" _
                                     (ByVal hWnd As Long, ByVal lpClassName As String, _
                                      ByVal nMaxCount As Long) As Long
Private Declare Function GetTimeZoneInformation Lib "kernel32" Alias "GetTimeZoneInformation" (lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long
Private Declare Sub GetSystemTime Lib "kernel32" Alias "GetSystemTime" (lpSystemTime As SYSTEMTIME)
#End  If

Note that you will likely also have to modify other parts of your code.

 

[kashif.special2005]

Hi jkpieterse,

Thanks for reply, however when I modified the code, the code in the #Else part is showing in Red color, is there is any problem or I did something wrong.

Thanks
Kashif

 

[kashif.special2005]

Hi,

Below is the code that is using the Window API, please guide me where I need to change.

Private Declare Function FindWindow Lib "user32.dll" Alias "FindWindowA" ( _
                                ByVal lpClassName As String, _
                                ByVal lpWindowName As String) As Long


                                
Private Declare Function GetClassName Lib "user32" Alias "GetClassNameA" ( _
                                ByVal hwnd As Long, ByVal lpClassName As String, _
                                ByVal nMaxCount As Long) As Long


                                
Private Declare Function SetForegroundWindow Lib "user32.dll" ( _
                                ByVal hwnd As Long) As Long

Public Sub somename()
Dim wsh As Object
Dim vReturn As Variant
Dim client As String


On Error Resume Next
Application.DisplayAlerts = False


client = ActiveCell.Value
If client <> "" And ActiveCell.Column = 13 And client <> "Client" Then
Set wsh = CreateObject("WScript.Shell")
vReturn = Shell("C:\Windows\explorer.exe J:\Programs\Shortcuts\somename.lnk", vbNormalFocus)
Application.Wait Now + TimeValue("00:00:05")
AppActivate "somename"
WinToForegound "somename"  [B]'Here is calling a procedure where Window API functions are using[/B]
wsh.SendKeys "%"
wsh.SendKeys "{P}{DOWN}{DOWN}{DOWN}{DOWN}{DOWN}{ENTER}"
Application.Wait Now + TimeValue("00:00:03")
wsh.SendKeys "{TAB}"
wsh.SendKeys client
wsh.SendKeys "{ENTER}"
End If
Application.DisplayAlerts = True
On Error GoTo 0
End Sub

Function WinToForegound(Optional sClassname As String = vbNullString, _
                   Optional sCaption As String = vbNullString) As Long
Dim hWin As Long, ret As Long
Dim sBuff As String * 32




    hWin = FindWindow(sClassname, sCaption)
    'SetForegroundWindow hwin
    If hWin Then
        WinToForegound = SetForegroundWindow(hWin)
        'returns 1 if success
    End If


    ' for testing get the classname if not known
    If Len(sClassname) = 0 And hWin Then
        ret = GetClassName(hWin, sBuff, 32)
        If ret Then
            sClassname = Left$(sBuff, ret)
        End If
    End If


End Function

Thanks
Kashif

 

[kashif.special2005]

Hi,

Below is the code that is using the GetTimeZoneInformation API.

Private Type SYSTEMTIME
    wYear As Integer
    wMonth As Integer
    wDayOfWeek As Integer
    wDay As Integer
    wHour As Integer
    wMinute As Integer
    wSecond As Integer
    wMilliseconds As Integer
End Type


Private Type TIME_ZONE_INFORMATION
    Bias As Long
    StandardName(0 To 31) As Integer
    StandardDate As SYSTEMTIME
    StandardBias As Long
    DaylightName(0 To 31) As Integer
    DaylightDate As SYSTEMTIME
    DaylightBias As Long
End Type


Public Enum TIME_ZONE
    TIME_ZONE_ID_INVALID = 0
    TIME_ZONE_STANDARD = 1
    TIME_ZONE_DAYLIGHT = 2
End Enum
    
'''''''''''''''''''''''''''''''''''''''''''''''''''''
' Required Windows API Declares
'''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Declare Function GetTimeZoneInformation Lib "kernel32" _
    (lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long


Private Declare Sub GetSystemTime Lib "kernel32" _
    (lpSystemTime As SYSTEMTIME)








Function ConvertLocalToGMT(Optional LocalTime As Date, _
    Optional AdjustForDST As Boolean = False) As Date
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' ConvertLocalToGMT
' This converts a local time to GMT. If LocalTime is present, that local
' time is converted to GMT. If LocalTime is omitted, the current time is
' converted from local to GMT. If AdjustForDST is Fasle, no adjustments
' are made to accomodate DST. If AdjustForDST is True, and DST is
' in effect, the time is adjusted for DST by adding
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim T As Date
Dim TZI As TIME_ZONE_INFORMATION
Dim DST As TIME_ZONE
Dim GMT As Date


If LocalTime <= 0 Then
    T = Now
Else
    T = LocalTime
End If
DST = GetTimeZoneInformation(TZI)
If AdjustForDST = True Then
    GMT = T + TimeSerial(0, TZI.Bias, 0) + _
            IIf(DST = TIME_ZONE_DAYLIGHT, TimeSerial(-5, TZI.DaylightBias, 0), 0)
Else
    GMT = T + TimeSerial(-5, TZI.Bias, 0)
End If
ConvertLocalToGMT = GMT


End Function






Function GetLocalTimeFromGMT(Optional StartTime As Date) As Date
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' GetLocalTimeFromGMT
' This returns the Local Time from a GMT time. If StartDate is present and
' greater than 0, it is assumed to be the GMT from which we will calculate
' Local Time. If StartTime is 0 or omitted, it is assumed to be the GMT
' local time.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim GMT As Date
Dim TZI As TIME_ZONE_INFORMATION
Dim DST As TIME_ZONE
Dim LocalTime As Date


If StartTime <= 0 Then
    GMT = Now
Else
    GMT = StartTime
End If
DST = GetTimeZoneInformation(TZI)
LocalTime = GMT - TimeSerial(0, TZI.Bias, 0) + _
        IIf(DST = TIME_ZONE_DAYLIGHT, TimeSerial(1, 0, 0), 0)
GetLocalTimeFromGMT = LocalTime


End Function




Function LocalOffsetFromGMT(Optional AsHours As Boolean = False, _
    Optional AdjustForDST As Boolean = False) As Long
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' LocalOffsetFromGMT
' This returns the amount of time in minutes (if AsHours is omitted or
' false) or hours (if AsHours is True) that should be added to the
' local time to get GMT. If AdjustForDST is missing or false,
' the unmodified difference is returned. (e.g., Kansas City to London
' is 6 hours normally, 5 hours during DST. If AdjustForDST is False,
' the resultif 6 hours. If AdjustForDST is True, the result is 5 hours
' if DST is in effect.)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


Dim TBias As Long
Dim TZI As TIME_ZONE_INFORMATION
Dim DST As TIME_ZONE
DST = GetTimeZoneInformation(TZI)


If DST = TIME_ZONE_DAYLIGHT Then
    If AdjustForDST = True Then
        TBias = TZI.Bias + TZI.DaylightBias
    Else
        TBias = TZI.Bias
    End If
Else
    TBias = TZI.Bias
End If
If AsHours = True Then
    TBias = TBias / 60
End If


LocalOffsetFromGMT = TBias


End Function






Function DaylightTime() As TIME_ZONE
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' DaylightTime
' Returns a value indicating whether the current date is
' in Daylight Time, Standard Time, or that Windows cannot
' deterimine the time status. The result is a member or
' the TIME_ZONE enum.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim TZI As TIME_ZONE_INFORMATION
Dim DST As TIME_ZONE
DST = GetTimeZoneInformation(TZI)
DaylightTime = DST
End Function

Please where I need to change in the code so that can work.

Thanks
Kashif

 

[jkpieterse]

Red lines in the #Else part can be ignored.