Any programmer that spends time writing VBA code in any Microsoft Office application soon realizes the benefit of writing custom COM Objects (referred to as References in the Visual Basic Editor included with Office). This tutorial will show you how to create a custom COM Visible Library, consume it using VBA code, and deploy it to other computers.
It is important to note that although examples are provided in C#, the difficulties associated with creating VBA reference libraries deal more with the process than special coding techniques. Thus, the language in the example should not hinder those who primarily focus on other .NET capable languages, such as VB.NET.
If you already have a basic understanding on how to code in Visual Studio, which is assumed by this tutorial, then creating a Class Library should be trivial. In this example, we will be using a Class Library project called VBACOMPluginDemo, with a single object called CurrentTime. The full project source code is provided for download at the end of this tutorial.
Within the CurrentTime object, I created one property called CurrentDate that provides the current date in the form of a string and one method called GetCurrentTime() that does the same for the current time.
public string CurrentDate
{
get { return DateTime.Now.ToLongDateString(); }
}
public string GetCurrentTime()
{
return DateTime.Now.ToString("hh:mm:ss");
}
I've included both a property and a method in this example because you can access them both in VBA. It is important to note that there are no static functions, properties, or variables within the CurrentTime class because static objects are not accessible through VBA (although you can use them internally within your Class Library).
In order to consume our Class Library using VBA, we must first make it visible to the COM system and register it with the COM Interop. This is done through the project settings in Visual Studio. The screen shots below highlight in red the changes needed to be made.
 |
Under Project Settings, the Application tab, click on the Assembly Information Button. Within the Assembly Information window, check the box next to Make Assembly COM-Visible. |
 |
Under Project Settings, the Build tab, scroll all the way to the bottom until you see the Output section. Check the box next to Register for COM interop. |
Now when you compile your class library, you should see both a VBACOMPluginDemo.dll and a VBACOMPluginDemo.tlb file in the build output directory.
For Vista and UAC Users...
For those who have UAC turned on, you may receive the following error when trying to compile your COM Visible Class Library:
Cannot register assembly "...VBACOMPluginDemo.dll" - access denied. Please make sure you're running the application as administrator. Requested registry access is not allowed.
This occurs because registering the COM Interop requires administration access to the registry. The easiest way to solve this is to run Visual Studio as an administrator by right clicking on the Visual Studio icon, then selecting, run as administrator as pictured below.
Because Microsoft Access Forms provide a quick user interface that can easily display the output of our interactions with our Class Library, I have chosen to use Microsoft Access to exemplify how to consume custom COM Libraries using VBA. The general technique will work for Excel and the other Microsoft Office Applications, as well. The example Access Database is included in the source code download at the end of this tutorial, and is compatible with Access 2002 and later.
I created an Access database called TestDatabase.mdb with a single form called testForm. On the form, I created two labels, one called lblOutputDate (used to display the current date) and one called lblOutputTime (used to display the current time). I also created a single button called cmdTestButton with the text "Get Current Time." The button will be used to obtain the current date and time from our custom .NET COM Library and display the information in the appropriate labels.
Before we can use our .NET Library, we must reference it. Open the Visual Basic Editor that came with Microsoft Office by clicking on the view code button while in design view. Click on Tools -> References to open References window.
 |
Here you can see all the COM Libraries currently referenced by your office document (in our case an Access Database) as well as multiple COM libraries you may reference if you desired. However, our desired custom .NET COM Library will not be on this list. We need to click the Browse button to specify its location. |
 |
After navigating to the Build Output directory of our Visual Studio Project from section one of this tutorial, you will see two files: VBACOMPluginDemo.dll and VBACOMPluginDemo.tlb. Select the VBACOMPluginDemo.tlb file, not the dll file. Then click open. You will be returned to the References Form above and should see VBACOMPluginDemo in the list of Available References, and it should be selected. Click OK to return to the Visual Basic Editor. |
Now that we have successfully referenced our .NET COM Library, we can use it in our VBA Code. Below is the code required to populate the current date and time from our custom library to the appropriate form labels when we click the button.
Private Sub cmdTestButton_Click()
'Create Current Time Object
Dim cTime As New VBACOMPluginDemo.CurrentTime
'Display current date on form using a property
Me.lblOutputDate.Caption = cTime.CurrentDate
'Display current time on form using a method
Me.lblOutputTime.Caption = cTime.GetCurrentTime()
End Sub
After our VBA code has been added, we can open our form and click on the Get Current Time button in order to display the current date and time obtained from our custom control.
If you don't get any date/time updates from our custom control, the VBA code behind the command button may not be running. In order for VBA code to run in any Microsoft Office application, you must enable VBA Macros. The easiest way to enable is to enable it when opening our example database. Every version of Office tends to handle this slightly different. The screen shots and steps are for Access 2007.
Open the TestDatabase.mdb in Access. If you have the default settings for Access 2007, you will see a security warning display on the screen just above the workspace.
Click on the Options...button within the security warning to open up the Microsoft Office Security Options window. Select Enable this content then click OK. The security warning should go away and the code behind the form should now execute.
When opening the TestDatabase.mdb on another computer, you may get the following warnings:
 |
Compile error: Can't find project of library This error occurs when either the .dll or .tlb file are not found or they have not been registered properly with the COM Interop, and the VBA code attempts to create an object defined in the library. This is solved by registering your COM Library with the COM Interop either through re-compiling project on computer in Visual Studio as described above or installing COM Library on computer using any of the methods below. |
|
Your Microsoft Access database of project contains a missing or broken reference to the file VBACOMPluginDemo.tlb version 1.0. *To ensure your database or project works properly, you must fix this reference. This error occurs when opening our example database and Access cannot find the library .tlb file. This is solved by ensuring your COM library exists on the current computer and re-adding the COM library reference as described above. |
Unlike an office document, COM Libraries require special installation procedures on every computer they are accessed in order to work properly. Most importantly, they must be registered with the COM Interop. On the development machine, Visual Studio takes care of this when we check the Register with COM Interop box under the build properties. During the build, Visual Studio will make sure that our Library is registered properly so it will work when we consume it using VBA. In order to register our library with the COM Interop of other computers, we have a couple of options.
Assembly Registration Tool (Regasm.exe)
This command line tool can register your library with the COM Interop for you, and is how Visual Studio registers your library. It's use is quite simple and this method is ideal if you are only going to install your library on a few computers under your control, lets say at home.
Command Line: regasm [LibraryFileName.dll] /tlb:[LibraryFileName.tlb]
Example for VBACOMPluginDemo.dll: regasm VBACOMPluginDemo.dll /tlb:VBACOMPluginDemo.tlb
After execution, you should have a .tlb file used to reference your library in Microsoft Office, and your library should be registered in the COM Interop. To learn more about the .NET Assembly Registration Tool, visit a more detailed description on MSDN's .NET Framework Development Center.
Create an Installer Project
The easiest way to ensure your library is registered with the COM Interop on every machine it is used on, is to simple create an installer that will handle the tricky steps for you. This is ideal if you are distributing your plugin to multiple machines where you may not be the one installing them, or simply want to make the installation easier on yourself.
Download Sample Code: VBACOMPluginDemo.zip (40.17 kb)
The sample code includes the entire Visual Studio 2008 project and a 2003 Microsoft Access compatible mdb database file, nothing more. In order to promote learning, you will have to compile the VBACOMPluginDemo.dll file and add it as a Reference in Microsoft Office yourself.
I hope this tutorial and included example are helpful to anyone wanting to use the power of the .NET framework within Microsoft Office Application. If you have any questions, feel free to ask them in the comments section below.
