VBA代码封装过程

1、VBA代码封装过程一、函数和子过程的封装过程二、封装后函数和子过程的调用三、窗体的封装和调用四、 com 加载项的制作五、利用com 加载项制作功能区如果你写VBA代码只是给自己使用或者不介意和别人共享代码， 那么就用不到封装。我们一般用VB6.0作为VBA代码的封装工具，VBA代码的封装在 我理解就是将VBA代码修改成VB6.0能运行的代码，然后利用 VB6.0 可以制作dll 文件的特点将所有代码封装在dll 文件中， 最后我们可以通过安装加载项或者引用的方式在工作表界面或VBA 界面调用其中的函数、子过程和窗体。下面让我们具体看下VBA 代码的封装过程(封装调用环境：xp 系统，exce

2、l2007， VB6.0) ：一、函数和子过程的封装过程函数和子过程也就是function过程和sub过程。封装的过程如下：1、在 vba 编写代码，此次实验的代码如下,包含一个函数过程和一个子过程：Function ceshi(a, b) ceshi = a + bEnd FunctionSub ceshi1()MsgBox Application.Name End Sub2、打开VB6.0,新建工程，选择 ActiveX dll,然后点打开。3、点打开后在该工程中可以看到工程自带一个类模块，可以修改工程名字和类模块名字，也可以选择默认的工程名和类模块名， 修改工程名和类模块名的过程如下，在

3、VB6.0界面右边工程资源管理器 窗口（如果没看见该窗口，可点击菜单栏中“视图”菜单的工程资源 管理器命令调用）选择工程名（类模块名），然后在下面属性窗口 ”名 称” 一行修改成自己想要的名称，这里我们将工程名修改成fengzhuang,类模块名修改成clsl94、右键点击类模块，在弹出的快捷菜单中选择查看代码，然后将VBA代码复制到类模块代码窗口中，部分代码要稍作修改，改成 VB6.0能识别的代码。如上面的子过程中出现了application对象，代表excel应用程序，但是在vb6.0直接这样写会出现错误，VB6.0并不 能识别这就是一个excel应用程序对象，就像我们在 VBA中直接写

4、Dictionary （字典对象）和regexp （正则对象）一样，VBA并不能识别， 要VBA能识别这两个对象，要么是创建新的对象（前期绑定和后期 绑定），要么是获取已有的对象（前期绑定和后期绑定），因为我们在 VBA中运行代码时，excel应用程序已经存在了，所以我们只需要获 取已有对象即可。下面介绍前期绑定和后期绑定修改我们的代码。A、前期绑定。在VB6.0最上面工程菜单中点击引用，在弹出的 引用对话框中选择 microsoft excel 12.0 object library,中间的数字因为 安装版本的不同会有变化，2003是11.0、2010是14.0,尽量选择最 高版本的，子过程

5、代码无需修改。弓 I用-fenzhuanE优先旗可用的引用(A):Nicrosofl Disk Quota 1.0Microsoft DT BDS TypeLib 2I I II I Illi I II JI IB II JJJI II IB ll!_ll II J|MI 1(|，U”igrofgft 1,4 Q Ubj电gt LjbrHT.Microsoft DTSDat近所p Scripting Objset Lib.g 1. IHicrosoft Feeds 2.0 Object LibraryMicrosoft Graph 14. 0 Object LibraryNicrosofl H

6、323 Service Provider 1.0 Type LMicrosoEt Kelp Data Services 1.0 Type LibrHicrosoft HTML Object LibraryMicrosoft IHAFI2 EIoe FurictionalityMicrosoft IMAII2 File System 工m单群 Creator Mii n Te,Microsoft Eited. 14. 0 Object Library定位：C:Prosran)FilEMi crosoft 0ffi ce 1 0ff 1 ce 14EXCEL EXE 语音标准B、后期绑定。后期绑定

7、比较方便，不需要在 VB6.0中设置引用， 只需要修改子过程的代码即可。修改如下，中间用了 getobject函数 用于获取excel应用程序对象：Sub ceshi1()Set a = GetObject(, excel.application)MsgBox a.NameSet a = Nothing End Sub在这里个人推荐使用后期绑定的方法修改代码，其实还有一种方 法也可以使代码正常运行，无须设置引用，也无须用 getobject获取 对象，只需要为子过程设置一个 object参数，然后在VBA中调用的 时候向子过程传递应用程序这个参数即可。在 VB6.0的代码如下：Sub cesh

