XL2000: Cannot See User-Defined Function in the Paste Function Dialog Box (277017)


The information in this article applies to:

  • Microsoft Excel 2000
This article was previously published under Q277017

SYMPTOMS

After you created a custom function in Microsoft Excel, your function may not appear as a user-defined function in the Paste Function dialog box.

CAUSE

This behavior occurs when you place the custom function in a worksheet code window or in a code window assigned to the Workbook event rather than placing the function code in a Public module.

RESOLUTION

In order to have your function appear as a user-defined function in the Paste Function dialog box, move the custom function code to a Public module sheet.

To move the function code to a Public module sheet, follow these steps:
  1. Start Microsoft Excel and press ALT+F11 to start the Visual Basic Editor.
  2. In the Project Explorer window, double-click the Worksheet or Workbook code window where the custom function code is located.
  3. Select the function code, and then on the Edit menu, click Cut.
  4. On the Insert menu, click Module.
  5. On the Edit menu, click Paste to insert the function code in the public module you just created.
  6. On the File menu, click Close and Return to Microsoft Excel.
  7. On the Insert menu, click Function, and then select User Defined in the Function Category list. Notice that you can see your custom function.

MORE INFORMATION

In Microsoft Excel 2000, you can create a custom Microsoft Visual Basic for Applications function to perform specialized calculations. Custom functions should typically be placed in a Public module if you intend to use the function from additional spreadsheets or modules. When you place a custom function on a code sheet that belongs to a Worksheet or to a code sheet that belongs to the Workbook, the scope of the function will be limited to that code sheet. This means that all other components of Excel cannot see or use that code by default.

REFERENCES

For additional information about custom functions, click the article number below to view the article in the Microsoft Knowledge Base:

213645 XL2000: How to Use a Custom Function in Another Workbook

Modification Type:MajorLast Reviewed:11/5/2003
Keywords:kbprb KB277017
©2003 Microsoft Corporation. All rights reserved.