0

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.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Dmitrij Holkin
  • 1,995
  • 3
  • 39
  • 86

2 Answers2

3

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).

  • What are the "String" and "Error" cases for? Can the caller ever be a String?? – Jean-François Corbett Mar 16 '15 at 08:06
  • @Jean-FrançoisCorbett - According to the MSDN doc I linked to *"An Auto_Open, Auto_Close, Auto_Activate, or Auto_Deactivate macro"* returns *"The name of the document as text"* and *"A macro set by either the OnDoubleClick or OnEntry property"* returns *"The name of the chart object identifier or cell reference (if applicable) to which the macro applies"*. –  Mar 16 '15 at 08:09
  • Aha, but a `Function` isn't a macro. It can't be assigned to a chart or called in any of those other ways you list. Only `Case "Range"` will ever apply; as it stands, the rest is dead code. – Jean-François Corbett Mar 16 '15 at 08:13
  • @Jean-FrançoisCorbett - The original question has been clarified with an edit but I was trying to cover many possible methods. TBH, I don't know offhand what would happen if the function was called from within an `Auto_Open` macro but wanted to cover as many bases as I reasonably could. –  Mar 16 '15 at 08:21
  • @Jean-FrançoisCorbett Actually a function can be assigned as a macro to any shape - you just have to type its name into the macro dialog. And in such a case, `Application.Caller` is indeed a string. ;) However, if you assign it using `Application.OnDoubleClick` for example, you actually get a Range object, not a String. – Rory Mar 16 '15 at 10:06
1

Is it Me you're looking for?

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.

Jean-François Corbett
  • 37,420
  • 30
  • 139
  • 188