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,
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:
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
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.