adding a user defined function to the library

[aa]

In Excel-2000 in the list of function categories there is "user defined"
category.
If I have written a function how do I add it to the list?
Also in which language this function is to be written?
Is this a script or a compiled language?

 

[Pete_UK]

You write the function in VBA - it differs from a macro in returning
one result, and can't affect formatting of cells. It is normally used
in the worksheet it is written for, but you could save it to your
Personal.xls to make it more widely available. If you had a lot of
them, then you could set them up as an add-in. You would use it in a
worksheet like:

=my_function(parameter list)

to return a particular result.

Do a search of these newsgroups looking for UDF and you will see many
other examples.

Hope this helps.

Pete

 

[aa]

Thanks, it helps

If it is VBA - do I need to compile it?
Where do I save the function to be used in a particular worksheet?

 

[Pete_UK]

A quick Google search for UDF in this newsgroup revealed 240+
responses. Here is one from Gord Dibben on 7th December 2003 which
answers most of the points:

" ...

1. UDF's and Macros can be stored in a workbook so they are available
only
for that workbook.


2. They can be stored in a Personal Macro Workbook that is placed in
the
XLSTART folder so they are available for all open workbooks.


3. They can be stored in a workbook which has been saved as an Add-in
with
the extension *.XLA


I prefer putting my macros/functions into a personal Add-in(*.XLA) and
having
that loaded whenever Excel is opened.


The reason I favor the Add-in route is that you rarely have a problem
with
Excel finding the macros/functions when referred to by name only,
without
preceding with Personal.xls.!


One minor problem with an Add-in is that the macros don't show up in
the
Tools>Macro>Macros list.


The UDF's you write will be available in the Function Wizard in
"User Defined" category.


If you have no Personal.xls one can be built easily.


On menu go to Tools>Macro>Record New Macro.


You will be presented with a dialog box with Macro1 as name of macro.
Below
that is "Store macro in". Select Personal Macro Workbook.


Record something simple like copying a cell and pasting. Stop
Recording.
Close Excel and you will be asked if you want to save changes to
Personal.xls


Click Yes. Excel will save it to your XLSTART folder.


You now have a Personal.xls which will open each time you start
Excel. When it is open, you can go to Windows>Hide and make it
hidden. Save
changes again. The macros you create will be available to work in
all
workbooks.


At any time Personal.xls can be saved as an Add-in through File>Save
As>Type>Excel Add-in(*.xla) and stored in the Office\Library folder.
It will
then show up in the list in Tools>Add-ins to be checked.


Gord Dibben

.... "

Hope this helps.

Pete

 

[aa]

Thanks a lot

 

[aa]

On my computer I have two XLSTART folders:
C:\Documents and Settings\?????????????\Application
Data\Microsoft\Excel\XLSTART (this folder contains one file called
PDFMaker.xla)
and
C:\Program Files\Microsoft Office\Office\XLStart (also with one file called
PDFMaker.xla)

Which one shall I use?