学习资源:《Excel VBA从入门到进阶》第80集 by兰色幻想
最后一节课了,加油!
一、封装自定义函数
封装可以在工作表中使用的自定义函数和79集一样,只是加载时多了一个步骤。
1. 创建步骤省略,下面是要封装的自定义函数,计算不重复值。
Function OnlyCount(rg As Excel.Range) Dim d As Object Dim arr, sr, r Set d = CreateObject("scripting.dictionary") arr = rg For Each r In arr d(r) = "" Next r OnlyCount = d.Count End Function
2. 加载dll中的自定义函数
① 注册dll控件:用管理员身份打开命令提示符,输入regsvr32 放置路径,如:
regsvr32 C:\Users\Admin\Documents\functionfeng.dll
② 在菜单栏,依次点击开发工具--Excel加载项--自动化,找到在VB中命名的工程名称和类模块名组合成的文字,选取它确定,回到加载宏对话框中,已出现该函数项目。
3. 使用方法,和一般的excel函数使用方法相同。
二、COM加载宏_事件程序
如果想用com加载宏的形式替代在excel的写EXCEL对象事件,可以在VB里编写好事件程序,封装到DLL里,然后注册DLL。
VB代码编写
和函数封装不同,com加载宏需要在VB里添加"外接程序"。
设计器的设置如下:
打开代码窗口,可以看到已经写了代码,删除不必要的代码,重新写。
代码详解:
① 声明一个Excel变量,让它能响应Excel程序事件。
Public WithEvents elevent As Excel.Application
② AddinInstance_OnConnection相当于Excel里的open事件,随时程序的打开而自动加载。
Private Sub AddinInstance_OnConnection(ByVal Application As Object, ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, ByVal AddInInst As Object, custom() As Variant) Set elevent = Application '打开Excel时启用elevent变量(Excel变量) End Sub
③ AddinInstance_OnDisconnection事件相当于Excel里的Close事件,在程序关闭时运行。
Private Sub AddinInstance_OnDisconnection(ByVal RemoveMode As AddInDesignerObjects.ext_DisconnectMode, custom() As Variant) Set elevent = Nothing '释放变量 End Sub
④ NewWorkbook事件,新建工作簿。
Private Sub elevent_WorkbookNewSheet(ByVal Wb As Excel.Workbook, ByVal Sh As Object) Sh.Name = "123" End Sub
2. 封装和注册
和封装自定义函数一样,保存工程,以便后续修改,再生成DLL文件。注册步骤见上面的函数注册,做法一样的。
3. 在Excel中加载
在菜单栏,开发工具-COM加载项--添加--找到刚刚做好的DLL文件--确认。
三、Com加载宏之菜单加载
先放个复习链接
菜单加载也可以用VB封装,把写在Custom UI Editor的语句,写到VB的外接程序中:
IRibbonExtensibility_GetCustomUI函数可以获取XML代码,并且创建接口。
Public Function IRibbonExtensibility_GetCustomUI(ByVal RibbonID As String) As String Dim sr As String sr = "<customUI xmlns=""http://schemas.microsoft.com/office/2006/01/customui"">" sr = sr & "<ribbon>" sr = sr & "<tabs>" sr = sr & "<tab id=""tab1"" label=""我的com功能区"">" sr = sr & "<group id=""g1"" label=""我的组"">" sr = sr & "<button id=""b1"" label=""只是测试一下"" onAction=""AA"" size=""large"" imageMso=""Copy""/>" sr = sr & "<separator id=""S1"" />" sr = sr & "<button id=""b2"" label=""输入100"" onAction=""AA"" imageMso=""Paste""/>" sr = sr & "<button id=""b3"" label=""删除100"" onAction=""AA"" imageMso=""Piggy""/>" sr = sr & "</group>" sr = sr & "</tab>" sr = sr & "</tabs>" sr = sr & "</ribbon>" sr = sr & "</customUI>" IRibbonExtensibility_GetCustomUI = sr End Function
回调代码:
Public Function AA(ByVal control As Office.IRibbonControl) Select Case control.Id Case "b1" MsgBox "点我是为了测试一下,嘿嘿!", 1 + 64, "兰色幻想的com加载宏" Case "b2" xlapp.ActiveSheet.Range("a1") = 100 Case "b3" xlapp.ActiveSheet.Range("a1") = "" End Select End Function
写好之后,保存工程和生成DLL文件,一样的注册和加载方法,自定义的功能区出现在我们的菜单栏中。