Very simple ActiveSheet.name question

[Guest]

I want to write a function that will return the name of the sheet where the function is entered. ie: I want to enter "=sheetname()" into cell A2 of "Sheet1" and have cell A2 display "Sheet1".

When I try the following I get the #Value error.

Function sheetname()
ActiveSheet.name
End Function

Am I missing something in regards to how functions return values? Do I need to use a variable and return the variable?

 

[JE McGimpsey]

you can use a UDF:

Public Function SheetName()
Application.Volatile
SheetName = Application.Caller.Parent.Name
End Function

or you can use a built-in function:


=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

In the UDF, the Application.Volatile is required or it won't update when
the sheet name is changed.

 

[Guest]

Thanks JE
I actually answered my own question about 3 minutes after posting it, but couldn't figure out how to get it to auto update. Thanks for the "volatile" tip.