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: INFO(type_text), where type_text can be one of the following 7 text values:

directory
numfile
origin
osversion
recalc
release
system

Regarding directory, Microsoft’s document says that INFO will return the “Path of the current directory or folder.” In reality, it actually returns the user’s Documents folder path. For example, INFO("directory") might return something like the following:

C:\Users\Homer\Documents\

Excel’s CELL Function has the following syntax: CELL(info_type,[reference]), where info_type can be one of the following 12 text values:

address
col
color
contents
filename
format
parentheses
prefix
protect
row
type
width

Regarding filename, Microsoft’s document says that CELL will return “Filename (including full path) of the file that contains reference,” and the default reference is “the last cell that was changed.” But CELL("filename",[reference]) actually returns a concatenation of the workbook’s path plus its filename enclosed in square-brackets plus the worksheet’s name. For example, CELL("filename",A1) might return something like this:

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 Kit

This is actually pretty neat. If you have trouble understanding or remembering it, our free VBA Developer Kit can help. It’s loaded with VBA shortcuts to help you make your own macros like this one - we’ll send a copy, along with our Big Book of Excel VBA Macros, to your email address below.

I'll take a free VBA Developer Kit

The NameOf function takes two arguments named This and Target; both are 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 This for the given Target.

The comment block at the beginning of the code identifies recognized values for This, which can be either numeric or text; text values are self-descriptive. Some recognized text values have a short-form and a long-form. For example, if This is either “sheet” or “worksheet” the return value will be the name of Target’s worksheet. If This is text, alphabetic case is ignored as are leading and trailing space characters, so both of the following cell formulas will yield the same result:

=NameOf("book")
=NameOf("  BOOK  ")

To help you out, the following cell formula will return a comma-separated list of all recognized This long-form text values:

=NameOf("Help")

Here is that formula’s output:

Worksheet, Workbook, Filepath, Application, Titlebar, Statusbar, User, Organization, Printer, Computer (EnvVar)

If This is not one of the recognized values, it will be assumed to represent an environment variable as described in our VBA Environ tutorial. For example, here is the formula and result for Homer’s APPDATA environment variable:

=NameOf("AppDATA")
C:\Users\Homer\Appdata\Roaming

If This is not specified, the default value is 0 (or “sheet” or “worksheet”). If This is not recognized and is not a valid environment variable, then NameOf returns #VALUE!.

The NameOf function’s Target argument is a Range object; its default value is set to the cell referencing NameOf, which is Application.ThisCell. If Target is specified, it must be a cell address like $A$1 or 'A Sheet'!A1 or '[That Book.xlsm]A Sheet'!A1. (Identifying a workbook in Target is discouraged because its file or path might change.) This is great for capturing the sheet name of a cell on another sheet, just in case the user changes it.

If NameOf is included in a VBA statement instead of a cell formula, Target must be a Range object like Range("$A$1") or Cells(1) or ActiveCell; in this case, the default value will cause an error. If This is not equivalent to “sheet”, “book”, or “path” then Target is ignored unless it is an invalid Range. If Target references a workbook that is not open, Target might be treated as the cell referencing NameOf (its default value) or as an invalid Range. If Target is an invalid Range, NameOf returns #VALUE!.

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 This. It is arranged to reduce the amount of calculation for a given This. Each result is determined by properties of Excel’s object model, where Target.Parent is the cell’s Worksheet object and Target.Parent.Parent is its Workbook object. The result will be “Default” if This is “statusbar” and Application.StatusBar is False, meaning the status bar is controlled by Excel.

NameOf Results

The following screen shot illustrates information available from the NameOf UDF. The results were created by entering this formula into cell B2:

=NameOf(A2)

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.

Final Thoughts

  1. 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.
  2. 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 Environ function. 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.
  3. Using Excel’s CELL function without specifying the optional reference argument might be problematic because the default reference is “the last cell that was changed.” However, this can be useful under certain circumstances.

If you found this UDF help, you’ll love the other VBA content we send our VBA insiders. Subscribe using the form below, then share this article on Twitter and Facebook!