8、i1(obj1 as object)MsgBox obj1.NameEnd Sub在vba中调用ceshi1子过程时向ceshi1传递参数application,写 成 ceshi1 (application)o5、生成dll文件。点击vb6.0文件菜单中生成dll命令生成dll文件engzhuang - Microsoft Visual Basic 设计汝:件征)下辑 视图9 工程区)格式)调试 运行 查询QP 图表9 工具H)外接前 新建工程cui+ffI. 髭回名哲注目工打开工程电).Ctri+a1(Code)添加工程). 移除工程()Q保存工程W)工程另存为现三| ce:Ctrl+ST

9、in,保存dsl clsl 另存为(A).- 保存选择f，excL. applicition-T J最后还要提到的一点是，因为上面测试用的代码比较简单，没有 讲到向函数传递对象时代码的封装过程，这里简单介绍下，比如在VBA中函数function ab(rng as range渔VB6.0中应该怎样写呢，第一 种是先设置引用 microsoft excel 12.0 object library ,然后将VBA函数 代码直接复制到VB6.0即可。第二种不需要设置引用，直接修改函数 将 range改成 object 即可，function ab(rng as object),在后期调用函 数的时候

10、直接传递单元格对象。至此，函数和子过程的代码封装就讲完了。二、封装后函数和子过程的调用封装后的函数和子过程如何调用呢，具体过程如下(以前面的函数和子过程为例)：1、在工作表中调用函数在工作表中可以调用部分简单的函数，部分需要传递对象的函数无法调用，如需要传递应用程序对象、控件对象等函数无法调用。调用过程如下：A、添加加载项。依次点击 excel2007按钮excel选项加载项，在加载项界面最下方选择excel 加载项，然后点击转到，出现加载宏对话框，点击自动化按钮，在自动化服务器对话框中找到我们要加载的对象，一般是工程名.类模块名的形式(fengzhuang.cls1) ，然后点击确定返回加载

11、宏对话框，点击确定。B、添加完加载项后就可以直接在工作表调用函数了，就像调用工作表内部函数一样。2、在VBA中调用函数和子过程。在 VB6.0封装的函数和子过程一般都可以在VBA中调用，具体过程如下：因为我们在vb6.0是用类模块封装的代码，如果要调用类模块中的函数和子过程，就先要创建一个类的实例，就像我们在VBA中创建一个类以后在调用其中的过程一样。创建类的实例有前期绑定和后 期绑定两种方法:前期绑定A.先在VBA中设置引用。点击VBA中工具菜单中引用命令，弹 出引用对话框，点击浏览按钮，找到封装文件(fengzhuang.dll),点 击打开返回引用对话框，这时引用对话框中已经选中我们要引

12、用的工 程(fengzhuang)点击确定完成引用。B、创建类的实例，在代码中调用过程。在 VBA中先用Dim a AsNew fengzhuang.clsl创建类实例，然后调用过程，测试效果如下:后期绑定创建类的实例使用后期绑定的方法无须设置引用，只要用createobject函数即可。代码和测试效果如下：End 3ubSub调用IQSet a - Cr电式工hu蝇g, clwl附）创建娄之洌 a cshil调用于过程End Sul至此，函数和子过程的封装过程就讲完了， 上面封装的过程其实 就是在vb6.0中创建一个类的过程，然后在VBA中通过创建类的实例 在调用实例的方法。三、窗体的封装和

13、调用过程窗体的封装大概过程是先在 excel VBA中将窗体文件导出，然后 在VB6.0中导入窗体文件，修改窗体的某些代码，在类模块中添加调 用窗体的过程，最后封装成 dll文件。窗体的调用与前面子过程的调 用相似。1、窗体的封装A、在excelVBA中设计好窗体和代码。如下图所示，测试用窗体 包含一个按钮，窗体代码包含一个事件过程。:图匾A fl 0Cobb andBaltonlPrivate Sub CoffimandButtonl _Cli ck ()MsgBox Appli uati on, NameEnd SubI *百任立丘+ 逐 VBAProjct (?m -麒 VBATroje

14、ct 盘更 理 Sheetl (Sheetl 翻 1 Shaet2 (Shcet2 宜 5h”t3 (Sheet3 5 Thi Workbook 国 UserForml 离模块IB、将窗体导出。在VBA工程资源管理器中选择窗体点击右键， 在弹出的快捷菜单中选择导出文件，在导出文件对话框中选择导出文 件的保存位置。这里将文件保存至桌面并选用默认的文件名 (userforml)。C、在VB6.0中导入窗体文件。在vb6.0中新建工程（比较懒，没 有重新新建工程，用的是前面封装函数和子过程的工程 ），点击工程 菜单栏添加窗体命令，在弹出的添加窗体对话框中选“现存” ，找到 先前导出的窗体文件，然后点

15、打开完成窗体的导入过程，这时我们可 以看到工程中多了窗体文件。D、修改窗体代码。右键点击工程资源管理器中窗体文件，选择 查看代码，在窗体代码窗口中修改代码。这里修改代码的过程与前面 修改子过程的代码相似，该窗体事件的代码修改如下：Private Sub CommandButton1_Click()Set a = GetObject(, excel.application)MsgBox a.NameEnd SubE、在类模块中添加调用窗体的代码。在类模块代码窗口添加如下代码，这个是方便我们以后在 VBA中调用窗体用的。Sub ad()UserForml.Show 0End SubF、生成dll文

16、件，完成封装，这一步与前面封装函数和子过程的步骤一样。2、窗体的调用窗体的调用与前面调用函数和子过程的方法有点不同， 通过调用 类模块中的过程（ad）间接调用窗体。代码及效果如下:Sul调用窗体。Dim a Am Newclsla.右d End Sub回XerFoEB.1窗体的封装与调用与函数和子过程的基本类似，只是窗体在封装 时要先导入窗体文件到 VB6.0中，调用的时候只能通过类模块中的方 法间接调用窗体。四、com加载项的制作在这里学习com加载项的制作是为了后面封装自定义命令栏和 功能区的代码。1、在excel中加载com加载项。依次点击 ofice按钮一excel选项一加载项，在加载

17、项界面的最下方选择 com加载项，然后点击转 到按钮，在com加载项界面勾选需要加载的com加载项即可，如果 需要加载的com加载项不在com加载项的列表框中，可点击旁边的 添加按钮添加包含com加载项的文件，然后在进行加载33IsxccL选项I?帚用狂 百期目战信任中心贲源金Microsoft Offfce客容片w跷A活动应用程库加轼膜Chinese Translation AddinC,.OfficeOfficsl2ADDIN5TCSCCONV.DLLCOMtPSoffice special symbol mput Add-inC：- 0+fi0ffil 2AD DINSSYMJNPin.

18、DLLCOM力蕾琮Office Wide Symbol Input七席前任 1OFFICE11ADDINSMI N PU T.DU.8M1B炳OffiaLE*%l加始质分析ISJS - VB45 J 通 12Llbra ryAna 1 产 lATPv 日 aemxlamEkcpI和插侬处划求蜂加班5C：V.A0fficel2MJb raryS0LVE RSOLVER.XLAMExcel加爰读欧元工层urotooLxlamEJtcel加皎出AS (Outlook电子在甘收件人C:.Jcrosoft ShardSmart TagFNAME-DLL智甑记7-.一 1- - -Chinese Tran

19、slation Addin澄谷杳：Mtro?oft Corporation位苴：C;Pn?g ran FileAMicrsoft OffiteXOfficel 2AV DINSMCSCCO NV.DILiKfiA：: COM addin th3t translates between Traditional Chinese and Simplified Chinese.赠，“2、com加载项的制作。Com加载项的制作这里还是要用到 VB6.0, 大概过程是在新建的工程中添加一个设计器类， 修改设计器类的加载 行为，书写代码后封装成dll文件或者exe文件都可以。制作com加 载项的具体过程如下

20、：(1)新建工程，添加设计器类。打开VB6.0,在新建工程窗口选择activeX.EXE Active.DLL或者外接程序中的一个，有些地方讲的是 要选择外接程序，其实选择另外两个也可以，只是外接程序会自带一 个设计器类。这里选择的是active.dll,选择好新建工程的类型以后点 打开，在工程资源管理器界面我们看到该工程只带有一个类模块，这时我们需要添加一个设计器类，点击VB6.0工程菜单中“添加AddinClas命令为工程添加一个设计器类。如果没有“添加Addin Class这个命令，可点击VB6.0工程菜单部件命令，在弹出的部件对话框中 选择设计器，然后勾选Addin Class点确定。

21、立一工程I - Iicrosoft Visual Basic 设计】文件任）编辑8 视图9 工程望）格式切 调试运行HJ森谭&）的表Q）工具（I）外捱翌序0）雷0, 斯明）（2）、修改设计器类的名称，选择设计器类的加载行为。在工程资源 管理器窗口双击设计器类，在弹出的对话框中选择“通用”选项卡，在通用选项卡中填写相应信息。A 外接程序显示名称，这里填写的名称就是加载com 加载项时显示的名称，这里填写com 加载。B 外接程序描述，可不写C应用程序，这里选择com加载项需要应用的程序，一定要填写， 不然当我们选择添加com 加载项时看不到相应的com 加载项，这里选择 excel。D应用程序版

22、本，选择你机子上安装的excel版本。这里是excel12.0E 初始化加载行为，这里有 4 个选项， 用来选择com 加载项的加载行为：Startup：每次当excel应用程序启动时，自动加载 com加载项。这里选择 startupLoad on demand： excel启动时不会加载com加载项，但是在每次启动时会自动选中，如果通用选项卡选择了这个，那么在 excel 加载 com加载项时需要将勾选去掉退出后在进入com 加载项界面选择该加载项才能加载。Load at next startup only: 当下次 excel启动时自动力口载 com力口载项， 但是之后excel在启动时，

23、该com加载项的加载行为就变了 Load on demandNone： excel启动时不会加载com加载项，也不会勾选，需要手动勾 选加载。注册表会记录下com 加载项的加载行为，以本文为例，那么可在HK工程I.AddInDesignerl LoadBehavior找至U对应的值。Startup 对应的值为 30 = Disconnect未力口载。1 = Connected已加载。2 = Bootload 一在应用程序启动时加载。8 = DemandLoad 只在用户请求时加载。16 = ConnectFirstTime一 只加载一次（在下次启动时）。EJ国国L电4士事率河二呈-粤工程1 :

24、程1）lAddinuesigner 1Ad-niwe signer 1Classi (Classi J|_i_IninV -a r eBrt Jul 、工程 1 - AddlnDesigner 1 (AddlnDesigner) . |0 X(3)通用选项卡的信息填写完毕后暂时不要关闭，先在属性窗口将设计器类的public属性改为true,这步完成后就可以关闭“通用选项卡”窗口了。由L皿 L 至 a十I至声w言|姓J三叁| |3 Ad.dLExxD e = i1 (AdLdlxdDes i Kn.tr 1 Jci=i (ci =1)JLcldl 皿L 里执字母中按分类序（ 4） 、 在设计器类

25、窗口中书写代码。在工程资源管理器窗口中右键点击设计器类，选择查看代码，在设计器类窗口中写下如下代码：Implements IDTExtensibility2Private Sub IDTExtensibility2_OnAddInsUpdate(custom() As Variant)End SubPrivate Sub IDTExtensibility2_OnBeginShutdown(custom() As Variant)End SubPrivate Sub IDTExtensibility2_OnConnection(ByVal Application As Object, ByVal

26、 ConnectMode As AddInDesignerObjects.ext_ConnectMode,ByValAddInInst As Object, custom() As Variant)End SubPrivate Sub IDTExtensibility2_OnDisconnection(ByValRemoveMode AsAddInDesignerObjects.ext_DisconnectMode, custom() As Variant)End SubPrivate Sub IDTExtensibility2_OnStartupComplete(custom() As Va

27、riant)End Sub第一句Implements IDTExtensibility2表示声明一个接口，其余 5个过程外壳表示实现接口的5 个方法， 说白点就是5 个事件过程外壳，监视com加载项的加载行为。OnAddInsUpdate表示当在excel中加载或卸载 com 加载项时触发该事件，当卸载代码所在的com 加载项时不会触发OnAddInsUpdate,卸载其他 com加载项可触发该事件，OnConnection 和 OnDisconnection 分别表示excel 与 com 加载项建立和失去连接时触发相应事件，只与代码所在com 加载项有关，其他com 加载项与excel 建

28、立和失去连接时并不会触发相应事件。OnStartupComplete和 OnBeginShutdown表示当 excel加载和卸载自身时触发相应事件，这个必须在com 加载项与excel 建立连接 (加载 com加载项)的情况下才能发生。详细解释参考;zh-cn;302896假如我们想要在开启excel 程序调用一个登录窗口，可以将事先设计好的窗体导入到 VB6.0中，然后在OnConnection事件调用窗体。代 码如下：Private Sub IDTExtensibility2_OnConnection(ByVal Application As Object, ByVal ConnectM

29、ode As AddInDesignerObjects.ext_ConnectMode,ByVal AddInInst As Object, custom() As Variant) UserForm1.Show 0End Sub(5)、生成dll文件或exe文件。点菜vb6.0文件菜单生成dll文件命 令(如果前面新建工程时选择的是 activeX.EXE这里就是生成exe命 令) ，然后选择保存文件的路径和文件名点击保存。( 6) 、测试 com 加载项。打开excel 程序，可以看到已经调用了登录窗口，如果不想调用登录窗口，可将com 加载项卸载掉。文件3）编辑视图 工程E）格式Q）调试

30、 运行 查询也）图表Q）工新建工程总打开工程w.Ctrl+SCtrl+0T题囱马哲3添加工程也）一 一 移除工程H保存工程W工程另存为重）保存Rd!疝fD。）AddlnDesigner! Dsr 另存为 &）._保存选择QCtrl+SI (Code)Ctrl+Py2 jOnkidlrLslfpAate (cusi生成 jia工 3 dll QS)y2_0rtB sginShut 4口 冒 n (eu;。：.-.人加1111：武1_色七01_%桌面调用窗保上”&工8.中即2 C： ,、桌面制作功能区、工程1. wbp3 C ： . . Uquu. , Adniniwtntor，豆面XgjjJ.

31、vbp4 C :, kAdmin. 桌面VBA代码封装完整示例，MyDLL vbpf OnC&nnect im (ByVaiy2_0nJi Eceiuiecti-cn(By退出（X）Alt+QFr i vatt Sub IDTEktensibill tyETiStsrtupCofftpltte (Com加载项的制作到这里就讲完了，其实一个 com加载项简单的理解就是一个经过封装的设计器类，如果我们要在程序中能加载com加载项就必须对设计器类的通用选项卡页面和public 属性进行设置，至于我们需要com 加载项实现什么功能就看我们写的代码了，可以声明一个 IDTExtensibility2接口

32、，然后在 IDTExtensibility2接口的 5 个事件程序外壳中书写代码实现相应功能，当然还可以声明其他接口，比如我们下一节要讲的IRibbonExtensibility接口，这个接口与制作自定义功能区有关。五、利用com加载项自定义功能区利用 com 加载项自定义功能区与前面制作com 加载项的过程基本相似，只是在制作自定义功能区时要用到IRibbonExtensibility接口而不是 IDTExtensibility2 接口，在声明IRibbonExtensibility 接口后，用IRibbonExtensibility 接口的 GetCustomUI 方法获取自定义功能区的代

33、码，与 Custom UI Editor 书写的代码一样，最后在书写自定义功能区代码中所涉及到的回调函数。下面介绍一个自定义功能区的例子，我们将利用com 加载项产生一个名叫“批量重命名”的选项卡，这个选项卡包含一个组，2 个按钮，一个复选框（checkbox） ，一个列表框（ dropdown） ，复选框控制其中一个按钮是否可用，列表框动态显示工作簿所包含的工作表名称，具体过程如下：（ 1）新建工程，添加设计器类（略）（ 2）修改设计器类的加载行为（略）（ 3）在设计器类的代码窗口中书写自定义功能区代码。具体代码如下，这里要说明的是回调函数是书写在设计器类代码框中的，与在VBA中书写回调函数

34、不同的是，VBA中回调函数一般是写成子过程的 形式，但是在VB6.0中部分回调函数要写成函数过程，如getpressedgetlabel、 getenabled、 getitemcount、 getitemID、 getitemLabel 等，这 里举个例子，如在VBA中getlabel的回调过程为sub getlabel(control As IRibbonControl, byref returnedVal)， 在 VB6.0 中 要 写 成 function getlabel(control As IRibbonControl) as string就是要把 returnedVal 参数

35、去掉，把函数返回值传递给相应控件，其他get 类型的回调函数与此类似。Public WithEvents app As ApplicationPublic ao As IRibbonUI一定要定义成IRibbonUI， 定义成 object或者不定义都会导致不能刷新功能区Implements IRibbonExtensibilityPublic bool As BooleanPrivate Function IRibbonExtensibility_GetCustomUI(ByValRibbonID AsString) As StringIRibbonExtensibility_GetCust

36、omUI = _ & _ & _ & _ & _size=large & _ & _ & _ & _ & _Set app = GetObject(, excel.application) 这样获取对象方便，准确End FunctionPrivate Sub app_SheetActivate(ByVal Sh As Object)MsgBox asasao.InvalidateEnd SubPublic Sub 主过程(ByVal control As IRibbonControl)响应按钮 1 的点击 动作MsgBox AEnd SubPublic Sub 帮助 (ByVal control As IRibbonControl) 响应按钮2的点击动作MsgBox bEnd Sub 动 态 改 变 按 钮 2 的 标 签 ， 回 调