How to Create a Global User-Defined Function (151490)


The information in this article applies to:

  • Microsoft Excel for Windows 95 7.0a
  • Microsoft Excel for Windows 5.0c
  • Microsoft Excel for the Macintosh 5.0a
This article was previously published under Q151490

SUMMARY

By default, a user-defined function is limited in scope to the workbook in which the function resides. In other words, only sheets within the same workbook can call a user-defined function. If you want to call a user- defined function from another workbook, you must first make a reference to the workbook that contains the user-defined function.

For information about using references to call user-defined functions, please see the following article in the Microsoft Knowledge Base:

141288 How to Use a Custom Function in Another Workbook

However, to be able to call a user-defined function from any file, new or existing, you have to manually create a reference in each and every workbook. To make your user-defined functions globally available, you can group them into one workbook and make an Add-in. With a module sheet active, click Make Add-in on the Tools menu. When the Add-in is loaded into memory, your functions will be available to any file.

MORE INFORMATION

The Add-in must be loaded into memory for your functions to be available. The Add-in can be placed in your XLSTART folder (directory),(usually "C:\MSOFFICE\EXCEL\XLSTART" (without the quotation marks) in Windows versions, and in the Excel Startup Folder on the Macintosh, either "System:Preferences:Excel Startup Folder (5)" or "System:Preferences:Excel Startup Folder" (without the quotation marks), or loaded through using the Add-in Manager.

To load an Add-In through the Add-In Manager, follow these steps:

  1. On the Tools menu, click Browse.
  2. Locate the file, click to select it, and then click OK.
Before making an Add-in, do the following:

  • Save your source file. Once the Add-in is created, it cannot be edited.
  • Remove any debugging code, comments, extraneous sheets, and so on -- anything that is not essential for the code to run. Leave this nonessential information in your source file.
  • You may specify a name for your Add-in.
To name an Add-In in Microsoft Excel version 5.0, follow these steps:

  1. On the File menu, click Summary Info.
  2. Type the name in the Title box, and click OK.
To name an Add-In in Microsoft Excel version 7.0, follow these steps:

  1. On the File menu, click Properties.
  2. Type the name in the Title box, and then click OK.
For information about creating a name and description for an Add-in, please see the following article in the Microsoft Knowledge Base:

108425 How to Create Add-in Application Name and Description

REFERENCES

"Microsoft Excel Visual Basic User's Guide," version 5.0, Chapter 13, "Creating an Add-in Application," and Chapter 3, "Creating User-Defined Functions."

For more information about Installing Add-ins, click Search, or the Index tab in Help and type:

Add-ins

For more information about User-defined Functions in Microsoft Excel 7.0, click the Index tab in Help and type:

User-Defined Function

Modification Type:MinorLast Reviewed:10/11/2006
Keywords:kbcode kbinfo kbProgramming KB151490
©2004 Microsoft Corporation. All rights reserved.