【VBA】自定义加载项和函数

大家都知道VBA有用，但很少有同学有兴趣系统学习VBA。作为一个引子，我希望通过这篇小文章，向同学们展示VBA的自定义加载项和自定义函数功能。即使我们不会写代码，当需要某个特定功能的时候，只需要去网络上搜索，将别人写好的代码复制粘贴进自己的加载项或函数中，就能用起来；慢慢地，再试着修改别人的代码，实现自己想要的功能。终有一天，同学们能掌握Excel VBA的技巧，成为同伴眼中的办公大神。因此，本文不介绍VBA的语法，只介绍当你写好代码后，如何调用加载项和函数。
[h2]1 基础知识[/h2]我们经济学院的同学们在本科阶段学习的是Java或Python语言，可能看VB代码会觉得陌生。千万不要害怕，VB是简单而啰嗦的语言，非常适合入门，只要你英语过关，有一丢丢编程基础，本文的代码你完全能看明白。Excel VBA除了使用VB语法外，还增加了一些与Excel紧密联系的对象、属性和方法，限于篇幅，我只能在其他文章中慢慢介绍给大家。
本文需要了解以下VBA基础知识：

VBA的代码写在VBE（代码编辑器）中，按【Alt+F11】可以打开
自定义加载项的代码表现为以Sub开头、End Sub结尾的代码块
自定义函数的代码表现为以Function开头、End Function结尾的代码块

[h2]2 自定义加载项[/h2]

Sub 测试1()
MsgBox "Everyone can use Excel VBA."
End Sub

复制代码

我们使用这段代码来测试加载项。很明显，代码块以Sub开头，以End Sub结尾。“测试1”是加载项的名称，如果你的加载项有更加明确的功能，应该尽量以功能为名称，使自己能够在无数加载项中快速定位到想用的加载项。
打开Excel，按下【Alt+F11】打开VBE，你会看到左侧有一个“工程”窗口。在这个窗口中，至少应该有如下树形结构：

─┬─VBAProject（工作簿1）
└─Microsoft Excel 对象

复制代码

如果你已经添加过其他加载项（比如Solver），就会出现其他与VBAProject并列的树根，请不要去管它们。在VBAProject上点击【鼠标右键】—【插入】—【模块】，树形结构变成：

─┬─VBAProject（工作簿1）
├─Microsoft Excel 对象
└┬模块
└模块1

复制代码

上述代码就放在模块1对应的代码窗口中。我们可以对VBAProject进行重命名（建议），然后保存文件为xlam格式，加载项就保存好了。双击打开xlam文件，你会发现这个文件连一个工作表都没有，这很正常；点击【Alt+F11】，如果你看到代码还在，就知道自己已经成功撰写了第1个加载项。

[h2]3 自动载入加载项[/h2]有了一个好用的加载项，我们当然希望以后使用Excel时能直接调用。如何调用呢？最直接的办法就是双击xlam文件。无论是先打开待处理的Excel工作表再双击xlam文件，还是先打开xlam文件再添加工作表，我们都可以直接使用上述代码功能。
同学们会发现加载项和宏很像，不同之处在于，xlam格式的加载项在载入后并不会显示在宏列表中，即点击【Alt+F8】是空的。但是，当我们手动输入加载项的名称“测试1”时，会发现它可以被执行。每次都输入文字才能执行加载项也太麻烦了，这绝不是使用加载项的好办法。
使用加载项的规定动作是【自动载入】+【点击按钮】，以此为目标，我们需要做的是两步：
[h3]第1步：设置自动载入加载项[/h3]【文件】—【选项】—【加载项】—【转到】管理Excel加载项—【勾选】自定义加载项—【确定】。如果我们自定义的加载项没有出现在框中，点击【浏览】，找到刚才保存的xlam文件即可。从此以后，Excel软件在启动的时候会自动载入这个自定义加载项，如果你删掉了xlam文件或者修改了名称，打开Excel就会报错。
[h3]第2步：添加加载项按钮[/h3]【文件】—【选项】—【自定义功能区】，出现以下大框框。我们先在右边的主选项卡中【新建选项卡】—【新建组】—分别【重命名】。然后在【从下列位置选择命令】中选择【宏】，会看到我们加载项中的“测试1”，点击【添加】将该宏添加到新建的组中。

