I'm not sure I see the purpose of such a function? You'd also need
to have write  some workbook or worksheet based events to trigger a
recalculation of the workbook as the active  sheet changes, since
changing the active worksheet doesn't automatically cause a
recalculation.

But, this should get you the active sheet  name at the time of any
recalculation (but "filename" only has a value if you've previously
saved the workbook):

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

If you just want to return the sheet name fo the worksheet the
function is located in, you'd use:

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