VBA如何打造一款实用型的Excel插件，附详细教程和测试代码 |
您所在的位置：网站首页 › excel工具栏按钮图标的功能在哪 › VBA如何打造一款实用型的Excel插件，附详细教程和测试代码 |
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】：删除掉多余的分组：；删除多余的按键，只保留需要的分组和按键就行。
需要修改的地方 修改后的菜单栏xml如下： tab label：我的插件【这个是挂在菜单栏最上面的名称】group label：数据处理【功能分区，可以有多个分组，每个分组可以指定不同的名称】botton lable：2列数据核实【功能分区，可以有多个子功能，每个功能指定不同的名称】onAction：两列数据核对【对应VBA主程序的名称】
根据需求，修改后的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代码改成：
新增1个功能按键的效果-【按键并排效果】 第2种，在已有的功能分区，新增1个功能按钮【下拉效果】：xml代码如下： 只需要在button语句的外围，增加menu分组语句：另外，button里面的图标的size设置，需要删除，否则会导致加载插件失败。
新增1个功能按键的效果-【按键下拉的效果】 第3种，在已有的功能分区，新增3个功能按钮【侧弹的效果】：xml代码如下： 只需要在button语句的外围，再继续增加menu分组语句：另外，menu、button里面的图标的size设置，需要删除，否则会导致加载插件失败。
新增3个功能按键的效果-【按键侧弹的效果】 3、给越来越丰富的大类功能选项卡，增加不同功能区分栏随着越来越多的内置功能，单靠1个下栏功能区，明显是不便于管理和使用，需要通过增加分栏的功能区实现区分。 下面增加3个新的内置功能，并设置功能分区： xml代码如下：
增加功能区新的分组的效果、并设置不同的功能图标 常用的菜单栏效果设置，到这里，已分享完了。 如果大家还有什么疑问，欢迎大家再下面留意讨论交流。 九、做个总结 插件以高度集成各类常用的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数据处理的干货技能知识。 |
【本文地址】
今日新闻 |
推荐新闻 |