看看我们是不是有一个新的选项卡【CXW】，卡中有个叫【TEST】的组，组中有个叫【测试1】的按钮。如下图所示：

好啦，我们试一试这个按钮有没有用。点一下，出现一个弹窗，这就是成功了。

可能有同学要问，弹出一句话的加载项有什么用啊？当然有用啦！
结合ActiveCell，辅导员老师可以点击某个同学的姓名单元格，获取他的个人信息；而这些信息只在辅导员的加载项中，不出现在工作表里，更不会随工作表传播出去。辅导员想找到你的手机号，只需要点一个小按钮。

[h2]4 自定义函数[/h2]有一天，龚老师问了我一个问题：如果Excel单元格中是分级会计科目，形式为“一级科目/二级科目/三级科目/四级科目”，如何只保留最后一级科目呢？我的第一反应是分列；但分级科目并不全都是4个级别，分列后的最后一级科目并不在同一列中。写个自定义函数吧：

Function LAST(s, sep) As String
Dim kemu As Variant, n As Integer
kemu = Split(s, sep)
n = Len(s) - Len(Application.WorksheetFunction.Substitute(s, sep, ""))
LAST = kemu(n)
End Function

复制代码

自定义函数以Function开头，以End Function结尾，函数名为LAST，一定要注意，不要和Excel自带的函数重复。代码会自动生成一个与函数名相同的变量，用来放置返回结果。LAST函数具有两个参数，第一个是待分割的文本s，第二个是分隔符sep，都是字符串类型。
首先，我们定义了Variant类型的变量kemu，用来保存分割后的多级科目；同时定义了Integer类型的变量n，用来保存分隔符的数量。然后，我们用VBA的Split函数分割文本s，得到数组kemu。
为了给大家展示工作表函数的用法，我使用了VBA函数中没有、但工作表函数中有的Substitute函数，分别计算包含分隔符和不包含分隔符的字符数，再相减得到分隔符的数量n。
最后，我们希望在公式单元格中返回kemu数组中索引为n（第n+1个）的内容，即最后一层的科目。试一试，在Excel的B1单元格中写下公式【=LAST(A1,"/")】，就会得到正确的结果。

自定义函数的使用方法和内嵌工作表函数完全一样，你可以将它保存在xlam文件中，并自动加载；只不过自定义函数在使用时需要你自己输入函数名称。

如果你一瞬间想不出自定义函数有什么用，没关系，随着你使用Excel越来越多，对自己重复进行的工作越来越了解，一定能体会到自定义函数的方便之处。
我最常用的自定义函数是将百分制成绩转化为等级，虽然IF函数也能写，但每次都重写真的很麻烦。通过自定义函数一次性输出均值、方差、极大值、极小值也很实用。

[h2]5 总结[/h2]这篇文章里，我们学习了如何使用Excel自定义加载项和自定义函数。正确理解以下概念非常重要：

xlam格式
Sub过程与Function过程
自定义功能区

留一道思考题吧。小明想根据P值给系数添加*号（P值列在系数列的右边1列），撰写代码如下：
Sub 加星()
Dim p As Double, cell As range
For Each cell In Selection
      With cell
         p = .Offset(0, 1).Value
         Select Case p
            Case Is < 0.01
                  .Value = .Value & "***"
            Case Is < 0.05
                  .Value = .Value & "**"
            Case Is < 0.1
                  .Value = .Value & "*"
         End Select
      End With
Next
End Sub
复制代码
尝试使用上述VBA代码，选中待标记显著性的单元格区域，给计量结果添加星号。