CODEWITHSUNDEEP
excelvba
Gary
Gary

Reputation: 2007

How can I determine the sheet name in a workbook that my function is sitting?

Imagine I have a xls workbook containing 3 tabs, SheetA, SheetB, and SheetC. I want to write a simple VBA function that returns the name of the sheet where the function is called.

Function SheetName as String
    SheetName = ???
End Function

So if I call =SheetName() in Tab SheetB, it will ALWAYS return SheetB.

Note:

 ActiveSheet.Name

doesn't work because if you are on the SheetA and calculate the workbook, it will return SheetA.

Upvotes: 2

Views: 416

Answers (2)

RBarryYoung
RBarryYoung

Reputation: 56725

This works:

Function SheetName as String
    SheetName = Application.Caller.Worksheet.Name
End Function

I should mention that as a practical matter, you should be very cautious about using caller-sensitive features like this in any function. Later on when you (or worse, someone else) are trying to debug something, it can be a real nightmare if you don't realize that that the function acts differently when called from code or in the debugger/immediate window, than it does in actual use.

Upvotes: 3

Joseph
Joseph

Reputation: 5160

You could do something like this:

Function sheetName(rng As Range) As String
    sheetName = rng.Parent.Name
End Function

Just pass the range the sheet is calling from (even the same range the formula is in).

In sheet1,

=sheetName(A1)

returns Sheet1

In Sheet2

=sheetName(Sheet1!A1)

Returns Sheet1 as well.

Upvotes: 2

Related Questions