如何创建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工作簿中将会新增一个“我的工具”选项卡，如本文开头的所示。