A VBA UDF to Enhance Excel's CELL and INFO Functions - wellsr.com

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