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

office办公软件入门基础教程 » 如何创建Excel加载宏

本文链接:【如何创建Excel加载宏】https://www.officeapi.cn/89533.html

OFFICE,天天学一点,进步多一点

联系我们