Excel 2003 - User def. function

[KSO]

I have defined a function like

Function GetSheetName() as String
Application.Volatile ' I tried this but no difference
GetSheetName=ActiveSheet.Name
End func

and when I use it in a cell on a workheet - then
sometimes it works and sometimes it won't - why ?

KS, Denmark

 

[Niek Otten]

That is not a safe way to get the name of the sheet where the formula is; some other sheet might be active.

Use

Function SheetName() As String
SheetName = Application.Caller.SheetName
End Function

 

[KSO]

Hi Niek

It won't work either - I get runtime error 424

But I can see it's NOT a safe metode.

KS, Denmark

 

[Niek Otten]

Do you put the code in a General Module? It should not be in a Sheet module. I didn't get the error.

 

[KSO]

I have to use the function on 10 defferent sheets so I placed the code in a
added module - I think that's what you'r poiting out - but still I get run
time error 424

 

[Niek Otten]

Sorry for that; SheetName turned out to be already in Laurent Longre's Morefunc add-in and that one was called in my case. Mine
uses wrong syntax.

 

[KSO]

I can now see the problem to know witch sheet the functions is called from,
but Application.Caller don't give me some usefull information here - then it
can't be done without installing this add-in you'r talking about ....

Any other idias ?

KS, Denmark

 

[Niek Otten]

This one seems to work. It needs a cell on that sheet as argument

Function test(a As Range)
Application.Volatile
test = Application.Caller.Worksheet.Name
End Function

But you may find Morefunc useful anyway; have a look!

http://xcell05.free.fr/

 

[Niek Otten]

Too early up for a Sunday. I'll get back to bed.
Of course you don't need the argument.