Microsoft Office Tutorials and References
In Depth Information
12.9.1 Creating the Data Worksheet
12.9.1 Creating the Data Worksheet
As an Excel application gets more complex, the associated menu gets more complex. Rather than
store all data directly in code, it makes sense to use a worksheet. Recall that add-in worksheets are
hidden from the user, so they are the perfect place to keep data for the add-in.
Open the SRXUtils.xls source workbook, delete all sheets but one, and name that sheet DataSheet.
Fill in the sheet as shown in Figure 12-5 . This sheet contains one row for each procedure (or
utility) of the add-in (we will add more rows later in the topic). The first row is for the
ActivateSheet utility whose code shell we included earlier. We will add code shells for the
other utilities a bit later. In later chapters, we will implement these utilities properly.
Figure 12-5. DataSheet of SRXUtils.xls
Let us take a closer look at the contents of DataSheet. The first column is the name of the utility.
This is not used outside of the sheet.
The second column is the name of the procedure that is activated when the utility is invoked by
the user through a menu item created by the add-in. In this case, all menu items fire the same
utility: RunUtility . This utility will determine the menu item that was clicked and call the
appropriate procedure.
The third column gives the location of this procedure. As you can see, we have placed the printing
procedures in a separate workbook called Print.utl . As an application gets more complex, you may
want to split it up into several workbooks. In this way, your add-in can be written to load a file
only when it is needed, thus saving resources. (In this example, we are splitting up the application
for demonstration purposes only. The printing utilities are not really complex enough to warrant a
separate workbook.)
The fourth column contains the caption for the menu item that will invoke the utility. Note the
ampersand character (&), which determines the menu hot key. For example, the ActivateSheet
menu item can be invoked using the A key. The fifth column gives the menu item name in case
there is a submenu. Thus, the print utilities are accessed through the Print submenu.
The final two columns determine whether the menu (or submenu) item will be enabled or disabled
when a worksheet or chartsheet is active. As we have seen, Excel uses a different main menu bar
when a worksheet is active (Worksheet Menu Bar) than when a chartsheet is active (Chart Menu
Bar). For a utility that pertains only to charts, for instance, we may not want the corresponding
menu item to be available from the Worksheet menu bar and vice-versa.
Next, you should create a new standard code module called basMain and place the following
constant declarations in the Declarations section:
Public Const Utility_Col = 1
Public Const OnAction_Col = 2
Public Const Procedure_Col = 3
Public Const InWorkbook_Col = 4
Public Const MenuItem_Col = 5
Public Const SubMenuItem_Col = 6
Public Const OnWksMenu_Col = 7
Public Const OnChartMenu_Col = 8
Search JabSto ::

Custom Search