不懂C#和VSTO,只懂VBA如何打造一款实用型的Excel插件,附详细教程和测试代码
职场老鸟
Excel | VBA | 插件制作 | 方案咨询
40 人赞同了该文章
目录
收起
一、网上各式各样Excel插件的现状
二、下面的文章将按照以下的章节结构,给大家介绍如何打造一个自己满意的Excel插件?
三、插件能解决数据处理工作中哪些痛点问题?
1、作为办公队伍中的数据gou,用Excel处理数据的常用手段有4种
2、最常用的数据分析处理工具都有哪些痛点?
3、Excel插件能解决哪些痛点问题
四、制作插件需要哪些VBA知识储备?
1、制作插件,需要学会哪些编程语言?
2、对于VBA语言的使用,需要掌握到什么程度?
五、插件的代码与普通VBA过程代码有什么不一样?
1、插件的代码需要申明回调
2、插件的代码-通常需要增加交互的语句
六、制作插件需要用到哪些辅助工具?
1、需要准备1款-office菜单、工具栏编辑器
2、如何使用编辑器,修改插件的各个显示参数、并绑定关联的VBA主程序?
七、如何把编制好的插件文件,内置到Excel菜单栏?
1、具体操作步骤如下:
2、检查加载后的效果
八、怎么把插件制做成多功能、多选项的效果?
1、给每个功能项增加对应的小图标,让功能选项看起来更有辨识度
2、给功能选项卡增加分支功能,把同类别的小功能集中收集到同一个大类的选项卡内
3、给越来越丰富的大类功能选项卡,增加不同功能区分栏
九、做个总结
【全文近15000字,花了一周时间整理编写,详细介绍在不懂C#,VSTO的情况下,如何只用VBA打造一款高度定制的Excel插件?】
毫无保留分享,码字不易,看完记得关注➕、点赞 、收藏⭐!感谢知友们的支持!
相信表格表妹们,工作当中都有使用过Excel插件的经历,或者看见身边的人使用过。
一、网上各式各样Excel插件的现状
- 基本上都是收费的。
- 内置的功能是基于通用的使用场景考虑
- 自定义设置的空间较小
- 某些自己特别想要的功能缺少
作为对于Excel处理数据有着执着追求的小伙伴们,有没办法自己打造一款更简单、更好用的插件呢?
二、下面的文章将按照以下的章节结构,给大家介绍如何打造一个自己满意的Excel插件?
阅读文章,可以获得以上的干货知识
三、插件能解决数据处理工作中哪些痛点问题?
1、作为办公队伍中的数据gou,用Excel处理数据的常用手段有4种
最常用的Excel数据处理分析手段工具
2、最常用的数据分析处理工具都有哪些痛点?
最常用的数据分析处理工具都有哪些痛点
3、Excel插件能解决哪些痛点问题
Excel插件可以集成以下功能:
- 集成日常使用的自定义函数功能,方便随时调用;
- 集成常用的自定义图表模板,方便直接调用;
- 集成自定义的数据统计功能,方便直接调用模板化的统计功能;
- 集成VBA常用的代码小工具,集中管理,需要用的时候直接调用。
Excel插件,可以集成4大类的数据分析处理功能
四、制作插件需要哪些VBA知识储备?
1、制作插件,需要学会哪些编程语言?
- 作为入门级的插件制作,不需要,高大上的C#语言
- 同样不需要http://VB.NET语言
- 只需要懂常用的VBA编程语言,就可以无缝转换成插件代码
2、对于VBA语言的使用,需要掌握到什么程度?
- 能够掌握常用的VBA语句的使用:
比如:判断语句if…then….,select case……end select…..;循环语句fox…next….,do…loop….
- 能够使用常用的对象属性、方法:
比如:worksheets、workbooks、range等对象
- 能够理解并可以使用数组、字典等对象工具,对于日常的代码进行优化提升
- 能够使用VBA调试窗口,对错误的代码进行调试并修改错误
- 总而言之,对于日常的VBA代码可以读懂,可以模仿,可以改进。
Excel插件制作,对于VBA技能的要求
五、插件的代码与普通VBA过程代码有什么不一样?
1、插件的代码需要申明回调
这里以一个简单的小功能【2列数据的差异标识】为例,说明二者的代码差异。
案例需求说明:
根据A、B 2列的员工姓名数据,标识2列数据的差异情况,需要区分A列B列都有、A列有B列没有、A列没有B列有3种情况。
源数据是A、B列,处理结果是D、E、F列
- 普通的VBA代码,可以这样写:
Sub 两列数据核对()
Dim d As Object, Rng As Range, strTemp As String, myReturnNum As Integer
Dim i As Long, n1 As Long, n2 As Long, n3 As Long
Dim arr1, arr2, brr, kr As Variant
Set d = CreateObject("scripting.dictionary")
Set Rng = Application.InputBox("请选择需要核对差异的第一列数据", "选择数据", Type:=8) '选取的数据不含标题行
Set Rng = Intersect(Rng.Parent.UsedRange, Rng)
If Not Rng Is Nothing Then arr1 = Rng.Value
Set Rng = Application.InputBox("请选择需要核对差异的第二列数据", "选择数据", Type:=8) '选取的数据不含标题行
Set Rng = Intersect(Rng.Parent.UsedRange, Rng)
If Not Rng Is Nothing Then arr2 = Rng.Value
i = WorksheetFunction.Max(UBound(arr1), UBound(arr2)) '计算两列最大行数
ReDim brr(0 To i, 1 To 3) '定义结果存放数组 '第一列放AB均存在的数据 '第二列放A有B没有的数据 '第三列放B有A没有的数据
'A列的数据装载到字典中,设置item为不存在
For i = 1 To UBound(arr1)
If Len(Trim(arr1(i, 1))) Then
d("'" & Trim(arr1(i, 1))) = "不存在"
End If
Next
'B列的数据,与A列存入字典的数据进行对比,如果存在,把Item值改成“存在”,如果不存放,差异的数据存储到结果数组brr(n3, 3)
For i = 1 To UBound(arr2)
If Len(Trim(arr2(i, 1))) Then
strTemp = "'" & Trim(arr2(i, 1))
If d.Exists(strTemp) Then '如果A有B也有,修改字典的Item值,并存入结果数组brr
n1 = n1 + 1
brr(n1, 1) = strTemp
d(strTemp) = "存在"
Else '如果B有A没有,存入结果数组brr
n3 = n3 + 1
brr(n3, 3) = strTemp
End If
End If
Next
kr = d.Keys '对比后,已修改的新字典,“存在”、“不存在”的数据存储到数组。
For i = 0 To UBound(kr)
If d(kr(i)) = "不存在" Then '如果A有B没有,存入结果数组brr
n2 = n2 + 1
brr(n2, 2) = kr(i)
End If
Next
brr(0, 1) = "两列均存在的数据有" & n1 & "条;"
brr(0, 2) = "A有B没有的数据有" & n2 & "条;"
brr(0, 3) = "B有A没有的数据有" & n3 & "条。"
myReturnNum = MsgBox("核对完成:" & vbLf & brr(0, 1) & vbLf & brr(0, 2) & vbLf & brr(0, 3), vbYesNo, "是否查看结果数据?")
If myReturnNum = 6 Then '对话框,如果选择“是”,输出核对的结果。
Set Rng = Application.InputBox("请选择放置查询结果的单元格起始位置,例如C1,结果会占用3列!", "选择单元格", Type:=8) '选取输出的结果存放位置
Rng.Resize(UBound(brr) + 1, 3) = brr
End If
End Sub
- 插件使用的代码,可以这样写:
Sub 两列数据核对(control As IRibbonControl)
Dim d As Object, Rng As Range, strTemp As String, myReturnNum As Integer
Dim i As Long, n1 As Long, n2 As Long, n3 As Long
Dim arr1, arr2, brr, kr As Variant
Set d = CreateObject("scripting.dictionary")
Set Rng = Application.InputBox("请选择需要核对差异的第一列数据", "选择数据", Type:=8) '选取的数据不含标题行
Set Rng = Intersect(Rng.Parent.UsedRange, Rng)
If Not Rng Is Nothing Then arr1 = Rng.Value
Set Rng = Application.InputBox("请选择需要核对差异的第二列数据", "选择数据", Type:=8) '选取的数据不含标题行
Set Rng = Intersect(Rng.Parent.UsedRange, Rng)
If Not Rng Is Nothing Then arr2 = Rng.Value
i = WorksheetFunction.Max(UBound(arr1), UBound(arr2)) '计算两列最大行数
ReDim brr(0 To i, 1 To 3) '定义结果存放数组 '第一列放AB均存在的数据 '第二列放A有B没有的数据 '第三列放B有A没有的数据
'A列的数据装载到字典中,设置item为不存在
For i = 1 To UBound(arr1)
If Len(Trim(arr1(i, 1))) Then
d("'" & Trim(arr1(i, 1))) = "不存在"
End If
Next
'B列的数据,与A列存入字典的数据进行对比,如果存在,把Item值改成“存在”,如果不存放,差异的数据存储到结果数组brr(n3, 3)
For i = 1 To UBound(arr2)
If Len(Trim(arr2(i, 1))) Then
strTemp = "'" & Trim(arr2(i, 1))
If d.Exists(strTemp) Then '如果A有B也有,修改字典的Item值,并存入结果数组brr
n1 = n1 + 1
brr(n1, 1) = strTemp
d(strTemp) = "存在"
Else '如果B有A没有,存入结果数组brr
n3 = n3 + 1
brr(n3, 3) = strTemp
End If
End If
Next
kr = d.Keys '对比后,已修改的新字典,“存在”、“不存在”的数据存储到数组。
For i = 0 To UBound(kr)
If d(kr(i)) = "不存在" Then '如果A有B没有,存入结果数组brr
n2 = n2 + 1
brr(n2, 2) = kr(i)
End If
Next
brr(0, 1) = "两列均存在的数据有" & n1 & "条;"
brr(0, 2) = "A有B没有的数据有" & n2 & "条;"
brr(0, 3) = "B有A没有的数据有" & n3 & "条。"
myReturnNum = MsgBox("核对完成:" & vbLf & brr(0, 1) & vbLf & brr(0, 2) & vbLf & brr(0, 3), vbYesNo, "是否查看结果数据?")
If myReturnNum = 6 Then '对话框,如果选择“是”,输出核对的结果。
Set Rng = Application.InputBox("请选择放置查询结果的单元格起始位置,例如C1,结果会占用3列!", "选择单元格", Type:=8) '选取输出的结果存放位置
Rng.Resize(UBound(brr) + 1, 3) = brr
End If
End Sub
- 对比可以看出,二者主要的区分是:
主体的程序完全一样,插件的代码多了回调参数的申明,增加了功能区控件对象申明的代码如下:
Sub 两列数据核对(control As IRibbonControl) 'control As IRibbonControl,申明为菜单栏功能区控件对象
2、插件的代码-通常需要增加交互的语句
- 比如,数据输入的交互语句【作用:引导用户对数据的交互操作】
'输入语句的交互:
Set Rng = Application.InputBox("请选择需要核对差异的第一列数据", "选择数据", Type:=8) '选取的数据不含标题行
Set Rng = Application.InputBox("请选择需要核对差异的第二列数据", "选择数据", Type:=8) '选取的数据不含标题行
输入交互过程1:
通过对话框的形式提示,需要选择输入数据:第1列数据
输入交互-提示选择数据的对话框1
输入交互过程2:
通过对话框的形式提示,需要选择输入数据:第2列数据
输入交互-提示选择数据的对话框2
- 比如,数据输出的交互语句
'输出语句的交互
Set Rng = Application.InputBox("请选择放置查询结果的单元格起始位置,例如C1,结果会占用3列!", "选择单元格", Type:=8) '选取输出的结果存放位置
通过对话框的形式提示,需要选择存放的结果数据的位置:
输出交互-提示输出结果放在哪个位置
- 比如,处理过程中,出现错误的提示语句【作用:引导用户对于错误出现的时候,如何做下一步的处理】
比如在输入提示框的后面,可以增加以下语句,起到提示用户操作不正确的作用:
'对于单列的数据判断,可以设置每次输入的数据,仅限于单列,多于1列以上的数据选择提示正确操作:请选择单列数据
If rng1.Columns.Count > 1 Then MsgBox "请选择单列数据。", , "温馨提示": Exit Sub
'对于只选中单个单元格的数据,明显不需要程序处理,可以设置提示:不能选择单个单元格
If rng1.Rows.Count = 1 Then MsgBox "不能选择单个单元格。", , "温馨提示": Exit Sub
错误操作提示1
错误操作提示2
六、制作插件需要用到哪些辅助工具?
1、需要准备1款-office菜单、工具栏编辑器
【工具名称】:Office Custom UI Editor
【中文名称】:office菜单、工具栏编辑器
【软件用途】:Office2007以上版本是一个完全开放式的界面,开发者可以自己定义Ribbon工具栏和菜单,而Office Custom UI Editor可以轻松地实现这一自定义的功能,需要使用xlm语言编辑。
【哪里下载】:百度一下,可以搜索到很多下载的链接,找一个下载安装就行。
【软件界面】:安装后如下图,十分的简洁小巧
编辑器的初始界面
2、如何使用编辑器,修改插件的各个显示参数、并绑定关联的VBA主程序?
- 插件的菜单栏的显示效果,类似这样:
新增的菜单栏
- 编制菜单栏的显示效果有什么结构套用?
打开Office Custom UI Editor,导入已编制好的VBA加载宏文件:我的插件.xlam
第一步:导入加载宏,编辑器初始的界面
第二步,选择xml编辑框架模板
选择插入默认的xml模板后的样子
把宏文件,加载到excel后的效果-默认xml模板
- 现在的问题是,以上按照的默认模板新增的菜单栏的效果,明显不是我们想要的,如何修改成自己喜欢的样子?
下面对xml模板的框架,进行自定义的修改:
【修改1】:把默认的标签【lable】的名称改成:自定义的名称
【修改2】:把关联的触发程序【onAction】改成:自定义的程序名称
【修改3】:删除掉多余的分组:<group…../>;删除多余的按键<button…../>,只保留需要的分组和按键就行。
需要修改的地方
修改后的菜单栏xml如下:
- tab label:我的插件【这个是挂在菜单栏最上面的名称】
- group label:数据处理【功能分区,可以有多个分组,每个分组可以指定不同的名称】
- botton lable:2列数据核实【功能分区,可以有多个子功能,每个功能指定不同的名称】
- onAction:两列数据核对【对应VBA主程序的名称】
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<ribbon>
<tabs>
<tab id="customTab" label="我的插件">
<group id="customGroup" label="数据处理">
<button id="customButton1" label="2列数据核实" size="large" onAction="两列数据核对" imageMso="Bold" />
</group>
</tab>
</tabs>
</ribbon>
</customUI>
根据需求,修改后的xml,这里只保留1个分组,1个按钮功能
修改后的菜单栏效果如下:
菜单名称:【我的插件】
功能选项名称:【2列数据核实】
功能分区的名称:【数据处理】
加载后,符合要求的菜单栏效果,整个界面干净清爽多了!
下面需要测试加载后的自定义菜单,是否可以正常调用主程序:【两列数据核对】?
测试的视频如下:
00:30
七、如何把编制好的插件文件,内置到Excel菜单栏?
1、具体操作步骤如下:
- 第1步、把编写好的VBA程序文件,另存为xlsm格式文件:我的插件.xlsm【保存代码,便于功能调试使用】
普通宏文件
- 第2步、我的插件.xlsm文件,另存为加载宏文件,我的插件.xlam【用于菜单栏编辑器,修改加载到菜单栏的效果样式】
可以加载宏文件
- 第3步、打开菜单栏编辑器,导入加载宏文件:我的插件.xlam,按照上面的样式修改操作,进行自定义的效果修改。
- 第4步、保存样式修改好的加载宏文件:我的插件.xlam。
- 第5步、以上操作操作完,接下来正式加载宏文件到Excel。【开发工具】-【excel加载项】-
【浏览】-选择-上面修改好样式的加载宏文件:我的插件.xlam,加载后的效果如下:可以看到【我的插件】,前面大上√。到此,插件已加载完成。
第1个插件,已加载到Excel
2、检查加载后的效果
随便打开1个Excel文件,可以看到Excel顶端的的菜单栏上,已多了1个新的菜单项【我的插件】,说明插件内置到Excel已成功了!
加载成功!恭喜你,可以正常使用了。
八、怎么把插件制做成多功能、多选项的效果?
以上的插件制作加载步骤完成后,Excel插件基本上可以正常使用了,但是我们发现还远远不够完善、不够完美。
下面将从3个方面,对简单甚至有点丑陋的插件,做进一步的美化和调整:
1、给每个功能项增加对应的小图标,让功能选项看起来更有辨识度
- 首先给插件增加1个有温度的小图标
用XML编辑器,重新打开加载宏文件:我的插件.xlam
【Insert】-【Icons…】,导入一张符合尺寸大小的图片(尺寸建议:32*32),经测试这个显示的效果比较好。
导入图标文件
这里导入1个叫”shujuchayi”的小图片:
给导入图标
还需要把xml文件的图标文件的名称修改成:shujuchayi
修改图标的名称
保存宏文件后,重新打开excel文件:可以看到插件的图标已成功修改!
修改图标后的插件效果
下面这些内容,如果感兴趣的人多的话,后续完善更新:
2、给功能选项卡增加分支功能,把同类别的小功能集中收集到同一个大类的选项卡内
- 第1种,在已有的功能分区,新增1个功能按钮【并排效果】:
xml代码改成:
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<ribbon>
<tabs>
<tab id="customTab" label="我的插件">
<group id="customGroup" label="数据处理">
<button id="customButton1" label="2列数据核实" size="large" onAction="两列数据核对" image="shujuchayi" />
<button id="customButton2" label="工资条生成" size="large" onAction="工资条生成" image="shujuchayi" />
</group>
</tab>
</tabs>
</ribbon>
</customUI>
新增1个功能按键的效果-【按键并排效果】
- 第2种,在已有的功能分区,新增1个功能按钮【下拉效果】:
xml代码如下:
只需要在button语句的外围,增加menu分组语句:另外,button里面的图标的size设置,需要删除,否则会导致加载插件失败。
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<ribbon>
<tabs>
<tab id="customTab" label="我的插件">
<group id="customGroup" label="数据处理">
<menu id="menu1" label="批量处理" image="shujuchayi" size="large" itemSize="large">
<button id="customButton1" label="2列数据核实" image="shujuchayi" onAction="两列数据核对" />
<button id="customButton2" label="工资条生成" image="shujuchayi" onAction="工资条生成" />
</menu>
</group>
</tab>
</tabs>
</ribbon>
</customUI>
新增1个功能按键的效果-【按键下拉的效果】
- 第3种,在已有的功能分区,新增3个功能按钮【侧弹的效果】:
xml代码如下:
只需要在button语句的外围,再继续增加menu分组语句:另外,menu、button里面的图标的size设置,需要删除,否则会导致加载插件失败。
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<ribbon>
<tabs>
<tab id="customTab" label="我的插件">
<group id="customGroup" label="数据处理">
<menu id="menu1" label="批量处理" image="shujuchayi" size="large" itemSize="large">
<button id="customButton1" label="2列数据核实" image="shujuchayi" onAction="两列数据核对" />
<button id="customButton2" label="工资条生成" image="shujuchayi" onAction="工资条生成" />
<menu id="menu2" label="提取字符" image="shujuchayi" itemSize="large">
<button id="customButton3" label="提取中文" image="shujuchayi" onAction="提取中文" />
<button id="customButton4" label="提取英文" image="shujuchayi" onAction="提取英文" />
<button id="customButton5" label="提取数字" image="shujuchayi" onAction="提取数字" />
</menu>
</menu>
</group>
</tab>
</tabs>
</ribbon>
</customUI>
新增3个功能按键的效果-【按键侧弹的效果】
3、给越来越丰富的大类功能选项卡,增加不同功能区分栏
随着越来越多的内置功能,单靠1个下栏功能区,明显是不便于管理和使用,需要通过增加分栏的功能区实现区分。
下面增加3个新的内置功能,并设置功能分区:
xml代码如下:
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<ribbon>
<tabs>
<tab id="customTab" label="我的插件">
<group id="customGroup1" label="数据处理">
<menu id="menu1" label="批量处理" image="shujuchayi" size="large" itemSize="large">
<button id="customButton1" label="2列数据核实" image="shujuchayi" onAction="两列数据核对" />
<button id="customButton2" label="工资条生成" image="shujuchayi" onAction="工资条生成" />
<menu id="menu2" label="提取字符" image="shujuchayi" itemSize="large">
<button id="customButton3" label="提取中文" image="shujuchayi" onAction="提取中文" />
<button id="customButton4" label="提取英文" image="shujuchayi" onAction="提取英文" />
<button id="customButton5" label="提取数字" image="shujuchayi" onAction="提取数字" />
</menu>
</menu>
</group>
<group id="customGroup2" label="表格操作">
<button id="customButton6" label="汇总工作表数据" image="workbook" size="large" onAction="汇总工作表数据" />
<button id="customButton7" label="汇总工作簿数据" image="workbook" size="large" onAction="汇总工作簿数据" />
<button id="customButton8" label="生成表格目录" image="workbook" size="large" onAction="生成表格目录" />
</group>
</tab>
</tabs>
</ribbon>
</customUI>
增加功能区新的分组的效果、并设置不同的功能图标
常用的菜单栏效果设置,到这里,已分享完了。
如果大家还有什么疑问,欢迎大家再下面留意讨论交流。
九、做个总结
- 插件以高度集成各类常用的VBA代码功能,大受众多办公室、生产车间数据人员的厚爱!
- 市面上的插件众多,为何还要自己再造轮子?因为收费的并不是最适合自己的!当然,也有像我一样喜欢自己折腾的。
- 用VBA制作插件的工具非常简单,就2个,1个是Excel本身,1个菜单栏编辑器工具!
- 用VBA制作插件的语言,涉及2种,1个是VBA本身,1个就是编辑栏效果需要的XML语言!不懂C#,不懂VSTO一样能制作插件!
- 用VBA转换成成插件的代码,简单的只需要增加一句回调语句:control As IRibbonControl。再复杂点就是增加一些输入的操作提示语句,更加专业点可以增加输入的小窗体,操作的选项全部在窗体内完成!
- 使用xml模板框架修改自定义菜单栏,需要注意各类分组、分项、按钮功能的id名称不能重复,另外设置图标,如果含有多层分组的size大小需要注意内层分组继承外层的size大小,内层分组不需要重复设置。
- 以上的流程熟悉之后,从普通的VBA代码转成Excel插件,可能只需要不到10分钟的时间。门槛非常低!上手相当容易!
- 追求高效率处理数据小伙伴们,可以尝试把常用的功能插件化。相信你也可以成为身边的数据处理、数据分析大神!
最后来个大总结
都看到这里了,别吝啬你的小手指啦,点点赞,点点关注,还没掌握的小伙伴先收藏!
我是专注Excel和VBA的
@职场老鸟
,关注我,不迷路,带你解锁更多VBA数据处理的干货技能知识。
今天的文章vba制作插件_VBA实用代码 office分享到此就结束了,感谢您的阅读。
版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
如需转载请保留出处:https://bianchenghao.cn/80059.html