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
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter