How about:

Function sn()
sn = application.caller.parent.Name
End Function

Application.caller is the cell with the formula. The cell's parent is the
worksheet.

But you could use this, too:

http://www.contextures.com/xlfaqFun.html#SheetName
If the workbook has been saved.

(From Debra Dalgleish's site)

Lynn wrote:

I have over 100 sheets in my workbook & need to refer to the sheet name in a
cell on each worksheet. I created the following function:

Function sn()
sn = ActiveSheet.Name
End Function

I grouped my sheets & entered the formula. On all sheets it displayed the
name of the first sheet. When I double click the cell & press enter, it
updates but this defeats the purpose of creating the function - I will still
have to go to each sheet & edit the formula cell.

Also - if I change the name of a sheet, the function does not display the
new name.

Is there some trick to updating custom functions?

--

Dave Peterson