Microsoft KB Archive/100614

{|
 * width="100%"|

XL: Add-in Macro Fails to Load when Starting Excel

 * }

-

The information in this article applies to:


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

-

SYMPTOMS
When you automatically load an add-in macro sheet, the add-in may appear to fail to load. All of the add-in custom functions appear in the Formula Paste Function dialog box, but none of its custom menu commands appear.

The primary method for automatically loading a document when you start Microsoft Excel is to place the file in your startup directory:


 * XLSTART directory (for Microsoft Excel for Windows)

-or-
 * Excel Startup Folder (4) (for Microsoft Excel for the Macintosh).

Or, you can use an OPEN= statement in the settings file: EXCEL4.INI file (Excel 4.0 for Windows), EXCEL5.INI file (Excel 5.0 for Windows), or Excel Settings (4) file (Excel 4.0 for the Macintosh).

CAUSE
This problem occurs when the name __DemandLoad is defined in the add-in sheet and either of the following is true:


 * The add-in macro sheet is located in the startup directory

-or-
 * The add-in macro is set to load in the settings file by using an OPEN= statement that contains the /F switch.

NOTE: this information applies to Microsoft Excel 5.0 only when you use the version 4.0 macro language.

MORE INFORMATION
When the name __DemandLoad (note that the string "DemandLoad" is preceded by two underscores) is defined on the add-in macro sheet, the add-in file is not actually loaded until you open it from the Open dialog box or until one of its custom functions is entered into a sheet or is recalculated. In essence, the add-in is loaded into memory upon demand. This saves time and memory when you start Microsoft Excel.

Because the add-in macro is not loaded, any auto open macros that add custom menu commands or run other command macros are not executed. Only custom functions are added when __DemandLoad is defined on your sheet. If your add-in macro sheet contains both types of macros, you can remove the defined name __DemandLoad from your add-in sheet and save it to your startup directory. When you start Microsoft Excel, this loads the add-in, executes any auto open macros, and adds custom functions to the Paste Function dialog box.

Alternatively, if you want the add-in macro to load on demand when one of its custom functions or custom menu commands is called, in addition to __DemandLoad, add the defined names __Command and/or __Menu, to your add-in sheet and then use the Add-in Manager to add your add-in to the startup list contained in your EXCEL4.INI file (Windows) or Excel Settings (4) file (Macintosh). The Add-in Manager checks for these defined names and performs the specified action.

__DemandLoad
When this name is defined on the add-in sheet, the Add-in Manager adds an OPEN= line with the /F switch to the Microsoft Excel section in your settings file. The OPEN= line contains the path and name of your add-in macro. /F is a fast load option switch that, when used in conjunction with a file containing the defined name __DemandLoad, places all of the custom functions in the Paste Function dialog box. Note that if the add-in macro sheet does not contain the defined name, the add-in file is fully loaded when you start Microsoft Excel.

The definition for __DemandLoad is not important. In the add-ins that are built into Microsoft Excel, it is commonly defined as the Boolean value, TRUE. When defined as FALSE, however, it functions the same.

__Command
When this name is defined on the add-in sheet, the Add-in Manager adds your custom commands to specified menus when you start Microsoft Excel. Similar to __DemandLoad, the add-in macro sheet is not actually loaded until you select one of the commands. This saves time and memory when you start Microsoft Excel.

The Add-in Manager adds the information necessary to add the custom menu commands to the Init Commands section of your settings file. __Command must refer to a horizontal array that contains 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 information in cells and define __Command as =$B$1:$E$1 (in the Refers To box),

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

-or-

you can use an array formula as follows:

   ={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
Use this defined name when your add-in creates a custom menu. The Add-in Manager adds the information necessary to add the custom menu 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 of the menu on the bar

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