GetSheetName = ActiveSheet.Name

That is probably not what you want. With that code, the function will
return the name of whatever sheet happens to be active when Excel
decides it is time to calculate. There is no certainty that
ActiveSheet will be the same as the sheet on which the formula
resides. This can cause problems that may be hard to diagnose.

Instead, use Application.Caller, which, when called from a worksheet
cell, will return a Range reference to the cell whence the function
was called.

Function GetSheetName() As String
GetSheetName = Application.Caller.Worksheet.Name
End Function


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Wed, 8 July 2009 08:44:26 -0700 (PDT), "michael.beckinsale"
wrote:

On Jul 8, 9:52*am, jodleren wrote:
Hello

I have a small function, so I can get the name of the present sheet...

It goes:
Function GetSheetName() As String
'ByVal Sheet As Worksheet
* GetSheetName = ActiveSheet.Name
End Function

and as formula it is used:
=GetSheetName();

however, that does not work in excel 2007
why?

WBR
Sonnich

jodleren

Amend as below so that the function responds to changes:

Function GetSheetName() As String
Application.Volatile
GetSheetName = ActiveSheet.Name
End Function

Another way is as follows:

Function GetSheetName As String
Application.Volatile
GetSheetName = Application.Caller.Parent.Name
End Function

Both the above tested with XL2003 & XL2007

Regards

Michael Beckinsale