Figure Data Sheet of SRXUtilsxls









QnArtLjIl Pr«




On^rtlir Umn

n ThFri MMj


-jï:-' I» i xo:

:-.n_: ty

Ih ■:■■■■■>■ :




"mi Ü .il:

Rull (7

n r r^ùL,

• ■ A


fil.--: .r?



: Iiir.: "V. J*:


1 rir.: M:lMjto


i -V Ij lri



■ Pi:






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

By using these constants throughout the add-in, if we need to move any columns in the DataSheet sheet, all we need to do is change the values of these constants. (This is precisely what symbolic constants are for!)

0 0

Post a comment