Demand Loading Add-Ins

To increase the speed with which Microsoft Excel starts, you can have an add-in be demand loaded. This means that when Microsoft Excel starts, it reads only enough information from the add-in workbook file to establish references to the custom worksheet functions in the add-in. These functions are added to the list in the Function Wizard and can be entered on a worksheet. However, the add-in isn't fully loaded until Microsoft Excel recalculates one of the custom worksheet functions.

Demand loading can significantly decrease the amount of time required to start Microsoft Excel, especially when you're working with large add-ins that contain numerous custom functions. Nothing is free of course, and the time delay eventually occurs when the add-in is fully loaded at the time of the initial function recalculation. In most cases, this is an acceptable tradeoff because the user encounters individual time delays one by one instead of encountering one long time delay at startup time.

To use demand loading for custom worksheet functions that are written in Visual Basic, you must wrap the Visual Basic function procedure in a Microsoft Excel 4.0 macro function. You must also define the name __DemandLoad (the string "DemandLoad" preceded by two underscores) in the workbook. Use the following steps to create an add-in that contains demand-loaded custom functions written in Visual Basic.

To create a demand-loaded add-in

1. Create a source workbook.

2. Insert a Visual Basic module, and then write the custom worksheet function.

3. Insert a Microsoft Excel 4.0 macro sheet. For each argument to the Visual Basic function, create an ARGUMENT function on the macro sheet.

4. The last function on the macro sheet is the RETURN function. In the argument list for this function, type the name of the Visual Basic function, including the argument strings you defined in step 3.

5. On the Insert menu, point to Name, and then click Define to define a name for the first ARGUMENT function. This will be the name that you'll use to call the custom function (the Visual Basic function name is known only to the RETURN function on the macro sheet). Be sure to click Function under Macro.

6. On the Insert menu, point to Name, and then click Define to define the name __DemandLoad (the string "DemandLoad" preceded by two underscores) on the macro sheet. In the Refers To box, type =TRUE

7. Save the source workbook.

8. On the Tools menu, click Make Add-In to compile the workbook to an add-in.

9. Close the add-in.

To use the add-in, install it by using the Add-Ins dialog box (Tools menu). Click Browse to locate an add-in that's not listed in the Add-Ins Available box. After the add-in is installed, the custom function name should appear in the User Defined category in the Function Wizard. The following illustration shows an example of a demand-loaded custom function called "vb_hyp," which calculates the length of the hypotenuse of a right triangle, given the lengths of the other two sides.

When you install an add-in that contains demand-loaded custom functions, there will be an OPEN value in the Microsoft Excel key in the system registry that points to the add-in workbook, and the OPEN value will contain the /F switch to indicate demand loading. Add-ins that aren't demand loaded don't have the /F switch in the OPEN value. For more information about the system registry, see "Removing an Add-In from the List" in the following section.