38
To find the VBProjectthat corresponds to the workbook Book1.xlsm, the following code can be used:
Dim oVBP As VBIDE.VBProject
Set oVBP = Workbooks(“Book1.xlsm”).VBProject
When creating Add-ins for the VBIDE itself, you often need to know which project is currently high-
lighted in the Project Explorer. This is given by the ActiveVBProjectproperty of the VBE:
Dim obVBP As VBIDE.VBProject
Set obVBP = Application.VBE.ActiveVBProject
Note that the ActiveVBProjectis the project that the user is editing within the VBE. It is not related in
any way to the ActiveWorkbookgiven by Excel. In fact, with the Developer Editions of Office 2000 and
Office XP, it was possible to create self-contained VB Projects that are not part of an Excel workbook.
That capability should still be available if you upgraded from one of those versions.
The VBComponent Object
The UserForms, standard modules, class modules, and code modules behind the worksheets and work-
book are all VBComponentobjects. Each VBComponentobject corresponds to one of the lower-level items
in the Project Explorer tree. Aspecific VBComponentcan be located through the VBComponentscollection
of a VBProject. Hence, to find the VBComponentthat represents the UserForm1form in Book1.xls,
code like this can be used:
Dim oVBC As VBIDE.VBComponent
Set oVBC = Workbooks(“Book1.xlsm”).VBProject.VBComponents(“UserForm1”)
The name of the VBComponentthat contains the code behind the workbook, worksheets, and charts is
given by the CodeNameproperty of the related Excel object (the workbook, worksheet, or chart object).
Hence, to find the VBComponentfor the code behind the workbook (where code can be written to hook
into workbook events), this code can be used:
Dim oVBC As VBIDE.VBComponent
With Workbooks(“Book1.xlsm”)
Set oVBC = .VBProject.VBComponents(.CodeName)
End With
And for a specific worksheet:
Dim oVBC As VBIDE.VBComponent
With Workbooks(“Book1.xlsm”)
Set oVBC = .VBProject.VBComponents(.Worksheets(“Sheet1”).CodeName)
End With
Note that the name of the workbook’s VBComponentis usually ThisWorkbook in the Project Explorer.
Donot be tempted to rely on this name. If your user has chosen a different language for the Office User
Interface, it will be different. The name can also be easily changed by the user in the VBE. For this reason,
do notuse code like this:
573
Chapter 26: Programming the VBE
39
Dim oVBC As VBIDE.VBComponent
With Workbooks(“Book1.xlsm”)
Set oVBC = .VBProject.VBComponents(“ThisWorkbook”)
End With
When developing Add-ins for the VBE, you often need to know the VBComponentthat the user is edit-
ing (the one highlighted in the Project Explorer). This is given by the SelectedVBComponentproperty
of the VBE:
Dim oVBC As VBIDE.VBComponent
Set oVBC = Application.VBE.SelectedVBComponent
Each VBComponenthas a Propertiescollection, corresponding approximately to the list shown in the
Properties window of the VBE when a VBComponentis selected in the Project Explorer. One of these is
the Nameproperty, shown in the following test routine:
Sub ShowNames()
With Application.VBE.SelectedVBComponent
Debug.Print .Name & “: “ & .Properties(“Name”)
End With
End Sub
For most VBComponentobjects, the text returned by .Nameand .Properties(“Name”)is the same.
However, for the VBComponentobjects that contain the code behind workbooks, worksheets, and charts, the
.Propertiescollection includes all the properties of the native Excel object, so .Properties(“Name”)
gives the name of the workbook, worksheet, or chart. You can use this to find the Excel object that corre-
sponds to the item that the user is working on in the VBE, or the Excel workbook that corresponds to the
ActiveVBProject. The code for doing this is shown later in this chapter.
The CodeModule Object
All of the VBAcode for a VBComponentis contained within its CodeModuleobject. Through this object,
you can programmatically read, add, change, and delete lines of code. There is only one CodeModulefor
each VBComponent. In the Office VBE, every type of VBComponenthas a CodeModule.
The CodePane Object
This object provides access to the user’s view of a CodeModule. Through this object, you can identify
such items as the section of a CodeModulethat is visible on the screen and the text that the user has
selected. You can identify which CodePaneis currently being edited by using the VBE’s
ActiveCodePaneproperty:
Dim oCP As VBIDE.CodePane
Set oCP = Application.VBE.ActiveCodePane
The Designer Object
Some VBComponents (such as UserForms) present both code and a graphical interface to the developer.
Whereas the code is accessed through the CodeModuleand CodePaneobjects, the Designerobject gives
574
Chapter 26: Programming the VBE
45
you access to the graphical part. In the standard versions of Office, UserForms are the only components
with a graphical interface for you to control. However, the Developer Editions included in Office 2000
and XPincluded a number of other items (such as the Data Connection Designer) that have graphical
interfaces; these too are exposed to us through the Designerobject and may be available in Office 2007
if you upgraded from one of those earlier versions.
These are the main objects that you’ll be using throughout the rest of this chapter, as you create the VBE
Toolkit Add-in.
Starting Up
There is very little difference in Excel 2007 between a normal workbook and an Add-in. The code and
UserForms can be modified in the same manner, and they both offer the same level of protection (locking
the Project from view). The two advantages of using an Add-in to hold your tools are that it is invisible
within the Excel User Interface, and that it can be loaded using Excel’s Add-ins dialog (Office Menu➪
Excel Options➪Add-Ins➪Manage: Excel Add-Ins➪Go). This chapter uses the term Add-into mean a
container for tools that you’re adding to Excel or the VBE. In fact, during the development of the Add-in,
you will actually keep the file as a standard workbook, only converting it to an Add-in at the end.
Most Add-ins have a common structure, and the one you develop in this chapter will be no exception:
❑
Astartup module to trap the opening and closing of the Add-in
❑
Some code to add the custom menu items to the command bars on opening and remove them
on closing
❑
For the VBE, a class module to handle the menu items’ Clickevents
❑
Some code to perform the menus’ actions
Start with a new workbook and delete all of the worksheets, apart from the first. Press Alt+F11 to switch
to the VBE, and find your workbook in the Project Explorer. Select the VBProjectentry for it. In the
Properties window, change the project’s name to aaVBETools2007. The name starts with the prefix aa,
so it always appears at the top of the Project Explorer, nicely out of the way of any other projects you
may be developing.
Double-click the ThisWorkbookVBComponentto bring up its code pane and type in the following code:
Option Explicit
Dim moMenuHandler As CMenuHandler
‘’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’
‘ Subroutine: Workbook_Open
‘
‘ Purpose: Create a new instance of the menu-handling class.
‘ The class’s Initialize event sets up the menus.
‘
Private Sub Workbook_Open()
Set moMenuHandler = Nothing
Set moMenuHandler = New CMenuHandler
End Sub
575
Chapter 26: Programming the VBE
42
This code is run when the workbook is opened, and it just creates a new instance of a class module
(which you’ll create next) and stores a reference to it in a module-level variable. Using this technique,
theClass_Initializeevent is run when the workbook is opened. The class is kept alive while the
workbook is open and is only destroyed (with Class_Terminatecalled) when the workbook is
actuallyclosed—crucially, afterthe user has been given the opportunity to cancel the close (whereas
theWorkbook_BeforeCloseevent is called before the user’s opportunity to cancel the close).
Adding Menu Items to the VBE
The VBE uses the CommandBar object model rather than the Ribbon, so the procedure for adding menus
to the VBE is almost the same as that documented in Chapter 15 for creating popup toolbars. There is
one major difference, which is how to run your routine when the menu item is clicked. When adding
menu items to Excel’s popup toolbars, you set the OnActionproperty of the CommandBarButtonto the
name of the procedure to run. In the VBE, the CommandBarButtonstill has an OnActionproperty, but it
is ignored.
Instead, Microsoft added the Clickevent to the CommandBarButton(and the Changeevent to the
CommandBarComboBox). To use these events, you have to use a class module containing a variable of the cor-
rect type declared WithEvents. So add a class module to the project, give it the name of CMenuHandler,
and type in the following code:
Option Explicit
‘A variable to hook the click event for all our menus.
Private WithEvents mbtnEvents As Office.CommandBarButton
‘’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’
‘ Subroutine: mbtnEvents_Click
‘
‘ Purpose: Handle the click event of all our menus, by running the procedure
‘ stored in the button’s OnAction property
‘
Private Sub mbtnEvents_Click(ByVal Ctrl As Office.CommandBarButton, _
CancelDefault As Boolean)
On Error Resume Next ‘In case the routine is wrong/doesn’t exist
‘Run the routine given by the commandbar control’s OnAction property
Application.Run Ctrl.OnAction
‘We handled it OK
CancelDefault = True
End Sub
The key things to note here are:
❑
Avariable, mBtnEvents, is declared to receive the Clickevent for the menu items.
❑
The Clickevent is raised by the object referred to by the mBtnEventsvariable (the only one
exposed by it).
576
Chapter 26: Programming the VBE
44
❑
The Clickevent passes the Ctrlobject (the menu item or toolbar button) that was clicked.
❑
The code runs the routine specified in the control’s OnActionproperty. The code is simulating
the behavior that occurs when adding menu items to Excel’s popup toolbars.
To use this procedure, set the mbtnEventsvariable to refer to one of your custom menu items. The
CommandBars event model is designed in such a way that when setting the reference, you’re also creat-
ing an association between the mbtnEventsvariable and the menu’s Tagproperty. This means that all
menu items that share the same Tagproperty will also raise Clickevents against that variable. You can
now add as many menus as you like, and all their Clickevents will be handled by that one procedure
(as long as you give them all the same Tagproperty).
Now that you can respond to menus being clicked, all you need to do to build the Add-in is add some
custom menus and the procedures to be called from the Clickevent. The easiest place to create your
menus is from within the Class_Initializeevent of the CMenuHandlerclass, which is called when
the class is created in the Workbook_Openprocedure. You can also include code to tidy up after yourself,
by removing the custom menus in the Class_Terminateevent. Because you’ll be adding lots of menus
in this chapter, it makes sense to factor out the menu-creation code into a separate procedure. The entire
CMenuHandlerclass is shown here:
Option Explicit
‘A variable to hook the click event for all our menus.
Private WithEvents mbtnEvents As Office.CommandBarButton
‘A unique tag to identify our menus
Private Const msTAG As String = “Excel2007VBEWorkbookTools”
‘’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’
‘ Subroutine: Class_Initialize
‘
‘ Purpose: Called when the class is created in the Workbook_Open event,
‘ this procedure creates the menus for the Add-In
‘
Private Sub Class_Initialize()
‘Just in case some of our menus got left behind, remove any previous remnants
DeleteMenus
‘We’ll add our menus here, later in the chapter
‘Associate our event-hook variable with any one of our menus.
On Error Resume Next ‘In case we don’t find any
Set mbtnEvents = Application.VBE.CommandBars.FindControl( _
Type:=msoControlButton, Tag:=msTAG)
End Sub
‘’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’
‘ Subroutine: DeleteMenus
‘
‘ Purpose: Find all the menus with our unique Tag and delete them
577
Chapter 26: Programming the VBE
Documents you may be interested
Documents you may be interested
