如何创建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工作簿中将会新增一个“我的工具”选项卡,如本文开头的所示。
猜你喜欢
- 今天小编为大家带来了Win8.1系统如何使用自带映像备份功能进行备份 Win8.1系统使用自带映像备份功能进行备份教程,感兴趣的朋友们可以跟
- 第一,word分栏常规设置word分栏如何使用呢?所谓word分栏就是将Word文档全部页面或选中的内容设置为多栏,从而呈现出报刊、杂志中经
- 当我们使用Mac的时候,有时候会把MAC中储存的资料误删掉,遇到这样的情况该怎么办呢?今天小编就为大家带来MAC储存的资料误删找回的方法,有
- 如何设置侧键开关功能呢?其实很简单,只要如下两步即可: &nb
- 在Word文档中如何快速导入邮件合并数据?很多朋友都不是很清楚,所以下面小编就为大家详细介绍一下,不会的朋友可以参考本文,希望能对大家有所帮
- Word是我们经常使用的一款办公软件!如何用word中的标尺对齐文字呢?下面小编为大家带来的是用word中的标尺对齐文字的方法!希望对大家会
- 蓝屏是电脑使用过程中常遇见的问题之一,但是不同的蓝屏代码解决方法都不同。如Win10系统出现win32kbase.sys蓝屏如何修复呢?下面
- wps office文档怎么找回?昨天给大家分享了——word边编辑边保存的功能! 但有时也难免出现突然断电?软件崩溃?系统中毒电脑突然关掉
- 联想电脑管家是一款相对来比较实用电脑安全软件,不过相比很多使用过联想电脑管家都不清楚其有个游戏模式吧,那么要如何开启联想电脑管家的游戏模式呢
- wps文字为用户提供了丰富的背景皮肤,如果大家觉得wps文字默认的背景皮肤不好看,那么,我们可以对其进行修改设置,下面就让小编告诉大家怎样设
- 1,星形接法和三角形接法都是指电机本身的绕组接法,星形接法指将电机绕组三相末端接在一起,三相首端为电源端。三角形接法指将三相绕组首尾互相连接
- 您可以随时更改演示文稿的主题。您现有的幻灯片继承新主题的属性,其中包括对象样式、标题样式等。如果自定了幻灯片上的任何属性,例如,更改幻灯片背
- 很多小伙伴发现新买的电脑几乎都是win10系统的,有些小伙伴用的不太习惯,想安装一个win7系统又不想把原来的Win10给删除了,那么win
- Win10的通知区域图标设置成为了“首批”进入Modern界面“设置”的项目之一,有一些从Win7/Win8.1升级而来的老用户也会因此感到
- 随着Win11的升级更新,有部分Win11用户觉得自己任务栏的图标很大,想要对任务栏图标大小进行设置但不清楚具体的操作方法。如果你也有这样的
- Excel经常会显一些错误值信息,如#N/A!、#VALUE!、#DIV/O!等等。出现这些错误的原因有很多种,最主要是由于公式不能计算正确
- 微软在Win11系统的资源管理器上添加了多标签功能,但并不是所有的用户都习惯这个功能,有什么办法可以关闭吗?小编在网上查询了很久,终于找到了
- macOS 系统莫名其妙就遇到声音和音频播放问题的情况相当普遍,在新添音频设备、应用程序之间进行切换或更新操作系统后,都可能会遇到音频错误。
- win10系统很多情况下都会自动进行更新,而不少用户是因为内存不足导致更新失败的关系,那么该怎么去解决win10内存不足导致更新失败怎么办呢
- Win10 Mobile预览版10536.1004上手体验,最新的Win10手机版10536修复了恼人的锁屏界面延迟Bug,在之前的1051