VBA代码库09：增强的CELL函数和INFO函数

excelperfect

本文介绍的自定义函数来源于wellsr.com，以Excel的CELL函数和INFO函数为样板，可直接返回工作表或工作簿的名称或工作簿路径，以及与Excel及其操作环境有关的各种信息。本文对其内容进行了整理并分享于此，希望能够有助于VBA代码的学习，同时留存这个自定义函数以备所需。

INFO函数回顾

INFO函数的语法如下：

INFO(type_text)

其中，参数type_text可以是下列值之一：directory、numfile、origin、osversion、recalc、release、system。

例如，如果指定参数值为directory，即输入公式：

=INFO('DIRECTORY')

则返回当前目录或文件夹的路径，对于我的示例工作簿来说将返回：

D:\01. Excel研究\06.2 VBA代码库\09\

详细内容参见：Excel函数学习27：INFO函数

CELL函数回顾

CELL函数的语法如下：

CELL(info_type,[reference])

其中，参数info_type可以是下列值之一：address、col、color、contents、filename、format、parentheses、prefix、protect、row、type、width。

参数reference，可选，默认值是最后一个发生变化的单元格。

例如，下面的公式：

=CELL('filename',A1)

在我的示例工作簿中返回：

D:\01. Excel研究\06.2 VBA代码库\09\[VBACodeLibrary09.xlsm]Sheet1

下面的公式来拆分出工作簿路径、工作簿名称和工作表名称。

公式：

=LEFT(CELL('filename',A1),FIND('[',CELL('filename',A1))-2)

结果返回工作簿路径：

D:\01. Excel研究\06.2 VBA代码库\09

公式：

=MID(CELL('filename',A1),FIND('[',CELL('filename',A1))+1,FIND(']',CELL('filename',A1))-FIND('[',CELL('filename',A1))-1)

结果返回工作簿名称：

VBACodeLibrary09.xlsm

公式：

=MID(CELL('filename',A1),FIND(']',CELL('filename',A1))+1,999)

结果返回工作表名称：

Sheet1

详细内容参见：Excel函数学习24：CELL函数

NameOf函数

NameOf函数用来增强CELL函数和INFO函数的功能，不需要像上面那样使用长而复杂的公式来获取相关信息。NameOf函数的代码如下：

' 返回工作表名,工作簿名或工作簿路径

' 或者, 返回应用程序名、版本、标题、状态栏、用户名、组织名或当前打印机

' 或者, 返回环境变量'COMPUTERNAME' 或This命名的任何环境变量名

' 语法: NameOf([This],[Target])

' 参数This默认值0 (或者 'sheet' 或者 'worksheet')

' This = 0 或 'sheet' 或 'worksheet' 返回工作表名(默认)

' This = 1 或 'book' 或 'workbook' 返回工作簿名

' This = 2 或 'path' 或 'filepath' 返回工作簿路径

' This = 3 或 'app' 或 'application' 返回应用程序名和版本

' This = 4 或 'caption' 或 'titlebar' 返回应用程序标题

' This = 5 或 'statusbar' 返回应用程序状态栏

' This = 6 或 'user' 返回应用程序用户名

' This = 7 或 'organization' 返回应用程序组织名

' This = 8 或 'printer' 返回当前打印机

' This = 9 或 'computer' 返回Environ('COMPUTERNAME')

' This ='?' 或 'help' 返回This的文本列表

' This = 上面没有列出的任意字符串返回Environ(This)

' 如果Target为空(默认), 则Target被设置为引用此函数的单元格(如果在VBA语句中引用则错误)

' 或者, Target应该是单元格地址(如$A$1或Sheet1!A1)或VBA单元格区域如Range('$A$1')

' 仿照Excel内置信息函数CELL和INFO

' 开发:wellsr.com

Public FunctionNameOf(Optional ByVal This As Variant = 0, _

  Optional ByVal Target As Range = Nothing) AsVariant

    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)

    End Select

    NameOf = vResult

End Function

NameOf函数有两个参数：This和Target，都是可选的。此外，两个参数都声明为ByVal，确保在函数中的更改不会影响到传递给它的参数。函数返回Variant型的结果，表示指定的Target的This的名称。

代码开头的注释部分说明了参数This可以指定的值，可以使用数字或文本来指定。如果This指定为文本，则忽略大小写以及前导和结尾的空格。

例如，公式：

=nameof(' book  ')

在我的示例中返回结果为：

VBACodeLibrary09.xlsm

下面的公式：

=nameof('Help')

输出可以在函数中使用的所有长格式文本值：

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

如果NameOf函数中没有指定参数This，则默认为0（或”sheet”或”worksheet”）。

NameOf函数的参数Target是Range对象，默认为公式所引用的单元格即Application.ThisCell。如果指定Target，则必须是单元格地址如$A$1或Sheet1!A1或’[示例工作簿.xlsm]Sheet1’!A1。

如果在VBA中使用NameOf函数，那么参数Target必须是Range对象如Range(“$A$1”)或Cells(1)或ActiveCell。如果参数This的值不是”sheet”、”book”或”path”，那么参数Target被忽略，除非其为无效的Range。如果参数Target引用了未打开的工作簿，则Target可能会被视为NameOf函数引用（其默认值）的单元格或无效的Range。如果Target是无效的Range，那么NameOf函数返回#VALUE!。

注意，使用Application.Volatile以确保在打开工作簿或重新计算单元格时，所有引用NameOf函数的单元格都会得到更新。

欢迎在下面留言，完善本文内容，让更多的人学到更完美的知识。