Microsoft KB Archive/100616

{|
 * width="100%"|

XL: Controlling How Add-in Macros Are Loaded During Startup

 * }

-

The information in this article applies to:


 * Microsoft Excel for Windows, versions 4.0, 4.0a, 5.0
 * Microsoft Excel for the Macintosh, versions 4.0, 5.0

-

SUMMARY
In Microsoft Excel, you can use the Add-in Manager to select add-in macros that you want to have available when you start Microsoft Excel.

With the Add-in Manager, you can use add-ins that are supplied with Microsoft Excel or you can use add-ins that you've created on your own. The Add-in Manager sets up the add-in to either load or have its custom commands and functions available in Microsoft Excel upon startup. In the latter case, the add-in is not actually loaded until you select one of its custom commands or functions. This kind of loading saves time and memory when you start Microsoft Excel.

The Add-in Manager sets up the way your add-in loads by writing appropriate commands to the settings file. (In Microsoft Excel for Windows, this is the EXCEL4.INI or EXCEL5.INI file, depending on the version you use and in Microsoft Excel for the Macintosh, this is the Excel Settings (4) file.) The commands that are written to your settings file are determined by defined names on your add-in macro sheet.

MORE INFORMATION
To add your add-in macro sheet to the Add-in Manager so that it is available when you start Microsoft Excel, choose Add-ins from the Options menu and select the Add button. Select the add-in filename, choose OK and then choose Close.

When you add an add-in to the Add-in Manager, the Add-in Manager checks for the following defined names and performs the specified action.

NOTE: The names that are preceded by a double underline; this is how they should be entered into the Name box in the Define Name dialog box. One or all of these names can be used on the same add-in macro sheet.

__DemandLoad
Use this defined name when your add-in contains custom function macros. When you add your add-in to the Add-in Manager startup list, an OPEN= statement containing a /F switch plus the name of your add-in and its path is written to the Microsoft Excel section of the settings file. For example, in the following statement

  OPEN=/F C:\EXCEL\ANALYSF.XLA

the /F switch places all of the custom functions contained on the add-in sheet in the Paste Function dialog box and __DemandLoad prevents the add-in from loading until you recalculate or enter one of its functions. If your add-in macro also contains command macros, you must use the __Command and/or __Menu defined names to have your custom commands added to the appropriate menus.

  NOTE: How you define __DemandLoad in the Refers To box in the Define Name dialog box is not important; you can use the Boolean value of TRUE, for example. If you use a value of FALSE, __DemandLoad behaves the same as if you use a value of TRUE.

__Command
Use this defined name when your add-in contains command macros that you run using custom menu commands. The custom commands are added to specified menus when you start Microsoft Excel (although the add-in macro sheet is not actually loaded until you select one of the commands). This loading procedure saves time and memory when you start Microsoft Excel.

The Add-in Manager writes your __Command definition in the appropriate format to the Init Commands section of your settings file. Entries in the Init Commands section add custom commands to specified menus. The definition for __Command must refer to a horizontal array containing the following information (only the first 4 are required):

     Menu bar number     Menu the command should be added to      Name of the command to be added    Name of the macro to run when command is selected      Position of command on the menu           Macro shortcut key        Message to be displayed on the Status bar          Help topic number

The array can be a cell range or an array formula. Assume your add-in contains a SalesRpt macro that runs when you select Create Report from the Options menu. You can enter the following and define __Command as =$B$1:$E$1 (placed in the Refers To box in the Define Name dialog box),

   B1: 1   C1: Options   D1: Create Report   E1: SalesRpt

or you can enter an array formula in the Refers To box:

   ={1,"Options","Create Report","SalesRpt"}

If you have more than one custom command to add, use sequential names beginning with __Command, then __Command1, __Command2, and so on.

__Menu
The __Command defined name must be defined on the add-in sheet for the __Menu definition to be added to the appropriate section of the settings file.

Use this defined name when your add-in creates a custom menu. The Add- in Manager writes the __Menu definition in the appropriate format to the Init Menus section of your settings file. Like the definition for __Command, __Menu must refer to a horizontal cell range or array formula containing the following information:

    Number of the bar you want to add the menu to         The name of your menu <Position>     Position of the menu on the bar

For example, if you want to add MyMenu to the right of the File menu on bar 1, you can enter the following and define __Menu as =$B$1:$D$1 (placed in the Refers To box):

<pre class="FIXEDTEXT">   B1: 1   C1: MyMenu   D1: 2

or you can enter an array formula in the Refers To box:

<pre class="FIXEDTEXT">   ={1,"MyMenu",2}

Again, if you have more than one custom menu to add, use sequential names beginning with __Menu, __Menu1, __Menu2, and so on.

__DeleteCommand
The __Command defined name must be defined on the add-in sheet for the __DeleteCommand definition to be added to the appropriate section of the settings file.

Use this defined name to specify built-in Microsoft Excel menu commands that you want to delete. The Add-in Manager writes the __DeleteCommand definition in the appropriate format to the Delete Commands section of your settings file. Like __Command and __Menu, the definition must refer to a horizontal cell range or array containing the following information:

<pre class="FIXEDTEXT">  <Menu_Bar_Num>  Number of the bar containing the menu from which

you want to remove the command.

<Menu>         The name of the menu containing the command you want to remove.

<Position>     Position of the command on the menu to be removed

For example, if you want to remove the New Window command on the Window menu, you can enter the following and define __DeleteCommand as =$B$1:$D$1 (in the Refers To box):

<pre class="FIXEDTEXT">  B1: 1   C1: Window   D1: 1

or you can enter an array formula in the Refers To box:

<pre class="FIXEDTEXT">  ={1,"Window",1}

Again, if you have more than one built-in command to delete, use sequential names beginning with __DeleteCommand, __DeleteCommand1, __DeleteCommand2, and so on.

__LongName
Use this defined name to specify the name you want to see in the Add-in Manager dialog box when you add your add-in to the startup list. __LongName can refer to a cell containing the extended name or it can be defined as a text string. No information is written to the settings file when this name is on the add-in.

If you want to see "My Add-in" in the Add-in Manager dialog box, enter My Add-in in cell B1 and define __LongName as =$B$1, or define __LongName as ="My Add-in".

__ReadOnly
This defined name designates that the add-in be read only and prevents you from changing the original file. The Add-in Manager creates an OPEN= line for your add-in with an /R switch if your add-in also contains __DemandLoad. If there is no __DemandLoad name, no information is written to the settings file. To work correctly, __ReadOnly must be defined as TRUE.

Each of these settings, with the exception of __LongName, can be added to the settings file manually. In the Microsoft Windows operating environment, you can add OPEN= statements, as well as these other settings, to the EXCEL4.INI or EXCEL5.INI file using a text editor such as Notepad. See the EXCELINI.TXT file for additional information. If you are using Microsoft Excel for the Macintosh, the Add-in Manager adds statements to the Excel Settings (4) file. To make modifications to the Excel Settings (4) file manually, you must use the ResEdit utility. ResEdit is available through Macintosh user groups, or the Apple Programmers and Developers Association (APDA).