[VBA] Introduction to Windows API: Mastering the Declare Statement

Introduction

VBA is a very high-performance language, but sometimes you need to perform operations that go beyond the standard features of Excel. For example, you might want to play a sound from the PC speakers or get information about a specific window. To achieve this, you need to operate at a level closer to the OS.

You can do this by calling the Windows API (Application Programming Interface) from VBA. The Windows API is a vast collection of functions provided by the Windows OS that allows external programs (like VBA) to use OS features.

The Declare statement serves as the “bridge” to make these API functions available in VBA. In this article, I will explain the basic syntax of the Declare statement and how to use it to call a simple API function.

Basic Syntax and Sample Code

In this example, we will use the Beep API function contained in kernel32.dll to play a beep sound from the PC’s internal speaker.

The Declare statement must be written at the very top of the module (before any procedures like Sub).

Complete Code

'--- Declare the API function at the top of the module ---
' Declare statement compatible with both 64-bit and 32-bit
#If VBA7 Then
    ' Declare the Beep function to be used as "SystemBeep" in VBA
    Declare PtrSafe Function SystemBeep Lib "kernel32" ( _
        ByVal dwFreq As Long, _
        ByVal dwDuration As Long _
    ) As Boolean
#Else
    Declare Function SystemBeep Lib "kernel32" ( _
        ByVal dwFreq As Long, _
        ByVal dwDuration As Long _
    ) As Boolean
#End If


' Macro to call the declared API function
Sub PlayBeepSound()
    ' Play 523Hz (Note C) for 300 milliseconds
    Call SystemBeep(523, 300)
End Sub

Explanation of the Declare Statement

Let’s break down this line: Declare PtrSafe Function SystemBeep Lib "kernel32" (ByVal dwFreq As Long, ByVal dwDuration As Long) As Boolean

• Declare: Indicates that you are declaring an external function.
• PtrSafe: A keyword indicating that this declaration is safe for 64-bit Office environments. By combining it with the #If VBA7 Then ... conditional compilation, you maintain compatibility with older 32-bit Excel versions. Think of this as a mandatory requirement for modern Excel VBA.
• Function: Use Function if the API returns a value, or Sub if it does not.
• SystemBeep: This is the name you will use to call this API function within your VBA code. You can choose a name different from the original function name (Beep).
• Lib "kernel32": Specifies which library file (DLL) contains the function. kernel32.dll is one of the most basic libraries, containing core Windows features like memory management and process control.
• ( ... ): Defines the arguments received by the API function.
  • ByVal: Indicates passing the argument “by value.” API arguments use ByVal in most cases.
  • dwFreq As Long: Defines the argument name and its data type. You must check the API documentation (such as MSDN) to specify the correct data type.
• As Boolean: Specifies the data type of the value returned by the API function.

Summary

In this article, I explained the basics of the Declare statement for calling Windows API from VBA.

• The Declare statement is a declaration to make VBA recognize external functions.
• PtrSafe is essential for compatibility with 64-bit Office.
• Use Lib to specify the DLL file containing the function.
• It is very important to accurately define the data types for arguments and return values.

Mastering the Windows API dramatically expands what you can achieve with VBA. It may seem difficult at first, but please start by calling simple functions to experience its power.