《EXCEL加载宏制作攻略》由会员分享，可在线阅读，更多相关《EXCEL加载宏制作攻略（8页珍藏版）》请在金锄头文库上搜索。
1、EXCEL 加载宏制作攻略(2009-07-05 22:42:21)标签：it 分类：计算机学习摘录本文所述加载宏是指使用 EXCEL VBA 制作的加载宏，这类文件的后缀默认为 xla，因此又称 XLA 加载宏。内容划分为五部分，第一部分为概述，简单介绍加载宏的一般知识；第二部分至第四部分介绍制作加载宏函数、过程和事件主要技巧；第五部分为制作加载宏的其它方面。一、概述1制作加载宏的一般步骤（1）创建一个新的工作薄，在其中添加代码。（2）在“文件”菜单上单击“属性”。在“文件名属性”对话框中，单击“摘要信息”选项卡，然后在“标题”框中为加载宏指定一个您希望在“加载宏”对话框中出现的名称，在“备
2、注”框中输入您希望当用户从“加载宏”对话框选择该加载宏时在“加载宏”对话框下方出现的说明信息。（3）在“文件”菜单上单击“另存为”。在“另存为”对话框中，从“保存类型”框中选择“Microsoft Office Excel 加载宏（*.xla）”，然后再选择保存路径，并可在“文件名”框中修改文件名。2加载宏的加载和卸载通过在“工具”菜单上单击“加载宏”，可以加载制作好的加载宏文件，如果它不在列表中，可以在“加载宏”对话框中单击“浏览”找到它，当某个加载宏名称前的复选框被选中时，说明它已经加载，取消选中复选框即为卸载该加载宏。3加载宏的作用加载宏可以扩展 EXCEL 的功能，将代码写在加载宏中和
3、写在普通的工作簿中相比，有如下优点：（1）加载宏对所有打开的 EXCEL 文件都生效。普通工作簿的代码一般只在特定的工作簿中才生效。（2）加载宏不受宏安全级的限制，也不会有宏运行的提示。即使将安全级设为“非常高”，加载宏就象 EXCEL 本身固有的功能一样工作。同样，它也不会被按住的 Shift 屏蔽。特别提请不习惯使用加载宏而专门 VBA 代码书写的朋友们注意的是，加载宏的所有好处和优点，都是属于 EXCEL 的常规操作者的，虽然加载宏中的代码可以被其他工作簿（中的代码）调用，但这样做是繁琐而低效的，千万不要把它当成了 C 中的“头”文件或链接库。也许不会有很多人这样认为，但笔者初学 VBA
4、 的时候，真的这样天真过。（3）加载宏在运行时其工作簿窗口是隐藏的，因此普通用户并不会感觉到它的存在。二、处理加载宏函数加载宏函数似乎是制作加载宏中最简单的，通常它只需要在模块中写一段 Function 代码，它就会和 EXCEL 自身的函数一样使用，并且出现在“插入函数”对话框的列表中。关于函数的制作，再没有其它的吗？1为函数添加说明信息至少你可以通过对象浏览器设置“属性”为函数添加一段说明信息（对需要换行的说明可以在编辑时使用组合键CTRL+ENTER），这些信息当用户使用“插入函数”对话框时就会看到。需要说明的是，在对象浏览器中为函数添加说明信息必须在“另存为”加载宏之前，一旦文件名的后
5、缀变为了 XLA，函数的属性设置将不被接受。关于为函数定制说明信息，请参见：图示自定义函数描述信息及有关介绍。现在，请你新建一个工作簿，插入模块，粘贴下面的代码：Function dx(n) by gly1126 金额小写转换为大写dx = Replace(Application.Text(Round(n+0.00000001, 2), DBnum2), ., 元)dx = IIf(Left(Right(dx, 3), 1) = 元, Left(dx, Len(dx) - 1) & 角 & Right(dx, 1) & 分, IIf(Left(Right(dx, 2), 1) = 元, dx
6、& 角整, IIf(dx = 零, , dx & 元整)dx = Replace(Replace(Replace(Replace(dx, 零元零角, ), 零元, ), 零角, 零), -, 负)End Function如果觉得必要，你可以设置文件属性（见一、1（2）并为该函数添加说明，先把它做为普通的工作簿保存（我们后面还会继续用它）再另存为 XLA，然后感受一下它的效果吧。对已经成为 XLA 文件的函数说明的添加及修改并不需要总是手工先还原为 XLS 文件，我们稍后就会提到 MacroOptions 方法。在此之前，先来说一下与加载宏相关的 Workbook 事件：（1）AddinInst
7、all 和 AddinUninstall。这两个事件分别发生在加载宏加载和卸载时的，因此使用这两个事件的工作簿一定是 XLA 工作簿，你需要记住的是，它们在加载宏作用期内各自只会发生一次，加载宏加载后每次 EXCEL 的打开和关闭都不会触发它们。（2）Open 和 BeforeClose。和上两个事件不同，这两个事件在加载宏作用期内会被多次触发，除了分别发生在AddinInstall 后和 AddinUninstall 前之外，每次打开和关闭 EXCEL 时都会触发它们。清楚了上述特性，你就应该知道如何合理安排加载宏的初始化代码。2为函数分类默认情况下，当用户使用“插入函数”时，加载宏函数会被
8、分类放在“用户定义”类别中。哦，没有个性！有的朋友甚至还会有被歧视的感觉呢，怎样才能和 EXCEL 自身的函数一样被放在“财务”类呢？我们会想到MacroOptions 方法：Application.MacroOptions Macro:=dx, Category:=1不熟悉这个方法不要紧，看完后面的例子你再去查看一下它的帮助，很快就会掌握的。现在的问题，上面的语句放在哪儿呢？首先把它放在 AddinInstall 事件中看看，你会发现，在第一次加载时，目的确实达到了，但当你关闭 EXCEL 重新打开时，却发现它又回到了“用户定义”中，看来需要放在 Open 事件中：Private Sub W
9、orkbook_Open()ThisWorkbook.IsAddin = FalseApplication.MacroOptions Macro:=dx, Category:=1ThisWorkbook.IsAddin = TrueThisWorkbook.Saved = TrueEnd Sub你会发现，除了我们需要的，又增加了 3 条语句，这是因为加载宏文件与普通 EXCEL 文件除了后缀形式上的区别外，一个重要的内在区别就是 IsAddin 属性，该属性为 True 说明是加载宏工作簿，MacroOptions 方法对宏的设置不能在隐藏的工作簿中进行，需要先将还原为普通工作簿，加入宏设置后
10、再改回。这两次改变不会触发 AddinInstall 和 AddinUninstall 事件，但 EXCEL 会记住普通 EXCEL 文件的修改，因此在完成设置后，还需要加一句 ThisWorkbook.Saved = True 以使 EXCEL 忘记这种改变。下面列出了 EXCEL 内部分类对应的 Category 参数的整数。1：财务 2：日期与时间 3：数学与三角函数4：统计 5：查找与引用 6：数据库7：文本 8：逻辑 9：信息如果你不想记住这些整数的含义，你可以直接写成：Application.MacroOptions Macro:=dx, Category:= 财务那么是否可以为加
11、载宏函数增加一个新类别呢？很简单！只需要将上面的 Category 参数改变一下就可以了：Application.MacroOptions Macro:=dx, Category:= 财务扩展函数上句会在“插入函数”对话框中增加一个新类别“财务扩展函数”，并把 dx 函数放入其中。MacroOptions 方法还有其它一些参数，可以帮助我们，包括为函数添加说明，下面语句在分类的同时会为函数增加说明。Application.MacroOptions Macro:=dx, Description:=金额小写转换为大写 & vbCr & 参数 N：要转换的金额。, Category:=财务扩展函数让
12、我们再次回到 Open 事件中，看看这 3 句代码： ThisWorkbook.IsAddin = FalseThisWorkbook.IsAddin = TrueThisWorkbook.Saved = True在加载宏文件中使用类似在无耐情况下才采用的变通代码，达到的目的仅仅是一般人并不注意的函数分类，我想我已经把你引入歧途，真的很抱歉。我该如何纠正呢？我无意保留一个没有多大实用价值的技巧，我更希望由你说出来（我会等你一个礼拜）呵呵，也不能全怪我，因为几乎所有的公开的资料都是这样介绍的，事实上，实现函数的说明和分类根本用不着明示的代码，这意味着在 open 事件中使用 MacroOptio
13、ns 根本就是多余的。因为 EXCEL 会记录并保存 MacroOptions 的操作，你只需在设计阶段，在立即窗口，执行需要的 MacroOptions 操作，或者写一段 MacroOptions 操作代码，然后再删除它就可以了。要记住的是，执行后一定别忘了在 VBE 中点击保存。结果竟然如此简单！请参考我在后面给你提供的附件实例。3函数规范有些朋友认为：Function 和 Sub 的区别仅仅在于是否有返回值，在 Sub 中使用的代码同样可以在 Function 中使用。这种认识最多对了一半。是的，对于在代码中调用的 Function 和 Sub，除了“返回值”，它们几乎没有区别，但是，全
14、局宏的函数是准备给用户在工作表中使用，象我们前面的做的函数，用户在使用时，通常会在工作表的 B2 单元格中输入“=dx(A1)”，如果一个函数是为工作表准备的，代码就要受到限制。先来看一个错误的函数：Function TTT(rg As Range) As Stringrg.Value = rg.Value + 1TTT = rg.ValueEnd Function上面的函数试图将参数区域的值先加 1，然后返回其值。如果你在代码中使用N=TTT(a1)不会有任何问题，现在，你在 A2 单元格输入公式“=ttt(A1)”看看。限于篇幅，本文不再探讨理论问题，而是直接给出这些应该注意的结论：（1）
15、严格遵循“黑匣子”理论，不要试图去改变外部对象的属性，包括参数对象，即使它们是传址方式的。对外部对象值的改变，会导致函数的结果错误，如你在上面看到的“#VALUE！”，其它属性的改变，通常会被忽略。（2）不要使用 Volatile 方法，除非你要构建的函数确实是象 RAND 类型的易失性函数。在函数中加入：Application.Volatile会告诉系统该函数是易失性函数，则当工作表的任何区域发生改变时，该函数都会被重算。这将严重影响系统的效率。要做到这一点，应当将所有影响函数值变动的区域都作为参数，即使某些区域是固定的也要作为参数传递。（3）函数中可以使用可选参数，但可选参数应避免为 Ra
16、nge 类型。因为使用 Range 类型的可选参数，将不可避免地使用Application.Volatile。三、处理加载宏过程加载宏的另一个重用功能就是提供扩展的操作，这通常是写在模块中的 Sub 过程。设计加载宏过程应注意两个方面，一是代码实体，二是提供适当的接口方式以便用户可以使用这些操作。在代码实体的设计上，加载宏代码与其它 VBA 代码看上去也许没有太多的差别，但它要求设计者更为密切地注意加载宏的运行环境，对象成员的使用也更应规范和严谨。比如，喜欢混用 ThisWorkbook 和 ActiveWorkbook 的朋友要好好看看帮助文档中二者的差别了。现在请在模块中粘贴下面的代码：Sub HVCenter() 这段代码的含义很简单，让选定区域文字水平垂直居中With Selection .Hor
