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.
| Constant | 16 bit | 32 bit | 64 bit |
|---|---|---|---|
| Vba6 | False | If Vba6 | False |
| Vba7 | False | If Vba7 | True |
| Win16 | True | False | False |
| Win32 | False | True | True |
| Win64 | False | False | True |
| Mac | False | If Mac | If 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
Privatekeyword- 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
