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 and set its value to =TRUE 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:

Q172475 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:

Q156942 How to Create an Add-in File in Microsoft Excel 97

Q120796 How to Create a Demand-Loaded Add-in File

Additional query words: XL97 sr1

Keywords : kbtool xladdins
Version : WINDOWS:
Platform : WINDOWS
Issue type : kbbug