Microsoft Office Tutorials and References
In Depth Information
Function SHEETNAME(rng As Range) As String
‘ Returns the sheet name for rng
SHEETNAME = rng.Parent.Name
The following function is a variation on this theme. It does not use an argument; rather, it relies on the fact that
a function can determine the cell from which it was called by using Application.Caller.
Function SHEETNAME2() As String
Returns the sheet name of the cell that contains the func-
SHEETNAME2 = Application.Caller.Parent.Name
In this function, the Caller property of the Application object returns a Range object that corresponds to the cell
that contains the function. For example, suppose that you have the following formula in cell A1:
When the SHEETNAME2 function is executed, the Application.Caller property returns a Range object corres-
ponding to the cell that contains the function. The Parent property returns the Worksheet object, and the Name
property returns the name of the worksheet.
Excel 2013 includes a new function, SHEET. This function returns a sheet number
rather than a sheet name.
Returning a workbook name
The next function, WORKBOOKNAME, returns the name of the workbook. Notice that it uses the Parent prop-
erty twice. The first Parent property returns a Worksheet object, the second Parent property returns a Workbook
object, and the Name property returns the name of the workbook.
Function WORKBOOKNAME() As String
‘ Returns the workbook name of the cell that contains the
WORKBOOKNAME = Application.Caller.Parent.Parent.Name
Understanding object parents