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:
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 3rd-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.
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:
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.
To bind in design time, in the VBA Editor:
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
If (ConnDB Is Nothing) Then
Set ConnDB = New ADODB.Connection
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.
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:
Complete examples and detailed explanations are given in my online course: Computer Programming and Databases with Excel VBA and SQL.
The in-depth course, Beyond Excel Boundaries with Databases & SQL, is now open.If you are serious about your career, or about understanding Databases, SQL and how to develop Excel VBA business-grade programs with Databases - this course is definitely for you.