XL97: Cannot Use User-Defined Function in Demand-Loaded Add-in

• Microsoft Excel 97 for Windows

SYMPTOMS

When you view the list of user-defined functions in the Function Wizard in Microsoft Excel 97, one or more function names may be missing. In addition, a function name may appear garbled.

CAUSE

This behavior occurs if the following conditions are true:

• You save multiple user-defined functions in an add-in file.

      -and-
  

• You define the demand-loaded name, "__DemandLoad" (without the quotation marks) and set its value to "=TRUE" (without the quotation marks) in the add-in file.

      -and-
  

• You assign the user-defined functions to the User Defined category.

WORKAROUND

To resolve this problem, first obtain and install Microsoft Excel 97 Service Release 1 (SR-1). Then, if you saved the add-in in Microsoft Excel 97, perform the additional steps in "Add-ins Saved from Microsoft Excel 97" section to resolve the problem.

Add-ins Saved from Microsoft Excel 97

  1. On the File menu in Microsoft Excel 97 SR-1, click Open. In the
     Open dialog box, click the add-in file that you want to convert
     (for example, Test.xla), and then click Open.

  2. On the Tools menu, point to Macro, and then click Visual Basic
     Editor (or press ALT+F11).

     In the Project Explorer window in the Visual Basic Editor, entries
     similar to the following appear

        <Project name> (<Add-in file name>)

     where <Project name> is the name of the Visual Basic project, and
     <Add-in file name> is the name of an add-in file.

     For example, entries similar to the following appear:

        MyAddIn (Test)

  3. Click the plus sign (+) to the left of the entry for the add-in
     file.

  4. If you are prompted to type a password, type the password and click
     OK. Otherwise, skip to step 5.

     NOTE: If you do not know the password for the add-in file, you
     cannot convert it into a normal workbook. If you know who
     created the add-in file, ask the creator for the password.
     Otherwise, you cannot continue.

     Entries similar to the following appear in the Project Explorer
     window:

        MyAddIn (Test)
            Microsoft Excel Objects
            Modules

  5. Click the plus sign (+) to the left of "Microsoft Excel Objects."

     The tree should appear similar to the following:

        MyAddIn (Test)
            Microsoft Excel Objects
                Sheet1 (Sheet1)
                Sheet2 (Sheet2)
                Sheet3 (Sheet3)
                ThisWorkbook
            Modules

  6. Click ThisWorkbook.

  7. In the Properties window of the Visual Basic Editor, click IsAddin.
     In the box to the right of IsAddin, change the value from True to
     False.

  8. On the File menu, click "Close and Return to Microsoft Excel."

     The worksheets and chart sheets in the add-in file (if it contains
     any) should be visible.

  9. On the File menu, click Save As.

 10. In the Save As dialog box, click "Microsoft Excel Add-in" in the
     "Save as type" box. Type a new name for the add-in (for example,
     "Test.xla") in the file name box. Click Save to resave the add-in
     file.

 11. After you save the workbook, click Close on the File menu.

 12. Quit and restart Microsoft Excel 97.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Excel 97 for Windows. This problem was corrected in Microsoft Excel 97 SR-1.

For additional information about SR-1, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q172475
   TITLE     : OFF97: How to Obtain and Install MS Office 97 SR-1

MORE INFORMATION

For additional information about creating an add-in from Excel 97, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q156942
   TITLE     : XL97: How to Create an Add-in File in Microsoft Excel 97

   ARTICLE-ID: Q120796
   TITLE     : XL: How to Create a Demand-Loaded Add-in File