Microsoft Office Tutorials and References
In Depth Information
Example 12-14. The RunUtility Procedure
the workbook and project properties, and lock the workbook from viewing) before saving it as an
We now need to implement the RunUtility procedure. This procedure, which should be placed
in the basMain code module, is shown in Example 12-14 .
Example 12-14. The RunUtility Procedure
Sub RunUtility()
' Use Tag and Parameter properties to find the procedure for
' the requested utility. Procedure name is in Tag property
' and workbook name is in the Parameter property.
' Use ActionControl to return the control.
Dim WkbName As String
Dim ProcName As String
WkbName = Application.CommandBars.ActionControl.Parameter
If WkbName = "" Or WkbName = "ThisWorkbook" Then _
WkbName = ThisWorkbook.Name
ProcName = Application.CommandBars.ActionControl.Tag
' Open workbook if necessary
On Error GoTo WkbNotFound
If Not IsBookOpen(WkbName) Then
Workbooks.Open ThisWorkbook.Path & Application.PathSeparator &
End If
' Run procedure
On Error GoTo ProcNotFound
Application.Run WkbName & "!" & ProcName
Exit Sub
MsgBox "Cannot find workbook " & WkbName & " in " & _
ThisWorkbook.Path, vbCritical, "Test Add-In"
Exit Sub
MsgBox "Cannot find procedure " & ProcName & " in " & _
WkbName, vbCritical, "Test Add-In"
Exit Sub
End Sub
Example 12-14 makes a call to the IsBookOpen function (which is shown in Example 12-15 ) to
see if the workbook containing the procedure is open. Perhaps the obvious choice for determining
whether or not a workbook is open is to look through the Workbooks collection, which is the
collection of all "open" workbooks (more on this in Chapter 17 ) . However, an add-in is hidden,
even from this collection. Fortunately, we can still refer to an add-in workbook by name, so we
just try to get this name using the line:
sName = Workbooks(sWkbName).Name
If this generates an error, we know that the workbook is not open. Otherwise, it will return the
name of the workbook. (Of course, we already knew the name in this case, but that doesn't matter.)
Search JabSto ::

Custom Search