VBA API Calls API declaration and usage


Example

Declaring a DLL procedure to work with different VBA versions:

Option Explicit

#If Win64 Then

    Private Declare PtrSafe Sub xLib "Kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)

#ElseIf Win32 Then

    Private Declare Sub apiSleep Lib "Kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)

#End If

The above declaration tells VBA how to call the function "Sleep" defined in file Kernel32.dll

Win64 and Win32 are predefined constants used for conditional compilation


Pre-defined Constants

Some compilation constants are already pre-defined. Which ones exist will depend on the bitness of the office version you're running VBA in. Note that Vba7 was introduced alongside Office 2010 to support 64 bit versions of Office.

Constant16 bit32 bit64 bit
Vba6FalseIf Vba6False
Vba7FalseIf Vba7True
Win16TrueFalseFalse
Win32FalseTrueTrue
Win64FalseFalseTrue
MacFalseIf MacIf Mac

These constants refer to the Office version, not the Windows version. For example Win32 = TRUE in 32-bit Office, even if the OS is a 64-bit version of Windows.

The main difference when declaring APIs is between 32 bit and 64 bit Office versions which introduced new parameter types (see Remarks section for more details)


Notes:

  • Declarations are placed at the top of the module, and outside any Subs or Functions
  • Procedures declared in standard modules are public by default
  • To declare a procedure private to a module precede the declaration with the Private keyword
  • DLL procedures declared in any other type of module are private to that module

Simple example for the Sleep API call:

Public Sub TestPause()

    Dim start As Double

    start = Timer

    Sleep 9000      'Pause execution for 9 seconds

    Debug.Print "Paused for " & Format(Timer - start, "#,###.000") & " seconds"

    'Immediate window result: Paused for 9.000 seconds

End Sub

It is recommended to create a dedicated API module to provide easy access to the system functions from VBA wrappers -- normal VBA Subs or Functions that encapsulate the details needed for the actual system call such as parameters used in libraries, and initialization of those parameters

The module can contain all declarations and dependencies:

  • Method signatures and required data structures
  • Wrappers that perform input validation, and ensure all parameters are passed as expected

To declare a DLL procedure, add a Declare statement to the Declarations section of the code window.

If the procedure returns a value, declare it as a Function:

Declare Function publicname Lib "libname" [Alias "alias"] [([[ByVal] variable [As type] [,[ByVal] variable [As type]]...])] As Type

If a procedure does not return a value, declare it as a Sub:

Declare Sub publicname Lib "libname" [Alias "alias"] [([[ByVal] variable [As type] [,[ByVal] variable [As type]]...])]

  • !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Also of note is that most invalid calls to the API's will crash Excel, and possibly corrupt data files

  • !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Office 2011 for Mac

Private Declare Function system Lib "libc.dylib" (ByVal command As String) As Long

Sub RunSafari()
    Dim result As Long
    result = system("open -a Safari --args http://www.google.com")
    Debug.Print Str(result)
End Sub

The examples bellow (Windows API - Dedicated Module (1 and 2)) show an API module that includes common declarations for Win64 and Win32