This page has been replaced by a new, updated version. If you are not redirected to the new page, click here.Creating An Automation Add-In For Excel
With Excel 2002 (XP), Excel introduced the Automation
Add-In. An Automation Add-In differs from both a standard XLA add-in and a
COM Add-In. An Automation Add-In (which can be incorporated within a COM
Add-In, see the Creating COM Add-Ins In Visual
Basic 6 page) does not extend the user interface or functionality of
Excel. It supports no user interface elements, and it does not implement the
IDTExtensibility2 interface as does a COM Add-In. There are no Startup or
Shutdown event procedures. It is simply an ActiveX DLL that contains
functions that are callable from worksheet cells. Automation Add-Ins will
work fine in Excel 2007 with no modifications.
The advantages of a Automation Add-In are two-fold:
Creating The Automation Add-In
Creating an Automation Add-In is very simple using Visual Basic 6. Procedures for writing an Automation Add-In in NET will appear later on this site. Start Visual Basic 6, choose New Project form the File menu, and choose ActiveX DLL. This will create a project with a single class module named Class1. Typically, your Automation Add-In will contain only one class contains the callable functions. However, you are not restricted to a single class. You can create multiple classes, each containing a family of related functions. In this example project, we will create two classes, ExcelMath and ExcelStrings.
First, you need to set up the environment for development. Go to the Project menu, choose References, and set a reference to the Microsoft Excel object library. While strictly speaking this is not required, you will need it if you are going to use any Excel object definitions such as a ranges or worksheets, or if you are going to use any of Excel's built-in worksheet function in your code. Change the name of the project from "Project1" to "ExampleAutomation" or any other meaningful name. Then change the name of the existing class module from "Class1" to "ExcelMath". Add a new class module from the Project menu, and name this class "ExcelStrings". In this example, we will create a single function in each class module. However, in the real world, your Automation Add-In class(s) will contain multiple functions. The Instancing property of both classes should be set to 5 - Multiuse.
Put the following code in each class.
Private XL As Excel.Application
Private Sub Class_Initialize() On Error Resume Next Set XL = GetObject(, "Excel.Application") End Sub Private Sub Class_Terminate() Set XL = Nothing End Sub
Then define your functions. In ExcelMath, we will have a single function named Times4, which simply multiplies it input value by 4 and returns the result.
Public Function Times4(D As Double) As Double Times4 = D * 4 End Function
In ExcelStrings, we will have a single function named CountChars, which returns the number of times a specific character occurs in an input text string. Paste the following function in ExcelStrings:
Public Function CountChars(Text As String, C As String, _ Optional CompareMode As VbCompareMethod) As Variant Dim Ndx As Long Dim Counter As Long If Len(C) > 1 Then CountChars = CVErr(xlErrValue) Exit Function End If For Ndx = 1 To Len(Text) If StrComp(C, Mid(Text, Ndx, 1), CompareMode) = 0 Then Counter = Counter + 1 End If Next Ndx CountChars = Counter End Function
Now you are ready to build the Add-In. From the File menu, choose "Make ExampleAutomation.dll...". If the compilation was successful, Visual Basic will automatically register the two classes in the System Registry (in the HKEY_CLASSES_ROOT section of the Registry). To use the functions, you must load the Automation Add-In classes from within Excel.
Registering The Automation Add-In With Windows
After you create your add-in, or if you add additional public classes containing new function to the project, you will need to unregister the existing library and then re-register the library with Windows. This ensures that all the classes containing function are available to other projects and workbooks. With Excel closed, go to the Windows Start menu and choose Run. In that dialog, enter the following to unregister the class library. Of course, change the file name to your specific file:
RegSvr32 "C:\MyFolder\DLLFileName.dll" /u
Then repeat this, omitting the /u, to register the library with Windows:
Go to the Tools menu, choose Add-Ins (not COM Add-Ins) and in that dialog, click the Automation button. This will display a very long list of available automation servers. Scroll down in the list to find ExampleAutomation.ExcelMath. Click OK. Repeat this process for ExampleAutomation.ExcelStrings. Now, in the Add-Ins list, you should see ExampleAutomation.ExcelMath and ExampleAutomation.ExcelStrings checked in the list. You can now call the functions in these classes directly from worksheet cells. For example,
You can call the functions in your Automation Add-In from your own VBA code. The first step is to set a reference in your VBA Project to the DLL file of your Automation Add-In. From the Tools menu, choose References to display the References dialog. In that dialog, click the Browse button and navigate to your Automation Add-In's DLL. Click Open. Once you've set the reference, you must create an object variable with a data type of your class library. Declare these variables as Public, project-wide variables, using the New keyword:
Public XLMath As New ExampleAutomation.ExcelMath Public XLStrings As New ExampleAutomation.ExcelStrings
We use the New keyword in the variable declaration in order to ensure that the variable will be created as needed. Now you can call upon the function in the DLL with code such as the following:
Sub AAA() Dim D As Double Dim Res As Double D = 123.456 Res = XLMath.Times4(D) Debug.Print Res End Sub
While I usual avoid using the 'New' keyword in a variable declaration (instead using code like Set XLMath = New ExampleAutomation.ExcelMath), I recommend using it in this situation to ensure that the XLMath and XLStrings variables will never become Nothing, due to editing existing VBA code or a procedure calling 'End'. If the variables were to be reset to Nothing, the code would raise an error 91, "Object variable or With block variable not set". Avoiding this error is especially important if you are writing a function that will be called from a worksheet cell.
Distributing Your Automation Add-In
You can distribute your to other users and other
machines. Copy the DLL file from your computer to the user's computer.
Then on the user's computer, go to the Windows Start menu, choose Run, and
enter the following:
Of course, change the path and filename to your DLL file.
Then, repeat the steps described in Loading The Automation Add-In above to make the add-in available in Excel on the user's machine.
That's it. That's all there is to writing Automation Add-Ins for Excel.
You can download the complete Visual Basic 6 project files here.