How to use Windows API in VBA

The Windows Application Programming Interface (Windows API) is a set of functions from the Microsoft Windows operating system that are available to Windows programmers.

Windows API embraces VBA

The Windows Application Programming Interface (Windows API) is a set of functions from the Microsoft Windows operating system that are available to Windows programmers. Fortunately, the Windows API has always exposed a large part of the underlying structure of the Windows systems to VBA programmers.

Why VBA Programmers Need Windows API

Often times a VBA programmer may not be able to find a suitable Excel or VBA function to perform a task but is available within the Windows APIs. For example, VBA does not have a function to measure time in milliseconds but it is available in Windows API (I will write another tutorial on this). In such cases, a VBA program is able to access Windows APIs by declaring the Windows API function at the top of a code module.

How to declare a Windows API in VBA

A Windows API function must be declared correctly at the top of a VBA code module. The declaration statement will tell VBA 3 important pieces of information.
1. The Window API function name you’re using.
2. The Library location of the the Windows API function.
3. The Window API function’s arguments.

Here is an example:

Declare PtrSafe Function GetSystemDirectory Lib "kernel32" _
Alias "GetSystemDirectoryA" (ByVal lpBuffer As String, ByVal nSize As Long) As Long

Let’s try to break this down into plain English.
Declare: A Windows API declaration always start with the Declare keyword.


PtrSafe: By adding PtrSafe after the Declare keyword, it makes this API work with 64-bit Excel. If you are writing VBA code in 32-bit Excel, this keyword should not be used.


Function GetSystemDirectory Lib “kernel32”: This tells VBA that GetSystemDirectory is the name of Windows API that will be used in the VBA code and the API is located in a library called “kernel32”.


Alias “GetSystemDirectoryA”: As a VBA programmer, you can ignore this. What this means is within the kernel32 library, the Windows API actual name is  GetSystemDirectoryA.


(ByVal lpBuffer As String, ByVal nSize As Long) As Long: After calling the function, the systems directory is contained in lpBuffer, and the length of the directory string is contained in nSize. The Windows API only returns a number indicating the length of the directory string.

Below is a VBA Sub to use this Windows API:

'Purpose: invoke the Windows API: GetSystemDirectory
Sub SystemsDir()
    Dim strSysPath As String * 255 'declare a fixed length string of 255 characters
    MsgBox Left(strSysPath, GetSystemDirectory(strSysPath, 255))
End Sub

Practical Windows API Tips for the VBA Programmer

As you can see, it is essential to get the Windows API declaration exactly correct. There are many resource in the web that lists down the declarations for Windows API calls. The VBA programmer only needs to copy the declarations and use the functions without understanding the details. As a useful resource you can obtain a list of Windows API declarations for VBA by searching for this file “Win32API_PtrSafe.TXT”. Or you can download it here (don’t worry it’s a plain text file).

How to Deal with 32-bit Excel and 64-bit Excel

To ensure that a Windows API function call to work in 64-bit Excel, we need the keyword “ptrSafe” somewhere in the API declaration. To ensure that an API function in both 32-bit and 64-bit Excel, we need to declare two versions of the function declaration by using conditional compiler directives.

#If VBA7 And Win64 Then
    'for 64-bit Excel
    Declare PtrSafe Function GetSystemDirectory Lib "kernel32" _
    Alias "GetSystemDirectoryA" (ByVal lpBuffer As String, ByVal nSize As Long) As Long
#Else
    'for 32-bit Excel
    Declare Function GetSystemDirectory Lib "kernel32" _
    Alias "GetSystemDirectoryA" (ByVal lpBuffer As String, ByVal nSize As Long) As Long
#End If

Leave a Reply

Your email address will not be published. Required fields are marked *

Contact Us

Drop us a message even when it’s late because the best ideas can come at night.

About Aeternus Singapore

aeternus consulting singapore logo
Aeternus Consulting is the premier consultancy firm for analyst-grade Microsoft Office training courses in Singapore. ACRA: T14LL0891K

Microsoft Office training solutions

corporate training course chair
We work with businesses and individuals who need effective Microsoft Office training solutions.

Analyst-Grade Training Courses

analytics with microsoft excel chart
Premium Training for Business Analysts, Data Analysts & Information Specialists in Singapore.

Our Services Include

Our Microsoft Office-centric services include:
* Full range of Microsoft Excel Training Courses in Singapore.
* Microsoft Office PowerPoint Training Courses.
* Consultancy in Excel, Access, VBA Application Development & PowerPoint Presentation Design Projects.
* Personal coaching & corporate bespoke Microsoft Office training sessions for employees.

Training Course List

Excel Basic, Intermediate, Advanced Levels
Excel PivotTables Inside Out
Data Management & Data Analysis in Excel
VBA Programming with Excel
Advanced VBA Programming with Excel
Essential Microsoft Excel for HR Professionals
Up to Speed with Microsoft PowerPoint
PowerPoint Design & Animation Ideas for Business
Microsoft SharePoint Training Workshop

Instagram (@aeternusconsulting)