The three user-defined functions we explain in this section place the name of a workbook into a cell, or the workbook's filename and path into a cell. The first two examples, MyName
and MyFullName
, do not take any arguments. The last one, SheetName
, is used in place of nesting the MID
and other functions inside the CELL
function to get the sheet name, a process that commonly would require the following unwieldy formula:
=MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,255)
As you can see, this requires quite a bit of typing for such a simple result, which is why we initially developed the SheetName
custom function.
To use this user-defined function, press Alt/Option-F11, select Insert » Module, and paste in the following code:
Function MyName( ) As String MyName = ThisWorkbook.Name End Function Function MyFullName( ) As String MyFullName = ThisWorkbook.FullName End Function Function SheetName(rAnyCell) Application.Volatile SheetName = rAnyCell.Parent.Name End Function
Save the function and close the window. The function will appear under User Defined in the Paste Function dialog (Shift-F3).
You can use the functions as shown in the figure. They take no arguments. The formulas in column A are shown for demonstration purposes only and have no effect on the result.
Figure. Functions and their result
![figs/exhk_0708.gif](images/059600625X/figs/exhk_0708.gif)
In cell A4 in the figure, we also placed the standard CELL
function that returns a workbook's name, file path, and active sheet name. The CELL
function is a standard function that will return information about the current operating system-in other words, information on formatting, location, and contents of a workbook.