COM addins were introduced in Office 2000. The alternative to a COM Addin is a standard addin such as an xla for Excel or Template for word. In essence COM addins are compiled dll’s (dynamic link libraries) that use the COM interface to interact with Office. The fact that they are compiled means that they:
- are more secure
- run faster
- are able to use controls and structures unavailable in VBA
The drawback though is that they a bit harder to:
There are basically few options to compile directly from VBA code:
- Visual Basic (5 or 6), as Add-in (DLL or OCX)
- Visual Basic (5 or 6), as a compiled EXE from XLS file
- Visual Basic 5 Controls Creation Edition, as ActiveX Add-in (OCX)
All probably will require a little code edition, but should be minor editions.
Creating a COM Addin in VB6
Open VB6 and create a new project. In the list of available projects select the type Addin.
When the new project opens you will have a Form called frmAddin and a Designer object called Connect. Remove the form from the project as it is a sample form and we don’t need it.
The next step is to set the addin up using the designer object. Double click on it and you will be presented with a Designer Form with several fields.
- Addin Display Name – This is the name of your addin.
- Addin Description – Give your addin a description so users know what it does
- Application – The application you are creating the Addin for. For the purposes of this article we will create an Addin for Excel so select Excel.
- Application Version – The version of the application you are creating it for.
- Initial Load Behaviour – Whether the addin loads automatically when the application is started up or not. For this addin set this field to Startup. The addin will then load when Excel starts.
Setting References and Other Project properties
The next thing to do is to set the required references. By default VB adds a reference to the VB6 extensibility library i.e. it assumes we are creating an addin for VB6. We wish to remove this and replace it with the reference to Excel.
To do this goto Project/References, uncheck the VB Extensibility Library and then find the reference to the Microsoft Excel Object Model. Check the box next to it and click OK.
Next goto to Project/Properties (bottom of the Project menu) and set the name and description of the project. (The difference here between the name set in the designer is that as we are creating a standard COM/ActiveX dll it could be added to a reference to other projects as well as being used as an addin. The Description here is the one that will appear in the references list.) This would then be a good time to save the project.
More on the Designer
Right click on the designer object in the project explorer and select ‘View Code’ or click the ‘View Code’ button at the top of the project explorer.
You will notice there is already alot of code in here that was generated for the sample Addin. Most of this code can be removed from the module. The only 2 procedures you need to leave behind are:
Private Sub AddinInstance_OnConnection(ByVal Application As Object, _ ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, ByVal AddInInst As Object, custom() As Variant)
Private Sub AddinInstance_OnDisconnection _ (ByVal RemoveMode As AddInDesignerObjects.ext_DisconnectMode, custom() As Variant)
These 2 procedures relate to the starting up and closing down of the addin. The starting up procedure passes a reference to the application that loaded the addin. In our case this will be an Excel application object. We can store this and use it for manipulating Excel from within the addin. Add a standard code module to the project and add the following line of code:
Public xlApp As Excel.Application
Now go back to the OnConnection procedure. Remove the existing code and add:
This procedure is also the place to initialise any objects and create any menus/toolbars etc. Therefore add a procedure CreateToolbarButtons to your code module and call it from you OnConnection procedure. The completed procedure should now look something like the following:
Private Sub AddinInstance_OnConnection(ByVal Application As Object, _ ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, ByVal AddInInst As Object, custom() As Variant) 'store the application Set xlApp=Application 'set up our menus CreateToolbarButtons End Sub
The OnDisconnection procedure is called when the addin is unloaded. It should therefore be used to remove any toolbars and destroy any objects etc. The procedure should therefore look something like:
Private Sub AddinInstance_OnDisconnection _ (ByVal RemoveMode As AddInDesignerObjects.ext_DisconnectMode, custom() As Variant) 'destroy xl object reference Set xlApp=Nothing 'removemenus RemoveToolbarButtons End Sub
You should also now add a procedure to your code module called RemoveToolbarButtons.
This is the basic structure of a COM addin completed – not too hard really!
Handling Toolbar Events
In Excel VBA when you create a commandbar button you can set the OnAction property to the name of the procedure you wish to run when the button is clicked. With COM addins, although you can still do this, when the button is clicked the OnAction procedure will not be found if it resides within the dll. OnAction will only look in standard VBA code modules.
This means that to handle the events you have to use a class module and declare a reference to the object ‘WithEvents’. You can then access the click event of button (other controls have different events available). Therefore add a class module to your project and name it cbEvents. Now add the following line:
Public WithEvents cbBtn As CommandBarButton
Now if you can select cbBtn in the top left dropdown and select the click event in the top right dropdown. The following code should result:
Private Sub cbBtn_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean) End Sub
Notice you are passed a reference to the control that caused the procedure to be called. We shall use this class module to handle the click event of all the buttons we create so we need to check which control was passed to determine the procedure to call. The easiest way to do this is to check the OnAction property. We can set this when we create the buttons to the name of the procedure we wish to call and use a select case statement to call that procedure e.g.
Select Case Ctrl.OnAction Case "Sub1" Sub1 Case "Sub2" Sub2 End Select
Finally, we need to set the CancelDefault property to true within this event to cancel Excel looking for the sub specified in the OnAction property which as discussed earlier it won’t find!
The completed class module should therefore look something like:
Option Explicit Public WithEvents cbBtn As CommandBarButton Private Sub cbBtn_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean) 'supress errors On Error Resume Next 'check onaction property Select Case Ctrl.OnAction Case "Sub1" Sub1 Case "Sub2" Sub2 End Select 'cancel Excel looking for the sub specified in the OnAction property CancelDefault = True End Sub
You should now create your toolbar buttons in the usual way in your CreateToolbarButtons procedure remembering to use xlApp instead of Application to reference the commandbars collection. The only difference is that when you have created the button you should assign it to an instance of the class we just created. The easiest way to do this is to declare an instance of the class and of a collection at the top of the module i.e.
Dim ButtonEvent As cbEvents Dim ButtonEvents As Collection
Now at the start of your CreateToolbarButtons procedure create a new collection:
Set ButtonEvents=New Collection
Finally after each button you create create a new instance of cbEvents class and assign to ButtonEvent, assign the created button to the cBtn of the ButtonEvent object then add the ButtonEvent object to the collection. In code this becomes (assuming your newly created button is in an object btNew):
Set ButtonEvent=New cbEvents Set ButtonEvent.cBtn=btNew ButtonEvents.Add ButtonEvent
An example procedure is as follows:
Public Sub CreateToolbarButtons() 'to make sure the buttons we are about to add aren't added twice 'try to remove them first RemoveToolbarButtons 'declare some variables Dim cbBar As Office.CommandBar Dim btNew As Office.CommandBarButton 'create a new collection Set ButtonEvents=New Collection 'find the worksheet menu bar in excel (this is the one 'with the file, edit, view etc. commands) Set cbBar = xlApp.CommandBars("Worksheet Menu Bar") 'add a new button to the Tools menu Set btNew = cbBar.Controls("Tools").Controls.Add(msoControlButton, , , , True) With btNew .OnAction = "Sub1" 'set a unique tag to make our custom controls easy 'to find later to delete .Tag = "COMAddinTest" 'set the tooltip text .ToolTipText = "Calls Sub1" 'set the caption that appears in the menu .Caption = "Sub1" End With 'get a new instance of our cbevents class Set ButtonEvent = New cbEvents 'now assign the button we created to it Set ButtonEvent.cbBtn = btNew ButtonEvents.Add ButtonEvent 'now add another button Set btNew = cbBar.Controls("Tools").Controls.Add(msoControlButton, , , , True) With btNew .OnAction = "Sub2" 'set a unique tag to make our custom controls easy 'to find later to delete .Tag = "COMAddinTest" 'set the tooltip text .ToolTipText = "Calls Sub2" 'set the caption that appears in the menu .Caption = "Sub2" End With 'get a new instance of our cbevents class Set ButtonEvent = New cbEvents 'now assign the button we created to it Set ButtonEvent.cbBtn = btNew ButtonEvents.Add ButtonEvent End Sub
The RemoveToolbarButtons sub would then look something like:
Public Sub RemoveToolbarButtons() Dim cbBar As CommandBar Dim cbCtr As CommandBarControl 'supress errors - this is important here as they may not have been created 'yet or may have been alreday deleted On Error Resume Next 'need to remove button from the command bar 'first find the commandbar Set cbBar = xlApp.CommandBars("Worksheet Menu Bar") 'not find the control using the tag we set when creating Set cbCtr = cbBar.FindControl(, , "COMAddinTest") While Not cbCtr Is Nothing 'now delete it cbCtr.Delete Set cbCtr = cbBar.FindControl(, , "COMAddinTest") Wend 'remove event handlers from memory Set ButtonEvents = Nothing Set ButtonEvent=Nothing End Sub
Installing an Excel COM Addin
Registering the Dll
Dll’s require registering with Windows in order to run. To do this you need to run regsvr32.exe passing it the filename of the dll. An easy way to do this is to download the script on the downloads page. Once you have run the script you’ll be able to Register and Unregister dll’s easily using the right click context menu.
NB: When you create the DLL in VB6 it will auto register it on the PC it was created on.
NB2: Also note that to remove or delete a dll you should unregister it first. You can again do this easily if you have ran my script but if not you need to run regsvr32.exe passing the /u switch.
Installing the Addin in Excel
As we set the initial load behaviour to ‘Startup’ in the designer the Addin should load automatically on reloading Excel. If startup isn’t selected then you may need to load it manually. To do this you need to select the ‘Com Addins’ menu option in Excel.
This is often not available by default and needs to be added by going to Tools/Customise and dragging it on to the menu bar. On selecting the ‘Com Addins’ button the available Com addins will be listed. The Addin should be in the list with a checkbox next to it. This will be checked if it is loaded and unchecked if unloaded. You can also remove addins and browse for other addins not in the list.
Create ActiveX controls using the VB 5 CCE
[adapted from http://www.borncity.com/web/WSHBazaar1/WSHVB5CCE.htm]
Visual Basic 5 Controls Creation Edition (VB 5 CCE) (Note: Microsoft has removed VB 5 CCE in 2004, but could be found on Microsoft Press book about Access Basic, on the companion CD), is basically a stripped-down version of the full Visual Basic 5.0 application; i.e., it does not include the functionality to create DLL and EXE projects and it only has limited documentation.
|Visual Basic Runtime||msvbvm50.exe (1.3m)|
|Visual Basic 5.0 Control Creation Edition||vb5ccein.exe (7.1m)|
|Visual Basic 5.0 CCE Help Files||ccehelp.exe (2.5m)|
Hereafter it explains the basic techniques to create an ActiveX control using VB 5 CCE. In addition, this page comes with a ready-to-use sample that you could use as a foundation to develope your own controls.
The steps to create an ActiveX control
Install VB 5 CCE in Windows, and fire it.
- After launching the program, a “New project” dialog asks you what to do. Select the ActiveX Control icon on the New property page and close the dialog window using the “OK” button. A new project will be created. This project contains one UserControl.
- Click in the project explorer window on the project name (“Project1(project1)”). Use the (Name) entry of the property page to re-name your project (from “project1” to “WSHTest” for instance).
- Click on the Entry “UserControl(UserControl)”. Use the (Name) entry in the property page to re-name your control from “usercontrol1” to “Test1” for instance.
- Save the project into a file (like WSHTest.vbp) using the Save button or the File menu.
- Double click on the UserControl icon to open the control window. Use the Create “View Code” button in the project explorer window to show the code page.
- Add the code for procedures and functions which will be used within the project. These entries are exposed later as the methods of your ActiveX control.
- Select the command “Make …..ocx” in the “Files” menu. If you used the file Test.vbp to save your project the name Test.ocx is used. I have used the name WSHTest.ocx in the ZIP-Archive.
In some environments, the last step opens some dialogs allowing you to set a few options for your ActiveX control. You may close these dialogs to set the default values. Then, an OCX-file with the name of your project file (or the selected name) gets created. The OCX control gets registered automatically on your machine. Within your WSH script you do not need to worry about this file name, because the Windows Registry keeps all the information where to find the control.
Note: The window of the project explorer may be found in the upper right corner of the picture shown above. Currently my WSHTest project contains only one UserControl module. The window in the lower right is the property window showing the properties of the selected module. Currently I have selected the UserControl Test1. The names shown in brackets within the project explorer window are derived from the file names. If you save the project the first time, a project file name is associated. I have used here Test.vbp, so all other files uses the same name but different extensions (like Test.ctl for the source code file of the control). I have named the OCX file as WSHTest.ocx (which is different than the project file name) to demonstrate that file names are not used within your WSH scripts.
|Important: Thanks to Steven Bondi, who has tested my sample, I would like to add an additional note. I can’t publish here hundred pages of my WSH book used to explain the basics of ActiveX programming. But, my first release of this page was a bit to short. You may use my sample ZIP file to study the code. But take care that the path settings depend on my test machine (I run several Windows 98 builds on my test machine – therefore the project file contains path names that may not apply to all user machines). If you intend to create your own ActiveX control it is important to use a new project! This assures that all paths and libraries are set properly within the project. Also VB 5 CCE creates a unique GUID for your control. This GUID is requested to register your control as a COM object. If you would like to sample code from another project, use the export and import function of VB 5 CCE (the program can run only one project at a time – this is another limitation of VB).|
Use the Control in your script
After completing these steps, your ActiveX control is ready to be used within VBA. You need the project name and the UserControl in the VB 5 CCE, required for the CreateObject (“TypLib.class”) statement. The following VBScript line creates a reference to your new ActiveX control:
Set objAdr = WScript.CreateObject("WSHTest.Test1")
The name WSHTest is the name of the type library and it corresponds to the project name. The subname Test1 is the class name of the object and it is the same as the UserControl name. After you create a reference to the object using the CreateObject method you may access the methods exposed from this object. The following statement uses the WSHDate method:
myDate = objAdr.WSHDate()
We use the object variable and the name of the method within an assignment statement. The result is assigned to the myDate variable. Pretty simple, isn’t it?
The source code for the sample ActiveX control
For your convenience I have provided a few simple methods within the UserControl Test1. The source code is shown below:
'************************************************ '* Project: Test1 (file Test.vbp) '* Author: (c) Günter Born '* '* This control demonstrates how to use the VB 5 CCE to '* create ActiveX-controls for the Windows Scripting Host. '* Compile it using the "Make Test.ocx" command of the '* "File" menu within the VB 5 CCE. The OCX component is '* getting registered automatically. '* '* Use it at your own risk. In no event will the author be '* responsible/liable for any consequences of the use of this software. '* The control may be used for free. Distribution is allowed, '* if the whole package is distributed with the copyright note '* and a note pointing to my web-site. This demo was derived '* from a real sample published in my book "Inside Windows '* Scripting Host", Microsoft Press Germany. Further versions '* of this control will be published on my WSH Bazaar at: '* http://www.borncity.de '************************************************* Public Function WSHGetTime() As String ' Just to test, return current time WSHGetTime = Time End Function Public Function WSHGetDate() As String ' Just to test, return current date WSHGetDate = Date End Function Public Function WSHInputBox(Prompt As String, title As String, Default As Variant) As Variant ' For JScript: provide a InputBox-Function WSHInputBox = InputBox(Prompt, title, Default) End Function Public Sub WSHAbout() txt = "ActiveX-Control to extend the Windows Scripting Host" + vbCrLf + _ "with a new Test object and and new methods. Get more from my" + vbCrLf + _ "Windows Scripting Host Bazaar at:" + vbCrLf + vbCrLf + _ "http://www.borncitye.de" MsgBox txt, vbOKOnly + vbInformation, "(c) Günter Borns WSH-Test - Version 1.1" End Sub '*** End
The ActiveX control exposes the methods WSHGetDate, WSHGetTime, WSHInputBox and WSHAbout. You may call these methods from either VBScript or JScript. WSHGetDate and WSHGetTime returns the current date and the current time. The WSHAbout method invokes a simple dialog box with some information about the control. The WSHInputBox method invokes an Inputbox dialog. This will be rather helpful in JScript, because this language doesn’t support the InputBox function from VBScript. Having the right ActiveX control enables you to use WSHInputBox in JScript (without the trickier way I have shown in my sample pages).
The source code for a VBScript test program
To show you more about the way how to use the methods provided by the WSHTest.ocx, I have created a short VBScript program. (By the way, the interface of these methods are identically to the WSHExtendActiveX control offered for download from this site). Further details about using the ActiveX control may be obtained from the following listing.
'************************************************ ' File: TestOCX.vbs (WSH sample in VBScript) ' Author: (c) Günter Born ' ' Demonstrates how to access the Test.ocx component ' using a WSH script. ' Use at your own risk. No support, no liability. ' ' New samples are available at the the WSH Bazaar: ' http://www.borncity.de '************************************************ Option Explicit Dim objAdr MsgBox "Test whether the ActiveX control Test.ocx is registered." + _ vbCRLF + "If a run-time error occurs, the OCX isn't registered." + _ vbCRLF + "Then use the program RegisterOcx.bat." ' Create the Test object, requires Test.ocx, otherwise the ' following line creates a run-time error (can't create object). Set objAdr = CreateObject("WSHTest.Test1") ' our first attempt to use some methods WScript.Echo "Time:", objAdr.WSHGetTime() WScript.Echo "Date:", objAdr.WSHGetDate() WScript.Echo "Your Input was: ", objAdr.WSHInputBox ( _ "Enter something", "WSHTest by G. Born", "Hello") objAdr.WSHAbout ' *** End
After invoking the script (double click on the VBS file), you will be guided through a few dialogs showing the date and time, asking for input and showing the “About” dialog. As you can see, accessing the methods of the ActiveX control is very simple.
And here comes another simple sample which may be used in the real world: it shows how convenient the ActiveX control WSHTest.ocx may be for the JScript programmers. The following few lines of code implements an InputBox in JScript.
//************************************************ // File: Input.js (WSH sample in JScript) // Author: (c) G. Born // // This script demonstrates how to get a user input in // JScript. I use the ActiveX control Test.ocx. // Use AS IS without any warranty. The source: // the WSH Bazaar at // http://www.borncity.de //************************************************ // var title = "InputBox function for JScript"; var prompt = "Please enter your name:"; // Create the new object var WSHTest = WScript.CreateObject("WSHTest.Test1"); // Get the user input and display it with the Echo method WScript.Echo (WSHTest.WSHInputBox (prompt, title, "Hello World")); //*** End
After invoking the script (double click on the JS file), you will see an input box asking for your name. If you click on OK, the user input will be shown in a 2nd dialog box. Clicking Cancel returns an empty string which will also be shown in a message box.
The whole project may be downloaded as a ZIP file: ActiveXDemo.zip (8 KB). Unzip the content of your file into a separate folder. The folder will then contain the source code modules for VB 5 CCE, the ready to use OCX, the VBS/JS test files and two BAT files. If you have already installed VB 5 CCE you can register the OCX and use the VBS file to test the control. To register/unregister the control use the two BAT files RegisterOcx.bat and UnregisterOcx.bat. If you load the VBP file into VB 5 CCE, you can inspect the source code and you may compile the project into a new OCX file.
Some examples of how to use the control to control Excel or Word:
- Excel: load a (CSV) file, print it and exit Excel
- Excel: import CSV files (the big hammer)
- Word: just a sample to access this application
Compile XLS file to EXE
In VB6, select Add-Ins, Add in manager, VB6 Resource editor, Loaded/Unloaded, OK, select VB resource editor on tool bar (green), click Add Custom resource (on tool bar of the Resource editor) and add your .xls file. Note the “CUSTOM” number added. Use the following code to instal the .xls file.
LoadDataIntoFile 120, "C:\Myfilename.xls" ' ***120 is Custom Resource number ' "C:\Myfilename.xls" is file destination Public Sub LoadDataIntoFile(DataName As Integer, filename As String) Dim myArray() As Byte Dim myFile As Long If Dir(filename) = "" Then myArray = LoadResData(DataName, "CUSTOM") myFile = FreeFile Open filename For Binary Access Write As #myFile ' Put #myFile, , myArray Close #myFile End If End Sub