How to use Windows API in VBA (2021 edit)

Estimated reading time: 7 minutes

Windows API embraces VBA

The Windows Application Programming Interface (Windows API) is a set of functions within 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 operating systems to VBA programmers too!

Why VBA programmers need this?

Often times a VBA programmer may not be able to find a suitable Excel or a native VBA language 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 a tutorial on microsecond timer in VBA). In such cases, a VBA program is able to invoke an Windows API by declaring the Windows API function at the top of a VBA code module.

How to declare a Windows API function

A Windows API function must be declared correctly at the top of a VBA code module. The declaration statement will tell the VBA virtual machine 3 important pieces of information.
1. The Window API function name you would like to use.
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 your VBA code and this Windows API is located in a library called “kernel32”.

Alias “GetSystemDirectoryA”: This means within the “kernel32” library, the Windows API’s actual name is  GetSystemDirectoryA.

(ByVal lpBuffer As String, ByVal nSize As Long) As Long: After calling the Windows API, the systems directory path 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 subroutine to use this Windows API:

'***************************************************
'Purpose: invoke the Windows API GetSystemDirectoryA
'***************************************************
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

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 (2021 edit)

Tip 1: Use PtrSafe for 64-bit Excel

To ensure a Windows API function call will work in 64-bit Excel, we need the keyword “PtrSafe” somewhere in the Windows API declaration. Without this, you will get an error.

Tip 2: Use LongPtr for 64-bit Excel

VBA now includes the variable type alias LongPtr. The actual data type that LongPtr resolves to depends on the version of Office that it is running in; LongPtr resolves to Long in 32-bit versions of Office. For Windows API code to work in 64-bit versions of Office, replace all Long and LongLong datatypes to LongPtr.

Tip 3: Use conditional compiler directives

If you need your WIndows API function call to be compatible in both 32-bit and 64-bit Excel, we need to declare two versions of the function declaration by using conditional compiler directives.

The following is an example to show all 3 tips in action. The codes in bold are the recommended changes for 64-bit Excel. You can read the long and complicated reference from Microsoft: 64-bit Visual Basic for Applications overview (if you want).

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

Now try an easy exercise (2021 edit)

A very useful Windows API to use in your VBA project is the Sleep Windows API. It suspends the execution of the your VBA subroutine until the time-out interval elapses. A quick search within the file “Win32API_PtrSafe.TXT” shows this Windows API:

Declare PtrSafe Sub Sleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)

So how would this Sleep Windows API be used within a VBA code module? It’s a simple 2 step process.

Step 1: Write the Windows API declaration statement at the top of a code module. You need to wrap the declaration in some preprocessor directives to handle both 32 bit Excel and 64 bit Excel environments. Don’t forget to add the extra keyword PtrSafe and replace all Long data type with LongPtr for 64-bit Excel.

#If VBA7 Then
    'for 64-bit Excel
    Declare PtrSafe Sub Sleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As LongPtr)
#Else
    'for 32-bit Excel
    Declare Sub Sleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)
#End If

Step 2: Now you can use “Sleep” just like a typical VBA function.

Sub Hello()
    Sleep (1000) 'this is a Windows API not a VBA function!
    MsgBox ("Oh, I slept for 1 sec.")
End Sub

Will Windows API in VBA work on MacOS? (2021 edit)

Window APIs in an Excel VBA application will not work on the MacOS. So to make your VBA code run safely on Windows OS and MacOS, it’s a good idea to check the operating system before executing any Windows API calls. You will need to limit yourself to pure VBA language functions or find an equivalent with AppleScript if you are working with MacOS.

#If VBA7 Then
    'for 64-bit Excel
    Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#Else
    'for 32-bit Excel
    Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If

Sub Hello()
    If DetectVBAEnvironment = "win" Then 'check for Win OS
        Sleep (1000) 'this is a Windows API for WinOS
        MsgBox ("Slept for 1000 ms")
    else
        Application.Wait Now + TimeValue("0:00:01") 'this is a VBA call for MacOS
    End If
End Sub


'Purpose: detects the OS
Function DetectVBAEnvironment()
  Dim s  As String
  s = LCase(Application.OperatingSystem)
    If InStr(s, "mac") Then
        DetectVBAEnvironment = "mac"
    Else
        DetectVBAEnvironment = "win"
    End If
End Function

Most useful Windows APIs for VBA programming (2021 edit)

There is a very long list of WIndows APIs available. This is my short & useful list of of useful Windows APIs for the typical VBA programmer:

Sleep. Suspends execution for a specified period. It places the running code into an inactive state for the number of milliseconds passed to the function.

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) Sleep milliseconds

GetUserName. If you need to know who’s logged into an Access database or Exel workbook, use GetUserName. 

Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

GetComputerName. This is similar to GetUserName except it retrieves the system’s name.

Private Declare Function GetComputerName Lib "kernel32" Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long

GetFileSize. Retrieves the size of the specified file, in bytes.

Declare PtrSafe Function GetFileSize Lib "kernel32" Alias "GetFileSize" (ByVal hFile As LongPtr, lpFileSizeHigh As Long) As Long

GetDiskFreeSpace. Retrieves information about the specified disk, including the amount of free space on the disk.

Declare PtrSafe Function GetDiskFreeSpace Lib "kernel32" Alias "GetDiskFreeSpaceA" (ByVal lpRootPathName As String, lpSectorsPerCluster As Long, lpBytesPerSector As Long, lpNumberOfFreeClusters As Long, lpTotalNumberOfClusters As Long) As Long

Aeternus Consulting is the premier training centre in Singapore for Excel Courses – Basic Excel, Advanced Excel and Excel VBA Macro courses. For more Microsoft Excel training courses, please visit our Excel training page.

Aeternus Consulting is now on Instagram! So take a look and follow us on Instagram.