Application.Caller object required error

Question

How to get current sheet (not activesheet) name from function? By "current sheet" I mean sheet name where function placed and called from.

I trying get like that

Function MySheet()

   MySheet = Application.Caller.Worksheet.Name
MsgBox MySheet
End Function

but i get error object required.

Answer 1

You are going to have to be careful in how you call that function. You've supplied very little detail on what you want to do with it.

Function MySheet()
    Select Case TypeName(Application.Caller)
        Case "Range"
            MySheet = Application.Caller.Parent.Name
        Case "String"
            MySheet = Application.Caller
        Case "Error"
            MySheet = "Error"
        Case Else
            MySheet = "unknown"
    End Select
End Function

Th above at least attempts to make some sort of determination of what Application.Caller is before using it to determine the associated worksheet object's name.

You can read more at Application.Caller Property (Excel).

Answer 2

Is it Me you're looking for?

http://www.formulaoldies.com/uploads/2012/06/Lionel-Richie.jpg

MsgBox Me.Name

This is what you want if by "sheet name where function placed" you mean, the Sheet module in which the function code is placed. (Documentation).

However, if you mean, the sheet containing the cell from which the user-defined function is called, you can use:

MsgBox Application.Caller.Parent.Name

Application.Caller returns a Range object referring to the cell; this range's Parent is then the sheet.