XL: Example of Using API Calls in Multi-Platform Macros

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for Windows NT, version 5.0
• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Excel 97 for Windows

SUMMARY

This article demonstrates a method for using application programming interface (API) calls in a multiplatform macro.

MORE INFORMATION

When you use Windows API functions from a Visual Basic for Applications macro, you may need to account for the bitness (32-bit or 16-bit) of the macro environment. You will need to make the API declarations for both the 32-bit and the 16-bit versions and selectively call the appropriate library according to your current macro environment.

The recommended method for calling API functions from within Visual Basic for Applications is to encapsulate the API function in a Visual Basic for Applications function. The Visual Basic for Applications function would then be used in the main code routines.

The following example shows this methodology applied to a macro example that will activate a window, given its Class name. For additional information, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q104710
   TITLE     : INF: How to Activate an Application with Class Name (2.0)

Visual Basic Code Example

1. Type the following code into a module sheet:

   NOTE: In the following sample code, an underscore (_) is used as a line- continuation character. Remove the underscore from the end of the line for the following declare statements. Declares must be entered on a single line.

         ' 32 Bit Declares.
         Declare Function ShowWindow32 Lib "user32" Alias "ShowWindow" _
             (ByVal hwnd As Long, ByVal nCmdShow As Long) As Long
         Declare Function FindWindow32 Lib "user32" Alias "FindWindowA" _
           (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
         Declare Function SetForegroundWindow32 Lib "user32" Alias _
           "SetForegroundWindow" (ByVal hwnd As Long) As Long
   
         ' 16 Bit Declares.
         Declare Function ShowWindow Lib "User" (ByVal hwnd As Integer, _
             ByVal nCmdShow As Integer) As Integer
         Declare Function SetActiveWindow Lib "User" (ByVal hwnd As _
             Integer) As Integer
         Declare Function FindWindow Lib "User" (ByVal lpClassName As _
             Any, ByVal lpWindowName As Any) As Integer
   
          ' Test routine. Run this subroutine.
          Sub TestIt_ActivateAppClass()
          Dim strClassName As String
          Dim iRetVal As Integer
          strClassName = "opusapp"
          iRetVal = AppActivateClass(strClassName)
          If Not iRetVal Then MsgBox strClassName & " Is NOT currently " & _
             "running"
          End Sub
   
          ' VB Api encapsulation function for ShowWindow.
          Function vb_ShowWindow(ByVal hwnd As Long)
          Const SW_SHOW = 9
          If is32Bit Then
             iRetVal = SetForegroundWindow32(hwnd)
             iRetVal = ShowWindow32(hwnd, SW_SHOW)
          Else
             iRetVal = SetActiveWindow(hwnd)
             iRetVal = ShowWindow(hwnd, SW_SHOW)
          End If
          vb_ShowWindow = iRetVal
          End Function
   
          ' VB Api encapsulation function for FindWindow.
          Function vb_FindWindow(ByVal lpClassName As String) As Long
          Dim strNullString As String 'Define Null String Variable
             If is32Bit Then
                vb_FindWindow = FindWindow32(lpClassName, strNullString)
             Else
                vb_FindWindow = FindWindow(lpClassName, 0&)
             End If
          End Function
   
          Function is32Bit() As Boolean
                is32Bit = False ' Assume Failure.
             If InStr(1, Application.OperatingSystem, "32", 1) > 1 Then
                is32Bit = True
             End If
          End Function
   
          Function AppActivateClass(lpClassName As String) As Boolean
          Dim hwnd As Long               ' The application's window handle.
          Dim iRetVal As Integer         ' Temp variable.
          Dim iCmdShow As Integer        ' The ShowWindow cmdshow argument.
          ActivateAppClass = False       ' Assume Failure.
                hwnd = vb_FindWindow(lpClassName) ' Get the Window Handle for
                                                  ' the className.
             If hwnd <> 0 Then                    ' The class was found.
                iRetVal = vb_ShowWindow(hwnd)     ' Activate the Application.
                ActivateAppClass = True           ' Return True if Application
                                                  ' Running.
             End If
          End Function
   
   

2. On the Tools menu, click on Macro. In the list box, click "TestIt_ActivateAppClass" (without the quotation marks), and click Run.