Functions

[Vsn]

Hello all,

Can some one tell me what I do wrong, I am new in automating Excel.

I made a VB(A) function which should return the name of the day given by a
date.

Function fDayName(dteDate as Variant)
'Code
End Function

I did save it in the template 'Personal.xls '

I thought I could use it now in any cell on the spreadsheet like
=fDayName(A1), as you use the normal build in functions of Excel.
Unfortunately it returns with an error #NAME? If I open Excel and go into
the VB editor I can see the file "Personal.xls' loaded as well I can call
the function from the immediate window.

What am I doing wrong here?

Thx for your help.

Ludovic

 

[Don Guillett]

To use a UDF from any other workbook including personal.xls use
personal.xls!Function fDayName(dteDate as Variant)

 

[Vsn]

That is not a very nead methode, I will probably have to speak with Bill
about this.

But I works, thanks for you help.

Ludovic

 

[Gord Dibben]

Ludovic

You need to add the filename in this case.

Try =Personal.xls!fDayName(A1)

Gord Dibben Excel MVP

 

[Gord Dibben]

Ludovic

If you save your Personal.xls as an Add-in and reference the UDF from there,
you do not have to preface with the filename.

Gord Dibben Excel MVP

 

[SidBord]

The technique I use is to click the TOOLS tab in the VB
Editor, and click on REFERENCES, then make sure the
Personal.xls workbook is searched by clicking the checkbox
next to VBAProject. Then ALL workbooks can reference
anything that's in Personal.xls. I've used this method
for years. However, if you get introuble with a VBAProject
naming conflict, you'd better send me an Email note, and
I'll send you the somewhat involved procedure to get rid of
the conflict.
Sid
(e-mail address removed)

 

[Vsn]

Gordon,

Maybe very simple but could you tell me how I can save the 'Personnal.xla'
file as an Add-in?

Thx for your advice.

Ludovic

 

[Dave Peterson]

File|SaveAs
scroll down in the "Save As type" box to "Microsoft Excel Add-In (*.xla)"

Then change the folder back to where you want to save the file.

Remember to only keep personal.xls or personal.xla in your xlstart folder. (You
don't want both there.)