ActiveX vs. Standard DLLs

Most libraries that you call from VBA are COM libraries that require you to work with objects.

However, some libraries allow you to skip the object handling part entirely.  What are some examples of these two types of libraries?  And why might you choose one over the other?

Let's dive in.

COM/ActiveX DLLs (Libraries)

If you want to use some function or subroutine of a COM library, you need at least two lines of code:

1. One to create the object via CreateObject or the New keyword; and
2. Another to call the required function or subroutine as a method of the object

For example, here's how you use the FileSystemObject to check if a file exists:

Dim oFSO As Object
Set oFSO = CreateObject("Scripting.FileSystemObject")
Debug.Print oFSO.FileExists(FullPathOfFileToCheck)

Standard Dynamic Link Libraries (DLLs)

With standard (non-COM) libraries, you call functions and routines directly–that is, without having to first create an object instance.  You do, however, have to create a Declare statement in VBA for each function you want to call.

The most common example of this in VBA is calling into the Windows API.  

For example, here's how you call the Windows Sleep routine to pause some code:

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

Sub MyRoutine()
    Sleep 5000   'Pause code for five seconds
End Sub

You still need two lines of code, but one of those is a declaration line that goes in the module header.  Unlike the object creation line associated with COM libraries, the declaration line for a standard DLL routine never executes.

COM DLLs vs. Standard DLLs

In VBA terms, you can think of it as the difference between standard modules and class modules.  

Like standard modules, the functions and routines in standard DLLs can be called directly.

Like class modules, the functions and routines in a COM (ActiveX) DLL cannot be called directly; you must first create an object instance and then call the methods in the context of that object instance.

A Great Summary from twinBASIC Creator, Wayne Phillips

Over on the twinBASIC Discord server, Wayne Phillips offered the following succinct summary on 2022-04-28 (lightly edited for clarity):

ActiveX = typelibrary + registry.  

Standard DLL = manual declare statements.  

Note that with the Standard DLL option you can easily avoid hardcoding paths to the DLL in your Declare statements by calling the LoadLibrary() API manually before calling any of the exported DLL functions, thus allowing you to configure the path to the DLL at runtime.  For example, if the DLL is distributed in the same folder as an Access ACCDB file that depends on it, you might call LoadLibrary(CurrentProject.Path & "\myLibrary.dll") in your startup routine of the Access application, and then the calls using the Declare statements will bind to the already loaded myLibrary.dll.

Deciding Which Type of Library to Use

Unless you are the one developing the library itself, you won't have any choice.  You'll use whichever type the developer chose to make available.

Deciding Which Type of Library to Build

If you are the one developing the library, then you have a decision to make.

Conceptually

From a purely theoretical perspective, you would only need to ask yourself one question:

Do I need to maintain program state?

If the answer is No, then go with a standard DLL.

If the answer is Yes, then go with a COM DLL.

Practically

Of course, we can't completely ignore a few practical considerations.

1. With a standard library, you can avoid the registry by using theLoadLibrary API to specify the location of the library at runtime (e.g., you could embed the .dll in your front-end application and then extract it on demand to the user's temp folder on an as-needed basis)
2. Standard libraries require more boilerplate code in the calling application, since every routine needs a Declare statement before it can be called
3. If you use early binding with your COM objects, your application won't work if those COM dependencies are missing from the end user's computer (this could complicate your deployment process)
4. It's cleaner to call a standard library function from an Access query since you don't need to create an object first (you just need the Declare line)

How to Actually Build a DLL

The good news is that once you decide what kind of library you need to create, twinBASIC will make it easy for you to create it–no matter which option you choose:

• ActiveX DLL
• Standard DLL

Image by Iván Tamás from Pixabay