There are two kinds of Excel people - The ones who use formulas in the spreadsheet exclusively and the ones who use VBA exclusively. Ideally - the complete excel programmer should be well versed in both.

But I'm a VBA person and here's how I'd get the sheet name:

Sub SheetName()
Dim S As String
S = ActiveSheet.Name
MsgBox (S)
End Sub
I've seen formulas in the sheet that also display the name but I didn't save them.

Hopefully, a formula person or a Compleat Excel Programmer will also hit on this thread.