Calling External Windows Functions (DLL) From Excel VBA

Windows offers tons of readily available functions that perform almost any task we need. Why not make use of it in our Excel VBA programs? * Here’s what you need to know about calling DLLs in Excel VBA

Modular Programming

As you recall from our computer programming lessons, one of the key concepts in good software design is modularity: breaking our code into functional subroutines or functions, each responsible for a specific task with a clear interface for calling it.

Once encapsulated in a self-contained function, this functionality can be re-used in many locations in your program. This improves the size, quality, maintainability and readability of your program. Also, without modular design, your program can only grow so much before you lose your arms and legs.

I’d go as much to say that a good modular design is one of the first signs I look for in judging the quality of a programmer.

Using Functions Outside of Excel

Expanding on the modularity concept, why limit ourselves to functions we coded in our VBA application? There are many functions out there already developed by others and proven robust and performant – why not break the boundaries of Excel VBA and consume them, or CALL them from our Excel VBA program?

Some of the things we would like to do can’t even be achieved without using an external function that is not part of Excel VBA or that we can write ourselves without consuming such a function.

Consider, for example, the need to control the Window properties (maybe size or position) of another application running on our Windows operating system, from Excel VBA. This Window is governed by the operating system and we have no access to it without calling a Windows function that has access to that Window and can manipulate it.

What is a DLL?

A DLL (Dynamic Link Library) is a package of functions/subroutines callable by other programs running in the Windows operating system.

Typically, a DLL file has the .dll extension, although other extensions are possible for certain types of files (e.g. .ocx for ActiveX controls).

Most of the Windows operating system functionality itself is implemented by DLLs, each calling and called by other programs. One such example would be the Comdlg32.DLL file, offering a function to open the File Open dialog box for selecting file(s) – a functionality needed by many programs running on Windows.

Almost every program that is installed on a Windows machine registers its own DLLs in the Windows registry.

Once a DLL is properly registered with Windows, its published functions can be consumed and called by any other program running on that machine, and that includes Excel VBA, of course!

Almost every program developed for running on Windows makes use of existing DLL files and contributes its own DLL files to the party.

For an elaborate discussion on DLLs, read this Microsoft article.

Calling a DLL from Excel VBA

In order to gain access to the functions/subroutines included in a DLL file, you need to first declare your intentions to do that, using the Declare statement.

If you intent to call the PlayMusic sub exposed by the (imaginary) FunActivities.DLL file, your declaration statement may look like this:

Declare Sub PlayMusic Lib “FunActivities” ()

If the PlayMusic sub expects arguments, the declaration must also include those arguments (passed ByRef by default):

Declare Sub PlayMusic Lib “FunActivities” (ByVal Duration as Long)

As with any declaration in VBA, you can precede the declaration with the Public or Private qualifiers to contol for the scope of the sub in your VBA project:

Public Declare Sub PlayMusic Lib “FunActivities” ()

Declaring a function is very similar, with the notable return type expected as with any function:

Private Declare Function CountPixels Lib “PixelsInfo” () As Long

Sometimes, a function name as exposed by the DLL may be in conflict with VBA or other variables used in your program. To circumvent this, you can specify a local name to reference that function by in your VBA program, instead of the original name as determined by the DLL developer. In this case, you will add the Alias qualifier to reference the original function name, while the declared name will be your own local flavor:

Declare Sub MyPlayMusic Lib “FunActivities” Alias “PlayMusic”()

Another way of referencing a function in the DLL, instead of by its published (or exported) name, would be by its index, or ordinal number, as defined by the developer. In this case, we must use the Alias qualifier and the “#” character to indicate an ordinal number:

Declare Sub PlayMusic Lib “FunActivities” Alias “#241”()

Referencing by ordinal number guaranties consistency even if the function name will be changed in future versions, but developers are very aware not to mess with function names in DLLs, and this way is rarely used nowadays.

For a complete and detailed explanation of the Declare statement, read this Microsoft article.

Calling a DLL Sub from Excel VBA Example

The following example is implemented in The Ultimate Excel Date Picker. If you got this neat perk already, you may be familiar with the following code, as the Date Picker comes with its VBA code open.

In developing The Ultimate Excel Date Picker, I needed to reference several DLL services, one of which is to simulate a keyboard TAB pressed.

I could have used the SendKeys() VBA function, but let me tell you – it has more promise than it delivers. I would not rely on this statement at all in any of my programs.

However, the Windows user32.DLL file offers a keyboard event sub, directly from Windows, not VBA. Here’s the declaration part I have:

#If VBA7 Then   

    Private Declare PtrSafe Sub keybd_event Lib "user32.dll" _

    (ByVal bVk As Byte, ByVal bScan As Byte, _

     ByVal dwFlags As LongPtr, ByVal dwExtraInfo As LongPtr)

#Else

    ' Code is NOT running in 32-bit or 64-bit VBA7

    Private Declare Sub keybd_event Lib "user32.dll" _

    (ByVal bVk As Byte, ByVal bScan As Byte, _

     ByVal dwFlags As Long, ByVal dwExtraInfo As Long)

#End If

And here’s the function implementing a TAB keypress. Two calls are invoked here, one for pressing the TAB key, another for releasing it:

Public Sub PressTab()

    keybd_event VB_TAB, 0, 0, 0

    keybd_event VB_TAB, 0, KEYEVENTF_KEYUP, 0

End Sub

The two Constants used are declared as usual at the top of the module:

Const VB_TAB = 9

Const KEYEVENTF_KEYUP = &H2

I know I know, you must be thinking: what’s that PtrSafe qualifier I did not talk about, and what’s the story with the “IF VBA7 clause and LongPtr… All about that in next week’s Blog post!