XL: How to Create a Global User-Defined Function

 Last reviewed: December 1, 1997
Article ID: Q151490
The information in this article applies to:
  • Microsoft Excel for Windows 95, versions 7.0, 7.0a
  • Microsoft Excel for Windows, versions 5.0, 5.0c
  • Microsoft Excel for Macintosh, versions 5.0, 5.0a

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: 

      ARTICLE-ID: Q141288
      TITLE     : 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: 

      ARTICLE-ID: Q108425
      TITLE     : 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

Additional query words: 5.00, 5.00a, 5.00c, 7.00, 7.00a
Keywords : kbcode kbprg PgmHowto
Version : WINDOWS: 5.0, 5.0c, 7.0, 7.0a; MACINTOSH: 5.0, 5.0a
Platform : MACINTOSH WINDOWS
Issue type : kbinfo

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: December 1, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.