Q：我看到过很多别人发布的Excel加载宏，能够提供一些额外的功能，或者是一些专门的功能，极大地扩展并方便Excel的使用。Excel加载宏是怎么开发出来的呢？
A：下面以创建一个示例加载宏的完整过程来介绍如何构建自已的Excel加载宏。
第1步：确定目标
首先，我们需要确定加载宏应该执行的操作以及应具备的功能。
本示例加载项的主要目的是帮助用户分析工作簿底层运作机制：
列出工作簿中所有的公式及相关信息
列出指定工作表中的条件格式设置信息
第2步：用户界面设计及操作
加载宏需要一种与用户交互的方式。这种方式可以是用户窗体，可以是一个数据输入和报表的工作表模板，可以是右键菜单命令，也可以是功能区选项卡中的命令。
本示例加载项将使用自定义功能区用户界面。在功能区中添加一个名为“我的工具”的自定义选项卡，在其中添加功能命令，如下所示。
当用户单击“公式清单”后，会弹出一个消息框，列出了工作簿中所使用的全部公式、公式所在单元格及工作表。
当用户单击“条件格式清单”后，弹出一个用户窗体，如下所示，在其中选择工作表，在下方将列出该工作表中设置的条件格式信息。
第3步：编写代码
功能区用户界面
新建工作簿并命名，然后关闭工作簿。在Custom UI Editor For Microsoft Office中打开该工作簿，单击Insert——Office2 7 Custom UI Part，如所示。
在其中输入下面的代码：
label=”公式清单”size=”large”onAction=”rxSheetToolsbtnFormulaLists”imageMso=”FunctionWizard” />label=”条件格式清单”size=”large”onAction=”rxSheetToolsbtnCondFormat”imageMso=”ConditionalFormattingsManage” />验证无误后，保存代码。单击“Generate Callbacks”，复制产生的回调代码，然后关闭Custom UI Editor。VBA代码在Excel中打开该工作簿，按Alt+F11键打开VBE。在VBE中插入标准模块并命名为：modRibbonX，在其中粘贴刚才复制的代码：‘Callbackfor rxSheetToolsbtnConditionalFormat onActionSub rxSheetToolsbtnCondFormat(control As IRibbonControl)End SubSub rxSheetToolsbtnFormulaLists(control As IRibbonControl)End Sub插入一个标准模块并命名为：modAFL，在其中输入代码：‘列出所有公式Sub AllFormulasLists()Dim wks As WorksheetDim str As StringDim rngUsedRange As RangeDim rngFormulaRange As RangeDim rng As RangeDim lngLastRow As Longstr = “工作表名称” & vbTab & “单元格地址” & vbTab & “公式文本” & vbCrLfstr = str &”———————————————————————–“& vbCrLf‘遍历工作簿中的工作表并获取工作表中的公式及相关信息For Each wks In ActiveWorkbook.WorksheetsSet rngUsedRange = wks.UsedRangeOn Error Resume NextSet rngFormulaRange =rngUsedRange.SpecialCells(xlCellTypeFormulas)For Each rng In rngFormulaRangestr = str & wks.Name &vbTab & vbTabstr = str &Application.WorksheetFunction.Substitute(rng.Address, “$”,””) & vbTab & vbTabstr = str & Mid(rng.Formula, 2,(Len(rng.Formula))) & vbCrLfNext rngSet rngFormulaRange = NothingSet rngUsedRange = NothingNext wksMsgBox str, , “本工作簿中的所有公式及对应的单元格地址”End Sub插入一个用户窗体，在其中放置两个标签控件、一个组合框、一个列表框和一个命令按钮，如下所示。为用户窗体编写的代码如下：Private Sub UserForm_Initialize()Dim wks As WorksheetFor Each wks In SheetscmbSheet.AddItem wks.NameNext wksEnd SubPrivate Sub cmdOK_Click()lblCaption.Caption = “”lstCondFormats.ClearListConditionalFormatting (cmbSheet.Text)End SubSub ListConditionalFormatting(wksName As String)Dim cf As VariantDim rng As RangeDim rngAll As RangeDim colConFormat As CollectionDim i As Long, j As LongDim var As VariantSet colConFormat = New CollectionOn Error Resume NextSet rngAll = Worksheets(wksName).Cells.SpecialCells(xlCellTypeAllFormatConditions)On Error GoToIf rngAll Is Nothing ThenlblCaption.Caption = “工作表” & wksName & “中没有设置条件格式.”Exit SubEnd IflblCaption.Caption = “工作表” & wksName & “中设置的条件格式如下:”For Each rng In rngAllFor i = 1 To rng.FormatConditions.CountWith rng.FormatConditionsOn Error Resume NextcolConFormat.Add .Item(i),FCSignature(.Item(i))On Error GoToEnd WithNext iNext rngReDim 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.CountSet cf = colConFormat.Item(i)var(i + 1, 1) =FCTypeFromIndex(cf.Type)var(i + 1, 2) = cf.AppliesTo.Addressvar(i + 1, 3) = cf.StopIfTrueOn Error Resume Nextvar(i + 1, 4) = “‘” &cf.Formula1var(i + 1, 5) = “‘” &cf.Formula2On Error GoToNext ilstCondFormats.ColumnCount = 5lstCondFormats.List = varEnd SubFunction FCSignature(ByRef cf As Variant) As StringDim strResult(1 To 3) As StringstrResult(1) = cf.AppliesTo.AddressstrResult(2) = FCTypeFromIndex(cf.Type)On Error Resume NextstrResult(3) = cf.Formula1FCSignature = Join(strResult, vbNullString)End FunctionFunction FCTypeFromIndex(lngIndex As Long) As StringSelect Case lngIndexCase 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 SelectEnd Function然后，回到modRibbonX模块，完善代码如下：‘Callback for rxSheetToolsbtnConditionalFormat onActionSub rxSheetToolsbtnCondFormat(control As IRibbonControl)frmCF.ShowEnd SubSub rxSheetToolsbtnFormulaLists(control As IRibbonControl)AllFormulasListsEnd Sub至此，本示例加载宏的编码工作完成。第4步：创建加载宏创建加载宏很简单，单击“Office按钮——另存为”（Excel 2 7）或者“文件——另存为”（Excel 2 1 ），在“保存类型”中选择“Excel加载宏(*.xlam)”，如所示。此时，Excel会自动导航到默认的加载宏文件夹，如所示。也可以选择存在自已指定的其他文件夹中。单击“保存”，创建加载宏。第5步：安装加载宏单击功能区“开发工具——加载项”，打开如下所示的对话框。如果“可用加载宏”列表中没有我们的示例加载宏，则可以单击右侧的“浏览”按钮，导航到加载宏所在的文件夹并选择加载宏文件，添加到列表中。如所示，选取示例加载宏前面的复选框，单击“确定”，完成加载宏安装。此时，Excel工作簿中将会新增一个“我的工具”选项卡，如本文开头的所示。
label=”公式清单”
size=”large”
onAction=”rxSheetToolsbtnFormulaLists”
imageMso=”FunctionWizard” />
label=”条件格式清单”size=”large”onAction=”rxSheetToolsbtnCondFormat”imageMso=”ConditionalFormattingsManage” />验证无误后，保存代码。单击“Generate Callbacks”，复制产生的回调代码，然后关闭Custom UI Editor。VBA代码在Excel中打开该工作簿，按Alt+F11键打开VBE。在VBE中插入标准模块并命名为：modRibbonX，在其中粘贴刚才复制的代码：‘Callbackfor rxSheetToolsbtnConditionalFormat onActionSub rxSheetToolsbtnCondFormat(control As IRibbonControl)End SubSub rxSheetToolsbtnFormulaLists(control As IRibbonControl)End Sub插入一个标准模块并命名为：modAFL，在其中输入代码：‘列出所有公式Sub AllFormulasLists()Dim wks As WorksheetDim str As StringDim rngUsedRange As RangeDim rngFormulaRange As RangeDim rng As RangeDim lngLastRow As Longstr = “工作表名称” & vbTab & “单元格地址” & vbTab & “公式文本” & vbCrLfstr = str &”———————————————————————–“& vbCrLf‘遍历工作簿中的工作表并获取工作表中的公式及相关信息For Each wks In ActiveWorkbook.WorksheetsSet rngUsedRange = wks.UsedRangeOn Error Resume NextSet rngFormulaRange =rngUsedRange.SpecialCells(xlCellTypeFormulas)For Each rng In rngFormulaRangestr = str & wks.Name &vbTab & vbTabstr = str &Application.WorksheetFunction.Substitute(rng.Address, “$”,””) & vbTab & vbTabstr = str & Mid(rng.Formula, 2,(Len(rng.Formula))) & vbCrLfNext rngSet rngFormulaRange = NothingSet rngUsedRange = NothingNext wksMsgBox str, , “本工作簿中的所有公式及对应的单元格地址”End Sub插入一个用户窗体，在其中放置两个标签控件、一个组合框、一个列表框和一个命令按钮，如下所示。为用户窗体编写的代码如下：Private Sub UserForm_Initialize()Dim wks As WorksheetFor Each wks In SheetscmbSheet.AddItem wks.NameNext wksEnd SubPrivate Sub cmdOK_Click()lblCaption.Caption = “”lstCondFormats.ClearListConditionalFormatting (cmbSheet.Text)End SubSub ListConditionalFormatting(wksName As String)Dim cf As VariantDim rng As RangeDim rngAll As RangeDim colConFormat As CollectionDim i As Long, j As LongDim var As VariantSet colConFormat = New CollectionOn Error Resume NextSet rngAll = Worksheets(wksName).Cells.SpecialCells(xlCellTypeAllFormatConditions)On Error GoToIf rngAll Is Nothing ThenlblCaption.Caption = “工作表” & wksName & “中没有设置条件格式.”Exit SubEnd IflblCaption.Caption = “工作表” & wksName & “中设置的条件格式如下:”For Each rng In rngAllFor i = 1 To rng.FormatConditions.CountWith rng.FormatConditionsOn Error Resume NextcolConFormat.Add .Item(i),FCSignature(.Item(i))On Error GoToEnd WithNext iNext rngReDim 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.CountSet cf = colConFormat.Item(i)var(i + 1, 1) =FCTypeFromIndex(cf.Type)var(i + 1, 2) = cf.AppliesTo.Addressvar(i + 1, 3) = cf.StopIfTrueOn Error Resume Nextvar(i + 1, 4) = “‘” &cf.Formula1var(i + 1, 5) = “‘” &cf.Formula2On Error GoToNext ilstCondFormats.ColumnCount = 5lstCondFormats.List = varEnd SubFunction FCSignature(ByRef cf As Variant) As StringDim strResult(1 To 3) As StringstrResult(1) = cf.AppliesTo.AddressstrResult(2) = FCTypeFromIndex(cf.Type)On Error Resume NextstrResult(3) = cf.Formula1FCSignature = Join(strResult, vbNullString)End FunctionFunction FCTypeFromIndex(lngIndex As Long) As StringSelect Case lngIndexCase 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 SelectEnd Function然后，回到modRibbonX模块，完善代码如下：‘Callback for rxSheetToolsbtnConditionalFormat onActionSub rxSheetToolsbtnCondFormat(control As IRibbonControl)frmCF.ShowEnd SubSub rxSheetToolsbtnFormulaLists(control As IRibbonControl)AllFormulasListsEnd Sub至此，本示例加载宏的编码工作完成。第4步：创建加载宏创建加载宏很简单，单击“Office按钮——另存为”（Excel 2 7）或者“文件——另存为”（Excel 2 1 ），在“保存类型”中选择“Excel加载宏(*.xlam)”，如所示。此时，Excel会自动导航到默认的加载宏文件夹，如所示。也可以选择存在自已指定的其他文件夹中。单击“保存”，创建加载宏。第5步：安装加载宏单击功能区“开发工具——加载项”，打开如下所示的对话框。如果“可用加载宏”列表中没有我们的示例加载宏，则可以单击右侧的“浏览”按钮，导航到加载宏所在的文件夹并选择加载宏文件，添加到列表中。如所示，选取示例加载宏前面的复选框，单击“确定”，完成加载宏安装。此时，Excel工作簿中将会新增一个“我的工具”选项卡，如本文开头的所示。
label=”条件格式清单”
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)
插入一个标准模块并命名为：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, , “本工作簿中的所有公式及对应的单元格地址”
插入一个用户窗体，在其中放置两个标签控件、一个组合框、一个列表框和一个命令按钮，如下所示。
为用户窗体编写的代码如下：
Private Sub UserForm_Initialize()
For Each wks In Sheets
cmbSheet.AddItem wks.Name
Private Sub cmdOK_Click()
lblCaption.Caption = “”
lstCondFormats.Clear
ListConditionalFormatting (cmbSheet.Text)
Sub ListConditionalFormatting(wksName As String)
Dim cf As Variant
Dim rngAll As Range
Dim colConFormat As Collection
Dim i As Long, j As Long
Dim var As Variant
Set colConFormat = New Collection
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
colConFormat.Add .Item(i),FCSignature(.Item(i))
End With
Next i
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
var(i + 1, 4) = “‘” &cf.Formula1
var(i + 1, 5) = “‘” &cf.Formula2
lstCondFormats.ColumnCount = 5
lstCondFormats.List = var
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)
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
然后，回到modRibbonX模块，完善代码如下：
‘Callback for rxSheetToolsbtnConditionalFormat onAction
frmCF.Show
AllFormulasLists
至此，本示例加载宏的编码工作完成。
第4步：创建加载宏
创建加载宏很简单，单击“Office按钮——另存为”（Excel 2 7）或者“文件——另存为”（Excel 2 1 ），在“保存类型”中选择“Excel加载宏(*.xlam)”，如所示。
此时，Excel会自动导航到默认的加载宏文件夹，如所示。也可以选择存在自已指定的其他文件夹中。
单击“保存”，创建加载宏。
第5步：安装加载宏
单击功能区“开发工具——加载项”，打开如下所示的对话框。如果“可用加载宏”列表中没有我们的示例加载宏，则可以单击右侧的“浏览”按钮，导航到加载宏所在的文件夹并选择加载宏文件，添加到列表中。
如所示，选取示例加载宏前面的复选框，单击“确定”，完成加载宏安装。此时，Excel工作簿中将会新增一个“我的工具”选项卡，如本文开头的所示。
标签： Excel常用函数excel常见问题excel技巧excel教程
相关文章
excel拖动排列步骤如下：1.我在EXCEL中输入以下数字，如图：2.现在我要想复制“1”，则拖动单元格A1，但此该你会发现，在下拖动下方有个方框，如图：3.上步出现的情况便是以顺序的方式出现，但你只需要点动那个右下方的方框，选择“复制单元格”即可......
2022-05-08 363 拖动 方框 要想 excel教程
Excel启动时打开指定工作簿的步骤1.打开Excel表格，接着点击工具栏的“文件”→Excel选项2.在Excel选项里点击“高级”菜单，在高级里下拉找到“常规”里的“启动时打开此目录中的所有文件夹&rdquo......
2022-05-08 134 内容 启动时 打开 文件夹 excel教程
excel前几行冻结步骤如下：01打开包含数据信息的Excel工作表。02将鼠标移动到左侧带有数字的那一列，选中前两行，如图所示。03点击菜单栏上方右侧的冻结窗口。04将表格向下翻，前两行是不会动的。就实现了冻结前两行了。......
2022-05-08 394 两行 冻结 实现了 这是 excel教程
excel2010插入备注步骤如下：第1步，打开Excel2010工作表，选中需要添加批注的单元格。第2步，切换至“审阅”功能区，在“批注”分组中单击“新建批注”按钮，如图1所示。图1单击“新建批注”按钮......
2022-05-08 125 单元格 批注 插入 excel教程
在使用Excel编辑工作表格时经常要输入大量数据，有时要求输入的数据不能重复，例如身份证号码、准考证号码、车牌号等，一旦输入错误查找起来相当麻烦。为避免输入出错，我们可以采用自定义公式和有效性数据检查联动的办法，让Excel对发现重复的数据进行自动全程核对检查。也就是说，每输入一个新的身份证号码，系统就会去和已经输入的......
2022-05-08 232 数据 输入 选项 重复 excel教程
