Excel VBA: Late Binding or Early Binding?

When using external function libraries in VBA, you need to choose between late biding and early biding * What is it, why chose one over the other and how is it done properly?

When the offered VBA functionality is not enough

With VBA you can flexibly write just the code you need for your program. It is a flexible, functional programming language that is also aware of the Excel Object Model. You can easily access and manipulate almost all of the Excel objects: Worksheets, Cells, Charts, Tables, Shapes, Printing settings and much more.

However, there are objects and tasks that are not readily accessible in Excel VBA, or that it would require quite an effort to code in VBA. Here are some activities you may want to do with Excel VBA:

• Convert prices using a currencies exchange service from the cloud (Internet)
• Parse an XML file you received from another system
• Connect to an external Database
• Read a text file encoded with Unicode characters (containing non-Latin characters)

Reuse, Reuse, Reuse

The above tasks (and many more) do not have out-of-the-box services in Excel VBA. Some can’t even be written by yourself using pure VBA built-in functions and objects. The trick here is to use external functions that someone else have already written. This concept is fundamental in computer programming, in almost any language: reuse.

In your own program, the code design should also extensively leverage reuse – of your own code. When you enclose a task within a function (or sub), you are creating a consumption service that can be re-used by any other function / sub. Similarly, it is highly common to include existing code encapsulated in an external file – to be available for you in your own program.

The benefits of re-using code are valuable: well tested and qualified code, less code lines, structured programs, easy to read and understand, time saving, extensibility.

Most of these external functions’ libraries are written by Microsoft and are part of Windows. These libraries are used by many 3^rd-parties software products and by Microsoft itself in its many products. To illustrate this concept, consider the common task of asking the user for a file. This involves opening the File Dialog box allowing the user to navigate his folders and select a file. Imagine if every application that requires the user to select a file had to develop this service on its own. Microsoft provides such a service encapsulated as a Functions’ Library (DLL file) that all Windows applications can use.

Late vs. Early Binding

In order for your program to access and use such external files (of Functions Library files), you need to bind an external file of choice to your program. This binding is the event by which your program includes this file’s declarations, objects, properties, methods and events to seamlessly be part of your own program. Following the binding action all of these elements are accessible by your program.

In general, the binding action can happen in two distinct timings:

1. Early in design Time: when you code your program.
2. Later during runtime: when your program is executed.

The below table summarizes the main benefits and drawbacks of each binding timing.

Either way, the targeted file needs to be available on the machine running your program.

How to Bind in Excel VBA

Early Binding

To bind in design time, in the VBA Editor:

• Select Tools -> References in the menu.
• Tick the checkbox next to the listed libraries (or browse for a 3^rd-party library file not offered by Windows here in the list).

As soon as you have included a reference to the library file, you can enjoy its benefits, such as creating new objects instances, selecting properties and methods of objects with the help of Intellisense and availability of enumerators.

Here’s an example of instantiating a connection object to an external Database, after having set a reference to the Microsoft library that includes the ADODB functions. The library selected as a reference here is Microsoft ActiveX Dataobjects 6.0 Library, encapsulated in the MSADO60.TLB file.

Public ConnDB As ADODB.Connection

Sub ConnectDB()

    If (ConnDB Is Nothing) Then

        Set ConnDB = New ADODB.Connection

    End If

End Sub

After calling the ConnectDB sub, you can use the public variable: ConnDB to access all of the ADODB objects, properties, methods and events, just like you use any Excel object in my program that is readily available for you.

Late Binding

To trigger a late binding during runtime, you call the CreateObject VBA function.

In the following example, we set a local variable to point to a newly created instance of the File System Object, which is part of the Microsoft Scripting Run-time library file (Scrrun.dll).

Dim fso As Object

Set fso = CreateObject("Scripting.FileSystemObject")

At this point, all of the FileSystemObject namespace within the Scripting library, on its properties, methods and events, is available through the fso variable. For example, I can call the CopyFile method to copy a file:

fso.CopyFile

Complete examples and detailed explanations are given in my online course: Computer Programming  with Excel VBA.