user defined functions

[Guest]

Hiya,

I created a user defined function in the module of personal.xls. Thing is I need to call the function like this to get it to work: =personal.xls!MyFunction(A1).

Where should I put it so that I can simply call =MyFunction(A1) ?

All the xla's seem protected.

Baz

 

[Rob van Gelder]

Option is to store all UDF within the workbook. But I guess you're after
storing them externally.

You could create a VBA reference to the XLA. This works alright for me.

To truly get rid of the qualifier you create an XLL.

Excel XP + has an Automation Add-In which is nice, but not as nice as XLL
(in my opinion). Before Recalc you get to see formulas qualified with the
ProgID.

--
Rob van Gelder - http://www.vangelder.co.nz/excel

Hiya,

I created a user defined function in the module of personal.xls. Thing is

I need to call the function like this to get it to work:
=personal.xls!MyFunction(A1).

 

[Guest]

I want the UDF available to any workbook created or opened.

You could create a VBA reference to the XLA. This works alright for me.

All the XLAs (add-ins I assume) are protected, and I don't know how to create a new one for myself. So I don't really understand what you mean by the above or how to implement it?

To truly get rid of the qualifier you create an XLL.

I'm on office 2000, how can I create an XLL?/where is it stored? how is it referenced?

Thanks for your help Rob.

Basil

 

[Gord Dibben]

Basil

To save a workbook as an add-in........

File>Save As>File Type>Microsoft Excel Add-in(*.xla)

You will find this file type at the bottom of the drop-down.

Name it something descriptive like MyAddin

Save it to your Office\Library folder.

Go to Tools>Add-ins and checkmark it.

UDF's can now be accessed directly =MyUDF(A1)

Gord Dibben Excel MVP