如何创建Excel加载宏
Q：我看到过很多别人发布的Excel加载宏，能够提供一些额外的功能，或者是一些专门的功能，极大地扩展并方便Excel的使用。Excel加载宏是怎么开发出来的呢？
A：下面以创建一个示例加载宏的完整过程来介绍如何构建自已的Excel加载宏。
第1步：确定目标
首先，我们需要确定加载宏应该执行的操作以及应具备的功能。
本示例加载项的主要目的是帮助用户分析工作簿底层运作机制：
列出工作簿中所有的公式及相关信息
列出指定工作表中的条件格式设置信息
第2步：用户界面设计及操作
加载宏需要一种与用户交互的方式。这种方式可以是用户窗体，可以是一个数据输入和报表的工作表模板，可以是右键菜单命令，也可以是功能区选项卡中的命令。
本示例加载项将使用自定义功能区用户界面。在功能区中添加一个名为“我的工具”的自定义选项卡，在其中添加功能命令，如下所示。
当用户单击“公式清单”后，会弹出一个消息框，列出了工作簿中所使用的全部公式、公式所在单元格及工作表。
当用户单击“条件格式清单”后，弹出一个用户窗体，如下所示，在其中选择工作表，在下方将列出该工作表中设置的条件格式信息。
第3步：编写代码
功能区用户界面
新建工作簿并命名，然后关闭工作簿。在Custom UI Editor For Microsoft Office中打开该工作簿，单击Insert——Office2 7 Custom UI Part，如所示。
在其中输入下面的代码：
<button id=”rxSheetToolsbtnFormulaLists”
label=”公式清单”
size=”large”
onAction=”rxSheetToolsbtnFormulaLists”
imageMso=”FunctionWizard” />
<button id=”rxSheetToolsbtnConditionalFormat”
label=”条件格式清单”
size=”large”
onAction=”rxSheetToolsbtnCondFormat”
imageMso=”ConditionalFormattingsManage” />
验证无误后，保存代码。单击“Generate Callbacks”，复制产生的回调代码，然后关闭Custom UI Editor。
VBA代码
在Excel中打开该工作簿，按Alt+F11键打开VBE。
在VBE中插入标准模块并命名为：modRibbonX，在其中粘贴刚才复制的代码：
‘Callbackfor rxSheetToolsbtnConditionalFormat onAction
Sub rxSheetToolsbtnCondFormat(control As IRibbonControl)
End Sub
Sub rxSheetToolsbtnFormulaLists(control As IRibbonControl)
End Sub
插入一个标准模块并命名为：modAFL，在其中输入代码：
‘列出所有公式
Sub AllFormulasLists()
Dim wks As Worksheet
Dim str As String
Dim rngUsedRange As Range
Dim rngFormulaRange As Range
Dim rng As Range
Dim lngLastRow As Long
str = “工作表名称” & vbTab & “单元格地址” & vbTab & “公式文本” & vbCrLf
str = str &”———————————————————————–“& vbCrLf
‘遍历工作簿中的工作表并获取工作表中的公式及相关信息
For Each wks In ActiveWorkbook.Worksheets
Set rngUsedRange = wks.UsedRange
On Error Resume Next
Set rngFormulaRange =rngUsedRange.SpecialCells(xlCellTypeFormulas)
For Each rng In rngFormulaRange
str = str & wks.Name &vbTab & vbTab
str = str &Application.WorksheetFunction.Substitute(rng.Address, “$”,””) & vbTab & vbTab
str = str & Mid(rng.Formula, 2,(Len(rng.Formula))) & vbCrLf
Next rng
Set rngFormulaRange = Nothing
Set rngUsedRange = Nothing
Next wks
MsgBox str, , “本工作簿中的所有公式及对应的单元格地址”
End Sub
插入一个用户窗体，在其中放置两个标签控件、一个组合框、一个列表框和一个命令按钮，如下所示。
为用户窗体编写的代码如下：
Private Sub UserForm_Initialize()
Dim wks As Worksheet
For Each wks In Sheets
cmbSheet.AddItem wks.Name
Next wks
End Sub
Private Sub cmdOK_Click()
lblCaption.Caption = “”
lstCondFormats.Clear
ListConditionalFormatting (cmbSheet.Text)
End Sub
Sub ListConditionalFormatting(wksName As String)
Dim cf As Variant
Dim rng As Range
Dim rngAll As Range
Dim colConFormat As Collection
Dim i As Long, j As Long
Dim var As Variant
Set colConFormat = New Collection
On Error Resume Next
Set rngAll = Worksheets(wksName).Cells.SpecialCells(xlCellTypeAllFormatConditions)
On Error GoTo
If rngAll Is Nothing Then
lblCaption.Caption = “工作表” & wksName & “中没有设置条件格式.”
Exit Sub
End If
lblCaption.Caption = “工作表” & wksName & “中设置的条件格式如下:”
For Each rng In rngAll
For i = 1 To rng.FormatConditions.Count
With rng.FormatConditions
On Error Resume Next
colConFormat.Add .Item(i),FCSignature(.Item(i))
On Error GoTo
End With
Next i
Next rng
ReDim var(1 To colConFormat.Count + 1, 1 To5)
var(1, 1) = “类型”
var(1, 2) = “单元格”
var(1, 3) = “如果为真则停止”
var(1, 4) = “公式1”
var(1, 5) = “公式2”
For i = 1 To colConFormat.Count
Set cf = colConFormat.Item(i)
var(i + 1, 1) =FCTypeFromIndex(cf.Type)
var(i + 1, 2) = cf.AppliesTo.Address
var(i + 1, 3) = cf.StopIfTrue
On Error Resume Next
var(i + 1, 4) = “‘” &cf.Formula1
var(i + 1, 5) = “‘” &cf.Formula2
On Error GoTo
Next i
lstCondFormats.ColumnCount = 5
lstCondFormats.List = var
End Sub
Function FCSignature(ByRef cf As Variant) As String
Dim strResult(1 To 3) As String
strResult(1) = cf.AppliesTo.Address
strResult(2) = FCTypeFromIndex(cf.Type)
On Error Resume Next
strResult(3) = cf.Formula1
FCSignature = Join(strResult, vbNullString)
End Function
Function FCTypeFromIndex(lngIndex As Long) As String
Select Case lngIndex
Case 1: FCTypeFromIndex = “单元格值”
Case 2: FCTypeFromIndex = “表达式”
Case 3: FCTypeFromIndex = “色阶”
Case 4: FCTypeFromIndex = “数据条”
Case 5: FCTypeFromIndex = “前1 ?”
Case 6: FCTypeFromIndex = “图标集”
Case 8: FCTypeFromIndex = “唯一值”
Case 9: FCTypeFromIndex = “文本”
Case 1 : FCTypeFromIndex = “空值”
Case 11: FCTypeFromIndex = “时间段”
Case 12: FCTypeFromIndex = “高于平均值”
Case 14: FCTypeFromIndex = “非空值”
Case 16: FCTypeFromIndex = “错误”
Case 17: FCTypeFromIndex = “无错误”
Case Else: FCTypeFromIndex = “未知”
End Select
End Function
然后，回到modRibbonX模块，完善代码如下：
‘Callback for rxSheetToolsbtnConditionalFormat onAction
Sub rxSheetToolsbtnCondFormat(control As IRibbonControl)
frmCF.Show
End Sub
Sub rxSheetToolsbtnFormulaLists(control As IRibbonControl)
AllFormulasLists
End Sub
至此，本示例加载宏的编码工作完成。
第4步：创建加载宏
创建加载宏很简单，单击“Office按钮——另存为”（Excel 2 7）或者“文件——另存为”（Excel 2 1 ），在“保存类型”中选择“Excel加载宏(*.xlam)”，如所示。
此时，Excel会自动导航到默认的加载宏文件夹，如所示。也可以选择存在自已指定的其他文件夹中。
单击“保存”，创建加载宏。
第5步：安装加载宏
单击功能区“开发工具——加载项”，打开如下所示的对话框。如果“可用加载宏”列表中没有我们的示例加载宏，则可以单击右侧的“浏览”按钮，导航到加载宏所在的文件夹并选择加载宏文件，添加到列表中。
如所示，选取示例加载宏前面的复选框，单击“确定”，完成加载宏安装。此时，Excel工作簿中将会新增一个“我的工具”选项卡，如本文开头的所示。
