用VBA编写Excel加载宏

田华兵（国家电力公司成都勘测设计研究院 成都 610072）
关键词 Excel 加载宏
编写Excel加载宏
加载宏的思想很简单，就是把执行特定功能的模块保存在磁盘中，用户可以方便地随时加载该模块并使用其中的功能，不需要时简单地卸载即可。如果你有录制宏、修改宏的经验，编写加载宏就是一件非常简单的事。把包含宏的工作簿以“.xla”格式保存，该工作簿即成为一个加载宏，其中的工作表自动变为不可见，工作表的“IsAddIn”属性也会被自动设置为“True”。加载宏的存放位置一般是Microsoft Office\Office文件夹下的"Library"文件夹或其子文件夹,也可以是Windows所在文件夹下的"Profiles\用户名\Application Data\Microsoft\AddIns"文件夹或其他用户可以存取的地方。此后,用户就 可以通过Excel工具菜单中的“加载宏”命令来加载、卸载它，一旦加载后，其使用方式与内部命令无异。
与普通工作簿或个人工作簿中的宏相比，加载宏有诸多优越之处。首先，加载宏能方便地提供给他人使用（简单的拷贝文件），让宏的编制者与使用者可以完全分离，因此，专业人员可以为某一目的编写包含大量复杂处理、功能完备的加载宏，提供专业级或企业级的解决方案，而用户却只需要按Excel命令的标准方式进行操作；其次，加载宏的按需加载、卸载机制，有利于系统内存的有效利用；此外，加载、卸载事件还为动态处理菜单、工具条等界面元素提供了时机，可以做到与Excel系统本身无缝连接。
事实上，Excel系统的许多附加功能就是以加载宏的形式提供的，如，“与 Access 链接”、“自动保存”、“规划求解”、“分析工具库”，等等。这与Autodesk的AutoCAD以lisp文件方式向用户提供附加功能有些类似，但Excel加载宏对系统的控制能力似乎更强。
制作一个简单的加载宏并不复杂，甚至可以把系统录制的宏直接保存为加载宏。但要实现功能较为齐备的加载宏，不经过编码是不可能完成的。加载宏中的宏过程，可以与动态添加的自定义菜单（工具条）命令连接，也可以实现为自定义的工作表函数。加载宏中的自定义工作表函数与普通工作簿中完全一致。若是以命令方式提供，则须动态处理菜单（工具条）。这可以在Auto_Open()和Auto_Close()过程中实现，他们分别在加载宏的工作簿被打开和关闭时自动执行。另外，Excel97还专门提供了工作簿的AddinInstall和AddinUninstall事件，分别是在工作簿作为加载宏被加载、卸载时发生。自然，编写加载宏还必须熟悉　Excel的Automation接口，即对象体系。
在Microsoft Excel 97 Developer's Kit中，加载宏被划分为两种类型。即，与Excel内置功能协同工作的提交加载宏（committee add-in）和用自定义命令完全取代Excel命令的接管加载宏（dictators add-in）。从自解释的命名中即可看出他们之间的区别，其编制原理是完全一样的。简单地说，如果在加载提交加载宏时，把全部的Excel界面元素卸载并换上自定义的界面，同时屏蔽内置命令、函数，则可得到接管加载宏。
以上介绍的是在Excel97及其以前两个版本中被广泛应用的加载宏形式，如果你使用的是Office 2000版本，那么你拥有更多的选择余地。Office 2000除了向后兼容，继续支持普通加载宏外，它还支持更为全面、功能更为强大的COM加载项形式。COM加载项是一个完全意义上的ActiveX Automation组件，它实现了IDTExtensibility接口，作为标准ActiveX服务器被Excel或/和其他Offfice应用程序连接和使用。由于COM加载项是在其他编程环境中完成而被Office套件使用的，在此不涉及，如果你编写过Visual Basic或Visual Studio环境中的Add In程序，则明白他们实质上是一回事，只是变换了客户而已。
4 一个简单的实例
这是一个完整的小型加载宏，当它加载后，在工具菜单的末尾将增加一个菜单项，点按该菜单将对当前选定区域求平均值、小值平均值、大值平均值，并以对话框形式显示结果。另外，你也可以以工作表函数形式使用其中的宏，只是略有限制。
限于篇幅，本程序中未处理异常。
'当工作簿打开时执行
Sub Auto_Open()
'在工具菜单中添加按钮
With Application.CommandBars("Tools").Controls.Add(msoControlButton, 1)
.Caption = "大小平均值" '按钮文本
.Tag = "Example" '按钮标签
.OnAction = ThisWorkbook.Name & "!AverageMM" '按钮命令宏
End With
End Sub
'当工作簿关闭时执行
Sub Auto_Close()
Dim colMyButtons As CommandBarControls
Dim ctrMine As CommandBarButton
'查找该工程添加的菜单按钮及其复制件
Set colMyButtons = Application.CommandBars.FindControls(msoControlButton, 1, "Example")
'如果已被用户手工删除则退出过程
If colMyButtons Is Nothing Then Exit Sub
'删除所有与该工程相关的菜单按钮
For Each ctrMine In colMyButtons
ctrMine.Delete
Next
End Sub
'实现功能的宏函数
Function AverageMM(Optional RA As Range = Nothing, Optional OP As Integer = 0)
Dim RAG As Range, CL As Range
Dim A As Single, MA As Single, MI As Single
Dim T As Integer, TMA As Integer, TMI As Integer
Dim MSG As String
If RA Is Nothing Then
Set RAG = Selection
Else
Set RAG = RA
End If
For Each CL In RAG
If CL.Value <> "" And IsNumeric(CL.Value) Then
T = T + 1
A = A + CL.Value
End If
Next
If T <> 0 Then
A = A / T
Else
Exit Function
End If
For Each CL In RAG
If CL.Value <> "" And IsNumeric(CL.Value) Then
If CL.Value - A > 0.001 Then
TMA = TMA + 1
MA = MA + CL.Value
ElseIf CL.Value - A < -0.001 Then
TMI = TMI + 1
MI = MI + CL.Value
End If
End If
Next
If TMA = 0 Then
MA = A
Else
MA = MA / TMA
End If
If TMI = 0 Then
MI = A
Else
MI = MI / TMI
End If
If RA Is Nothing Then
MSG = "有效单元共：" & T & "个" & vbCrLf & "平均值：" & Space(2) & _
A & vbCrLf & "大值平均：" & MA & vbCrLf & "小值平均：" & MI & Space(20)
MsgBox MSG, vbInformation, "平均值"
Else
Select Case OP
Case -1
AverageMM = MI
Case 0
AverageMM = A
Case 1
AverageMM = MA
End Select
End If
End Function