Visual Basic 6 (VB6)

In my search to build a com addin for the Excel VBE editor I came accross many articles describing this. All of them seemed to start with creating an add-in for Excel and not for VBE. Even though it will work this way, you have to pull some tricks that without the sample code I would not have been able to build. The following website was of great value to me http://www.appspro.com/Utilities/CodeCleaner.htm. The code I would not have been able to figure out myself is the following: Application.Run "xhYe1lsGtd3soe2t4ns" (this forces VBE to initialize).

The only problem I ran into using the method described there was that I could not make use of the CreateToolWindow method to create a docking window in the VBE Editor. I am assuming this is due to the fact that the method expects a VBIDE.AddIn as it's first argument. When creating an excel com addin the IDTExtensibility2_OnConnection event passes an Excel.AddIn. This wil cause a Type Error to occur... I was stuck.

I realy wanted to have a custom window in the VBE Editor of excel and since the API exposes this method I was pretty sure it should be able to work. So, after some trial and error I found that the solution is to create a VBE addin instead of an Excel Addin. And this is just as easy, it only requires the registry keys to be in a different place than for excel.

Here are the steps I performed to get a toolwindow docked in my Excel VBE environment:

Start VB6 and create a new ActiveX DLL project. This project is called ToolWindowDemo.
Add the following references to your project.

• Microsoft Add-In Designer
• Microsoft Visual Basic for Applications Extensibility 5.3
• Microsoft Excel 14.0 Object Library
• Microsoft Office 14.0 Object Library

A com-addin for excel (I believe for Word and Outlook as well) and VBE needs to implement the IDTExtensibility2 interface. I usually implement this interface in class called somethingConnect (remember thic class because we will later on need to reference it in the windows registry), for the demo I'll call it VBEConnect. Since I started this little article because I wanted to share with you howI managed to make use of the CreateToolWindow method I also add a new item called a User Document and call this myDoc. The User Document will be hosted in the tool window in the VBE. So the VBEConnect class now looks like this:


Option Explicit

Implements AddInDesignerObjects.IDTExtensibility2

Private m_myDoc As myDoc
private m_window as VBIDE.Window

Private Sub IDTExtensibility2_OnAddInsUpdate(custom() As Variant)

End Sub

Private Sub IDTExtensibility2_OnBeginShutdown(custom() As Variant)

End Sub

Private Sub IDTExtensibility2_OnConnection(ByVal Application As Object, ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, ByVal AddInInst As Object, custom() As Variant)

On Error GoTo errorHandler

Dim app As VBIDE.VBE
Set app = Application

Set m_window = app.Windows.CreateToolWindow(AddInInst, "ToolWindowDemo.myDoc", "My Caption", "anystring", m_myDoc)
m_window.Visible = True

errorHandler:
Select Case Err.Number
Case 0
Case Else
Debug.Print Err.Number & " " & Err.Description
End Select
End Sub

Private Sub IDTExtensibility2_OnDisconnection(ByVal RemoveMode As AddInDesignerObjects.ext_DisconnectMode, custom() As Variant)

End Sub

Private Sub IDTExtensibility2_OnStartupComplete(custom() As Variant)

End Sub

As you can see there are some things happening in the OnConnection event. The other events are not necessary for this little demo, you should be able to find enogh references on the web to find out more about them and the order in which they occur. So first I create an app object of Type VBIDE.VBE. This is the object (or class if you will) that contains the Windows collection on which we can invoke the CreateToolWindow method. This returns a VBIDE.Window object, the m_window object was declared as a private member of this class. Beware of the fact that you declare it as a VBIDE.Window object and not just as a Window object, this may be of the incorrect type. Notice that the current Addin, called AddInInst, is passed to the CreateToolWindow method as well as the myDoc (User Document) that is to be hosted in this window. After I created the window I set its visibility to True.

The next step is to compile the dll and register it using the regsvr32.exe command from the command line. After the dll has been registered a key needs to be created in the windows registry. I'm not going to go into the details of all the keys, except for the following: do not put the key in the HKEY_CURRENT_USER/Software/Microsoft/Office/Excel/Addins key, but in the HKEY_CURRENT_USER/Software/Microsoft/VBA/VBE/6.0/Addins key. Name the key after the project name and the class that is implementing the IDTExtensibility2 interface. In our case that would be: ToolWindowDemo.VBEConnect.

Add the following DWORD Values:

• CommandLineSafe with a value = 0
• LoadBehavior with a value = 3

Add the following String Values:

• FriendlyName with a value = ToolWindowDemo
• LoadBehaviorwith a value = My ToolWindowDemo VBE Com Addin

Run the project in debug mode and step through the code if you want (CTRL+F5). The fire-up Excel. When you open the VBA editor (ALT+F11) the toolwindow should appear with the following caption "My Caption". Place any other controls on the "User Document" to actualy make use of this new cool window.

Here are some internet references I used:

• http://www.cpearson.com/excel/mainpage.aspx
• http://msdn.microsoft.com/en-us/library/aa241638%28v=vs.60%29.aspx
• http://www.appspro.com/Utilities/CodeCleaner.htm