如何创建Excel加载宏
发布时间:2022-04-26 20:48:30
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工作簿中将会新增一个“我的工具”选项卡,如本文开头的所示。
猜你喜欢
- 无论是电脑还是手机,录屏是我们常常用到的一项功能,可以保存一些内容,比如录制打游戏的教程等等。Windows10系统的游戏录制组合键Win+
- copyto是一款完美的个人数据处理平台,集多种应用程序于一身,灵活、便捷的表现方式让你复制数据发送数据变得轻而易举。功能特点:1.提高您的
- 当我们使用win10操作系统的时候,有一些小伙伴想重新安装自己的操作系统,但不知道现在win10的哪个版本最流畅、好用。所以对于这个问题小编
- 许多小伙伴在启动计算机时,出现蓝屏代码0X0000ED的情况,无法进入系统,也无法操作。情况如何?可能是硬盘故障,导致启动时无法正常加载,可
- 可以更改,并自定义为系统包含的边框线。如图所示,定义好颜色,线型后,拖放鼠标画出表格即可。你的要求如下图
- 安装win10分区时应该选择什么格式?许多小型合作伙伴发现在安装win10时有一个格式选择。系统默认为GPT,但因为新主板是UEFI模式。让
- 用word做了一份报告文档,里面有文字,也有数字。类似如下:
- Word2007中怎样取消输入法控制处于活动状态在Word2007文档中,如果启用“输入法控制处于活动状态”功能,则在打开Word2007文
- Word是文档编辑的软件,能够帮助我们完成表达的工作,如何能让你的文档更好的帮助你表达呢?下面的Word小技巧帮到你,接下来由小编为大家推荐
- 只查找第一列的信息,在我们单位是不够的,我们需要查找前三名的数据进行分析,怎么用公式实现?我们只需把昨天公式中的最大值函数换成LARGE函数
- 在编辑论文的时候,我们经常遇到这种情况,将数据图表从excel中粘贴到word中后,只能显示图表的最下面一部分,大部分都显示不出来。所以下面
- 使用“以显示精度为准”选项会产生累积计算影响,使您的数据随着时间的推移越来越不准确。仅在您确定显示精度可以保持数据的精确度时才可以使用此选项
- 另存也能加密!通过保存文件时设置权限,可以让文档在打开时或修改时受到限制。打开文档后选择“文件→另存为”,点击“保存”按钮左侧的“工具”按钮
- 在日常办公的过程中,我们经常会遇到这样现象:Excel中一个对象分布在不同的区域,怎样对其求和呢?如图:张三分布在不同的行,怎样对其求和呢?
- 哈喽,大家好!今天我们要给大家分享5种不用函数公式的Excel数字提取技巧。不管是从有规律的文本、还是没有规律的文本中提取手机号、金额、尺寸
- 在 watchOS 7 中,苹果加入了洗手检测功能,此功能可以帮助用户正确洗手,以防止病毒和细菌滋生。洗手是防止生病和细菌传播的最重要的保护
- 在Word中处理图片时,经常会遇到这样或那样的尴尬场景,比如添加的图片不对齐等,这时你都是如何处理呢?本期Word小编与大家分享5个超赞的W
- 因工作需要,希望在Word中输入的文字能以任意方向旋转,我看了一下,Word中的文字似乎只能横向、竖向排列,我想要的方向是比如45度那样的,
- Word文档怎么删除分页符?许多用户在使用Word文档编辑文章的时候都有遇到过这样的问题:文章莫名其妙的跳转到了下一页,而目前页面还有很多空
- win10系统的用户对此很熟悉。它内置了Edge浏览器。但是,在使用过程中可能会遇到一些问题。例如,最近,win10系统的用户报告他们遇到了