Excel - User Defined Function 1

[mbarron]

I've created (copied from thread68-603752) a UDF and want to make it more "universal".

When I create it in a module for a workbook, I can reference it using:
=ShowFormula(G13)

If I put it in a module in the personal.xls I have to reference it using:
=Personal.xls!Module5.ShowFormula(G13)

I put it in the personal.xls module because that is where I've put other macros that I wanted to use for all my new workbooks. (I could be way off base and the help file has been of no help)

I want to be able to use the formula without the Personal.xls!Module5. Is this possible?

Mike

 

[Zathras]

AFAIK, no. You shouldn't need the "Module5" part, but you can't get away from the "Personal.xls!" part. Try using Insert/Function... from the menu and you will see what I mean.

 

[xlbo]

Unless you just copy it into a module in the workbook you want to use it in ie use Personal.xls as a "database" of useful functions / subs and simply do a copy / paste into a module in the workbook it is to be used in...

Rgds, Geoff
Si hoc legere scis, nimis eruditionis habes
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[mbarron]

Rats. I was hoping for a more convenient method.

Zathras,

I'm not AFAIK.....

The formula showed up the first time including the Module part using the Insert/Function..., after I re-opened Excel (as you mention) it was gone.

xlbo,
I'll keep the database option in mind.

Thanks for your responses.

Mike

 

[xlbo]

mBarron
AFAIK = As Far As I Know
[LOL] [LOL] [LOL]

Rgds, Geoff
Si hoc legere scis, nimis eruditionis habes
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[mbarron]

O = Oh Thanks for straightening that out for me.

I saw that Zathras used AFAIK when he responded to the thread I referenced. Obviously I didn't check the original posters handle.

Mike

 

[SkipVought]

There IS another AFAIK

Always Friday Afternoon I KRASH!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[luceze]

Mike,
You can save a workbook with your userdefined functions as a .xla file and then go to tools\addins and select that workbook. One note, the operating system you are using determines the location where you need to save the xla file. On my Windows 2000 machine it is "C:\Documents and Settings\eisom\Application Data\Microsoft\AddIns\". Where eisom is my username. You should be able to find it by using search or find

HTH,
Eric

 

[mbarron]

Thanks Eric! A star for you.



Mike

 

[PeterMoran]

Hi Mike,

Despite what you have been already advised above, if I understand your problem correctly then I think these articles may be what you want to see:

http://support.microsoft.com/default.aspx?scid=kb;en-us;q151490

titled Q151490 -- XL: How to Create a Global User-Defined Function

also the following reference may be useful if not repeating the above:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexcel9/html/xlcraddns.asp

I understand that if you put your custom functions into an addin, then you can use them just like any other Excel provided function, without the filename reference.

Good Luck!

Peter Moran
Two heads are always better than one