一起学Excel专业开发15：用户自定义函数与函数库加载宏

使用VBA编写用户自定义函数，不仅可以在程序中进行调用，还可以像Excel内置的工作表函数一样，在工作表公式中使用。例如下面的用户自定义函数GetNum：

Function GetNum(rng As String)

Dim lngLen As Long

Dim i As Long, result

lngLen = Len(rng)

For i = 1 To lngLen

If IsNumeric(Mid(rng, i, 1)) Then

result = result & Mid(rng, i,1)

End If

Next i

GetNum = result

End Function

GetNum函数的作用是从含有数字的字符串中提取出数字，如下图1所示。

图1

在Excel中，只用于提供用户自定义函数的加载宏称为函数库加载宏，这是最简单的一类加载宏。这样，只要安装了函数库加载宏，就可以在工作表中使用其所包含的自定义函数了。

让用户自定义函数更像Excel内置函数

默认情况下，Excel将用户自定义函数分配到用户定义类别中。将用户自定义函数注册到Excel，可使其看起来更像是Excel内置函数，包括为自定义函数指定描述性文字、将其分配到相应的类别中，有两种方法。

方法1：使用Application.MacroOptions方法

如下面的代码：

Sub RegisterFunction()

Dim sDescription As String

sDescription = "从字符串中提取出数字"

Application.MacroOptions macro:="GetNum", _

Description:=sDescription, _

Category:=9

End Sub

运行后，在Excel中注册GetNum函数，将其置于类别9（即信息类）中，如下图2所示。

图2

说明：

1.Application.MacroOptions方法可以为自定义函数使用较长的描述文字，并且在为自定义函数指定了新的类别后，Excel会将其从用户定义类别中删除。

2.在转换成加载宏之前，先使用Application.MacroOptions方法来注册自定义函数。

3.Excel将内置函数进行了分类，将函数归于不同的类别中，以方便查找和使用。下图3列出了函数类别号和对应的名称，其中类别号为10-13的在“插入函数”对话框中一般不会显示。在将自定义函数指定为这些类别后，它们才会在“插入函数”对话框中出现。

图3

方法2：使用XLM函数

可以执行一个XLM宏函数来注册自定义函数，具体的代码见Function.xla中的MRegister模块，你可以在完美Excel微信公众号底部发消息：

宏函数注册

下载该工作簿。

该方法的优点是可以完全控制自定义函数的描述及类别的各个方面，但XLM的宏字符串（包括名称、描述及其他信息等）的长度不能超过255个字符，因此使用该方法对自定义函数的描述文本不能太长。并且，无论你是否为函数指定了类别，它都在用户定义类别中存在。

给加载宏创建友好的名称和描述信息

单击功能区选项卡“开发工具——加载项”，显示“加载宏”对话框，其中列出了所有可用的加载宏。选取想要安装的加载宏前的复选框，单击“确定”即可安装该加载宏，如下图4所示。

图4

打开VBE编辑器，双击加载宏工作簿中的ThisWorkbook模块，设置其IsAddin属性值为False，如下图5所示。

图5

此时，将会出现加载宏工作簿界面，单击“文件—信息”，在属性中输入标题、备注、作者等内容，如下图6所示。

图6

保存后，回到图5所示的界面，将IsAddin属性值设置为True。

此时，单击功能区选项卡“开发工具—加载项”，在“加载项”对话框中选择刚才的加载宏后，下方会显示详细的信息，如下图7所示。

图7