In this VBA workshop, we’ll develop an Excel user-defined function (UDF) patterned after Excel’s built-in information functions CELL and INFO but able to provide certain information that is not offered by either. In particular, this UDF will directly return the name of a worksheet or a workbook or a workbook’s path plus a variety of information about Excel and its operating environment.
Excel's CELL and INFO Functions
Excel’s INFO Function has the following syntax:
directory numfile origin osversion recalc release system
Excel’s CELL Function has the following syntax:
address col color contents filename format parentheses prefix protect row type width
C:\Users\Homer\Documents\This Folder\[That Book.xlsm]A Sheet
The following three formulas can be used to separate this result into the workbook’s path, the workbook’s name, and the worksheet’s name (in order, followed by each formula’s value):
=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-2) C:\Users\Homer\Documents\This Folder =MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))-1) That Book.xlsm =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,999) A Sheet
Doesn’t that seem like a lot of work to get such basic information? Is there other useful operational information not provided by Excel’s built-in functions?
The NameOf Function
Let’s develop a user-defined information function to augment Excel’s CELL and INFO functions. The complete VBA code for our NameOf UDF is listed below. Notice the comment block at the beginning of the code. Further discussion follows the listing.
Public Function NameOf(Optional ByVal This As Variant = 0, Optional ByVal Target As Range = Nothing) As Variant ' User-Defined Function (UDF) to return Target's Worksheet.Name, Workbook.Name, or Workbook.Path ' otherwise, return Application.Name and .Version, .Caption, .StatusBar, .UserName, .OrganizationName, or .ActivePrinter ' otherwise, return environment variable "COMPUTERNAME" or any environment variable named by This (ignoring alphabetic case) ' SYNTAX: NameOf([This],[Target]) ' Default This is 0 (or "sheet" or "worksheet") ' This = 0 or "sheet" or "worksheet" return Target's Worksheet.Name (default) ' This = 1 or "book" or "workbook" return Target's Workbook.Name ' This = 2 or "path" or "filepath" return Target's Workbook.Path ' This = 3 or "app" or "application" return Application.Name and Application.Version ' This = 4 or "caption" or "titlebar" return Application.Caption ' This = 5 or "statusbar" return Application.StatusBar ("Default" unless set by a macro) ' This = 6 or "user" return Application.UserName ' This = 7 or "organization" return Application.OrganizationName ' This = 8 or "printer" return Application.ActivePrinter ' This = 9 or "computer" return Environ("COMPUTERNAME") ' This = "?" or "help" return text list of recognized This ' This = any string not listed above return Environ(This) ' If Target is Nothing (default), then Target is set to the cell referencing this function (error if referenced in a VBA statement) ' otherwise, Target should be a Worksheet cell's address (like $A$1 or Sheet1!A1) or VBA Range such as Range("$A$1") ' Patterned after Excel's built-in information functions CELL and INFO ' DEVELOPER: J. Woolley (for wellsr.com) Dim vResult As Variant Application.Volatile If Not IsNumeric(This) Then This = Trim(LCase(This)) Select Case This Case 0, "sheet", "worksheet": If Target Is Nothing Then Set Target = Application.ThisCell vResult = Target.Parent.Name Case 1, "book", "workbook": If Target Is Nothing Then Set Target = Application.ThisCell vResult = Target.Parent.Parent.Name Case 2, "path", "filepath": If Target Is Nothing Then Set Target = Application.ThisCell vResult = Target.Parent.Parent.Path Case 3, "app", "application": vResult = Application.Name & " " & Application.Version Case 4, "caption", "titlebar": vResult = Application.Caption Case 5, "statusbar": vResult = Application.StatusBar If Not vResult Then vResult = "Default" Case 6, "user": vResult = Application.UserName Case 7, "organization": vResult = Application.OrganizationName Case 8, "printer": vResult = Application.ActivePrinter Case 9, "computer": vResult = Environ("COMPUTERNAME") Case "?", "help": vResult = "Worksheet, Workbook, Filepath, Application, Titlebar, Statusbar, User, Organization, Printer, Computer (EnvVar)" Case Else: vResult = Environ(CStr(This)) If vResult = "" Then vResult = CVErr(xlErrValue) ' #VALUE! (Error 2015) End Select NameOf = vResult End Function
Make powerful macros with our free VBA Developer's Kit
There's a lot to unpack here. To save time and become really good at VBA, make sure you get our free VBA Developer's Kit below. It's full of shortcuts, tips and pre-built macros to make writing VBA easier.
The NameOf function takes two arguments named
Optional. Also, both arguments are declared
ByVal since they are changed within the function under certain circumstances. The function returns a
Variant result representing the name of
The comment block at the beginning of the code identifies recognized values for
=NameOf("book") =NameOf(" BOOK ")
To help you out, the following cell formula will return a comma-separated list of all recognized
Here is that formula’s output:
Worksheet, Workbook, Filepath, Application, Titlebar, Statusbar, User, Organization, Printer, Computer (EnvVar)
The NameOf function’s
Range object; its default value is set to the cell referencing NameOf, which is
If NameOf is included in a VBA statement instead of a cell formula,
Range object like
ActiveCell; in this case, the default value will cause an error. If
Range, NameOf returns
Notice the use of Application.Volatile. It’s included to ensure all cells referencing the NameOf function are updated when the workbook is opened or when such a cell is recalculated.
Finally, a VBA Select Case statement is used to resolve the result requested by
Target.Parent is the cell’s
Worksheet object and
Target.Parent.Parent is its
Workbook object. The result will be “Default” if
False, meaning the status bar is controlled by Excel.
The following screen shot illustrates information available from the NameOf UDF. The results were created by entering this formula into cell B2:
The formula was then copied down column B (range B3:B15). The last three rows represent environment variables. Notice the difference between rows 8 and 15.
- You might ask what’s the point of adding a cell formula that returns the worksheet’s name or the workbook’s name or its path when you already know those things? The point, of course, is that Homer might move the file or change its name or rename the sheet, and his operating environment might be different from yours. This could also be important if you’re relying on a VBA code interacting with a particular sheet and the user inadvertently changes the name of the sheet.
- Although the NameOf UDF can be referenced in a VBA statement, this serves no purpose because all of the information can be obtained directly from Excel’s object model and VBA’s
Environfunction. I state this with the caveat that it could still be valuable if your code isn’t optimized and your macros rely on your spreadsheet having a fixed sheet name, for example.
- Using Excel’s CELL function without specifying the optional
referenceargument might be problematic because the default referenceis “the last cell that was changed.” However, this can be useful under certain circumstances.