VBE CreateToolWindow

Introduction

To create a dockable window in the Microsoft Excel VBA Editor, you must make sure that you create a VBE (VBA) add-in and not an Excel add-in. This article quickly shows how to create a new custom window in the VBA editor making use of the CreateToolWindow method.

Background

Many of the articles on the web regarding COM add-ins for Office discuss add-ins for Excel, Word, or Outlook. But not too many talk about creating and add-in for the Visual Basic Editor of Excel. The VBE can be customised as well creating toolbars, menus, and dockable windows. The API can be found here: http://msdn.microsoft.com/en-us/library/aa443985%28v=vs.60%29.aspx.

The CreateToolWindow method can only be used if a true VBE COM add-in is built, and not with an Excel add-in. The CreateToolWindow method creates a new Tool window containing the indicated UserDocument object.

Using the code

Instead of building an add-in for Excel and calling the Run method to force the VBE to initialize from the OnConnection event, you can also build a VBE COM add-in for the VBA Editor. The only difference is the location of the Registry keys with respect to an Excel add-in. The same Registry entries need to be made, but placed in the following location: HKEY_CURRENT_USER/Software/Microsoft/VBA/VBE/6.0/Addins.

The VB6 project

Create a new ActiveX DLL project in VB6 and name it VBEDemo and add the following references to the 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

Add a class called VBEConnect to the project and implement the IDTExtensibility2 interface in this class. Add a User Document to the project and name it CoolDoc, this User Document will be hosted in the new Tool window in the VBE. Declare two private variables at the top of the class, m_cooldoc as CoolDoc and m_window as VBIDE.Window. The m_window object will hold a reference to the Tool window that we are about to create. The VBEConnect class should now look something like this:

Option Explicit

Implements AddInDesignerObjects.IDTExtensibility2

Private m_cooldoc As CoolDoc
private m_window as VBIDE.Window

Private Sub IDTExtensibility2_OnAddInsUpdate(custom() As Variant)
'do nothing
End Sub

Private Sub IDTExtensibility2_OnBeginShutdown(custom() As Variant)
'do nothing
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, "VBEDemo.CoolDoc", _
                   "My CoolDoc", "anystring",  m_cooldoc)
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)
'do nothing
End Sub

Private Sub IDTExtensibility2_OnStartupComplete(custom() As Variant)
'do nothing
End Sub

The magic happens in the OnConnection event. Here a VBIDE.VBE application object is declared that is used to call the CreateToolWindow method. The method takes the current add-in and User Document as arguments and this is where things go wrong if an Excel COM add-in is created. The AddInInst object must be a VBE add-in and not an Excel add-in!

In a previous project (an Excel add-in), I forced the VBE to start using the Run method and tried to execute CreateToolWindow, but that did not work. Just to show the difference, here is the code that does not work:

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

Dim gxlApp as Excel.Application
Set gxlApp = Application

Dim gxCAI as Excel.Addin
Set gxCAI = AddInInst

On Error Resume Next
''' Force the VBE to initialize.
gxlApp.Run "xhYe1lsGtd3soe2t4ns"
On Error GoTo errorHandler

Set m_window = app.Windows.CreateToolWindow(AddInInst, "VBEDemo.CoolDoc", 
                   "My CoolDoc", "anystring",  m_cooldoc)
m_window.Visible = True

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

Running the code

Before the add-in will work, it is necessary to compile the DLL and add it to the Registry using the regsvr32.exe command. The proper keys need to be created in the Windows Registry in the following location: HKEY_CURRENT_USER/Software/Microsoft/VBA/VBE/6.0/Addins.

The new key must be named after the project name and the class that is implementing the IDTExtensibility2 interface: VBEDemo.VBEConnect. The key needs to contain two DWORDs and two string values:

• DWORD: CommandLineSafe | 0
• DWORD: LoadBehavior | 3
• String value: FriendlyName | VBEDemoToolWindow
• String value: Description | A Tool Window VBE Addin Demo

When the Excel VBA editor is started, the add-in will load and the Tool window will appear. Of course, this does not do anything since no functionality has been added, but I had a grin from ear to ear having spent some hours searching the web to find out how to do it (without success if I may add) and reverting to trial and error. I hope this little super simple article may save other people a couple of hours.

History

• First version: 2012-01-05 (yes, some people still use VB6).