user defined functions

[docmurali]

Dear all,
I wrote an user defined function in excel 2000 to calculate certai
values pertaining to my job as purchase manager for a health programme
The same VBA code works in immediate mode in the VBA editor. But it i
not listed in the list of functions in the formula dialog box (eg Whe
= is typed in a cell) the name of the function is not displayed in th
user defined or custom category.
a)What are the steps to register this function for this worksheet?
b)what should I do to make it available for all other worksheets?
I would appreciate anyhelp in this matter. Could it be that securit
levels for macros have to be changed?

Docmural

 

[Bob Phillips]

The UDF must be in a standard code module, not a worksheet class, workbook
class, userform or other class module.

It will be available as long as the workbook with UDF is open, but other
workbooks will need to prefix it with the workbook name. A common practice
is to put in in Personal.xls.

Another approach is to put it in an addin, and install that.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[David McRitchie]

Unless you make up a reference, you will need to prefix your
user defined macro even though it is in your personal.xls workbook.
=personal.xls!myfunction(A2,C2)

Getting Started with Macros and User Defined Functions
User Defined Functions (#UDF)
http://www.mvps.org/dmcritchie/excel/getstarted.htm#udf

Your own User Defined Functions will appear under the
"User Defined" topic near the bottom of the Paste Function (wizard)
seen as Fx on the toolbar. Since you cannot easily put
a description on each parameter, it would be wise to name
the parameters with meanful names.

Security level should be set to "Medium". A "High" security
level prevents macros and your UDFs from running. If you
are running your macros, you should no problem here.
Tools, macro, security (tab), level set to Medium