Q:我看到过很多别人发布的Excel加载宏,能够提供一些额外的功能,或者是一些专门的功能,极大地扩展并方便Excel的使用。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工作簿中将会新增一个“我的工具”选项卡,如本文开头的所示。