Example The Run Utility 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 & WkbName End If

' Run procedure

On Error GoTo ProcNotFound

Application.Run WkbName & "!" & ProcName Exit Sub

WkbNotFound:

MsgBox "Cannot find workbook " & WkbName & " in " & _

ThisWorkbook.Path, vbCritical, "Test Add-In" Exit Sub

ProcNotFound:

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.)

0 0

Post a comment