[终稿]EXCEL加载宏制作攻略

[终稿]EXCEL加载宏制作攻略 EXCEL加载宏制作攻略 Tag: EXCEL 加载宏 制作 本文所述加载宏是指使用EXCEL VBA制作的加载宏，这类文件的后缀默认为xla，因此又称XLA加载宏。内容划分为五部分，第一部分为概述，简单介绍加载宏的一般知识;第二部分至第四部分介绍制作加载宏函数、过程和事件主要技巧;第五部分为制作加载宏的其它方面。 一、概述 1(制作加载宏的一般步骤 (1)创建一个新的工作薄，在其中添加代码。 (2)在“文件”菜单上单击“属性”。在“文件名 属性”对话框中，单击“摘要信息”选项卡，然后在“标题”框中为加载宏指定一个您希望在“加载宏”对话框中出现的名称，在“备注”框中输入您希望当用户从“加载宏”对话框选择该加载宏时在“加载宏”对话框下方出现的说明信息。 (3)在“文件”菜单上单击“另存为”。在“另存为”对话框中，从“保存类型”框中选择“Microsoft Office Excel 加载宏(*.xla)”，然后再选择保存路径，并可在“文件名”框中修改文件名。 2(加载宏的加载和卸载 通过在“工具”菜单上单击“加载宏”，可以加载制作好的加载宏文件，如果它不在列表中，可以在“加载宏”对话框中单击“浏览”找到它，当某个加载宏名称前的复选框被选中时，说明它已经加载，取消选中复选框即为卸载该加载宏。 3(加载宏的作用 加载宏可以扩展EXCEL的功能，将代码写在加载宏中和写在普通的工作簿中相比，有如下优点: (1)加载宏对所有打开的EXCEL文件都生效。普通工作簿的代码一般只在特定的工作簿中才生效。 (2)加载宏不受宏安全级的限制，也不会有宏运行的提示。即使将安全级设为“非常高”，加载 宏就象EXCEL本身固有的功能一样工作。同样，它也不会被按住的Shift屏蔽。 特别提请不习惯使用加载宏而专门VBA代码书写的朋友们注意的是，加载宏的所有好处和优点，都是属于EXCEL的常规操作者的，虽然加载宏中的代码可以被其他工作簿(中的代码)调用，但这样做是繁琐而低效的，千万不要把它当成了C中的“头”文件或链接库。也许不会有很多人这样认为，但笔者初学VBA的时候，真的这样天真过。 (3)加载宏在运行时其工作簿窗口是隐藏的，因此普通用户并不会感觉到它的存在。 相关贴子[求助]如何根据EXCEL中的数据在WORD文档中填充... EXCEL单元格数据写入acess出错，请各位高手帮忙～ 怎样利用EXCEL编一个网络汇总程序 [求助]excel有类似于vb的file控件吗, [求助]excel不能准确排名次 [求助]把excel文件中的sheet的指定列保存成单... [求助]提示运行时错误'13' 类型不匹配(内有代码) 学vba的好素材—投票统计系统v1.3 用excel的vba 设置Word页面的问题 [求助]单元格无法右击 更多相关 无论你走得多么远,你的心总和我连在一起; 无论黄昏时树的影子有多长,它总是和树根连在一起。 穿别人的鞋，走自己的路，让他找去吧～ 帖子评价 (0.5) 1(100%) 0(0%) 0(0%) [规则] [明细] 2007-7-26 14:15:40 ? 献给初学者:《循序渐进学Excel》视频教程 ? qee用 本人受好评明细查询 等级:贵宾 威望:13 文章:3712 精华:3 EH币:7733 积分:78 注册日期:2005年4月13日 行业:制造业 职业:人力资源 第 2 楼 ※邮件 二、处理加载宏函数 加载宏函数似乎是制作加载宏中最简单的，通常它只需要在模块中写一段Function代码， 它就会和EXCEL自身的函数一样使用，并且出现在“插入函数”对话框的列表中。关于函数的制 作，再没有其它的吗, 1(为函数添加说明信息 至少你可以通过对象浏览器设置“属性”为函数添加一段说明信息(对需要换行的说明可以在编辑时使用组合键CTRL+ENTER)，这些信息当用户使用“插入函数”对话框时就会看到。需要说明的是，在对象浏览器中为函数添加说明信息必须在“另存为”加载宏之前，一旦文件名的后缀变为了XLA，函数的属性设置将不被接受。 关于为函数定制说明信息，请参见:图示自定义函数描述信息及有关介绍。 Application.macro options方法进行设置。 现在，请你新建一个工作簿，插入模块，粘贴下面的代码: 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 & "角整", IIf(dx = "零", "", dx & "元整"))) dx = Replace(Replace(Replace(Replace(dx, "零元零角", ""), "零元", ""), "零角", "零"), "-", "负") End Function 如果觉得必要，你可以设置文件属性(见一、1((2))并为该函数添加说明，先把它做为普通的工作簿保存(我们后面还会继续用它)再另存为XLA，然后感受一下它的效果吧。 对已经成为XLA文件的函数说明的添加及修改并不需要总是手工先还原为XLS文件，我们稍后就会提到MacroOptions方法。在此之前，先来说一下与加载宏相关的Workbook事件: (1)AddinInstall和AddinUninstall。这两个事件分别发生在加载宏加载和卸载时的，因此使用这两个事件的工作簿一定是XLA工作簿，你需要记住的是，它们在加载宏作用期内各自只会发生一次，加载宏加载后每次EXCEL的打开和关闭都不会触发它们。 (2)Open和BeforeClose。和上两个事件不同，这两个事件在加载宏作用期内会被多次触发，除了分别发生在AddinInstall后和AddinUninstall前之外，每次打开和关闭EXCEL时都会触发它 们。 清楚了上述特性，你就应该知道如何合理安排加载宏的初始化代码。 2(为函数分类 默认情况下，当用户使用“插入函数”时，加载宏函数会被分类放在“用户定义”类别中。 哦，没有个性～有的朋友甚至还会有被歧视的感觉呢，怎样才能和EXCEL自身的函数一样被放在“财务”类呢,我们会想到MacroOptions方法: Application.MacroOptions Macro:="dx", Category:=1 不熟悉这个方法不要紧，看完后面的例子你再去查看一下它的帮助，很快就会掌握的。 现在的问题，上面的语句放在哪儿呢,首先把它放在AddinInstall事件中看看，你会发现，在第一次加载时，目的确实达到了，但当你关闭EXCEL重新打开时，却发现它又回到了“用户定义”中，看来需要放在Open事件中: Private Sub Workbook_Open() ThisWorkbook.IsAddin = False Application.MacroOptions Macro:="dx", Category:=1 ThisWorkbook.IsAddin = True ThisWorkbook.Saved = True End Sub 你会发现，除了我们需要的，又增加了3条语句，这是因为加载宏文件与普通EXCEL文件除了后缀形式上的区别外，一个重要的内在区别就是IsAddin属性，该属性为True 说明是加载宏工作簿，MacroOptions方法对宏的设置不能在隐藏的工作簿中进行，需要先将还原为普通工作簿，加入宏设置后再改回。这两次改变不会触发AddinInstall和AddinUninstall事件，但EXCEL会记住普通EXCEL文件的修改，因此在完成设置后，还需要加一句ThisWorkbook.Saved = True以使EXCEL忘记这种改变。 下面列出了EXCEL内部分类对应的Category参数的整数。 1:财务 2:日期与时间 3:数学与三角函数 4:统计 5:查找与引用 6:数据库 7:文本 8:逻辑 9:信息 如果你不想记住这些整数的含义，你可以直接写成: Application.MacroOptions Macro:="dx", Category:= "财务" 那么是否可以为加载宏函数增加一个新类别呢,很简单～只需要将上面的Category参数改变一下就可以了: Application.MacroOptions Macro:="dx", Category:= "财务扩展函数" 上句会在“插入函数”对话框中增加一个新类别“财务扩展函数”，并把dx函数放入其中。 MacroOptions方法还有其它一些参数，可以帮助我们，包括为函数添加说明，下面语句在分类的同时会为函数增加说明。 Application.MacroOptions Macro:="dx", Description:="金额小写转换为大写" & vbCr & "参数N:要转换的金额。", Category:="财务扩展函数" 让我们再次回到Open事件中，看看这3句代码: ThisWorkbook.IsAddin = False ThisWorkbook.IsAddin = True ThisWorkbook.Saved = True 在加载宏文件中使用类似在无耐情况下才采用的变通代码，达到的目的仅仅是一般人并不注意的函数分类，我想我已经把你引入歧途，真的很抱歉。我该如何纠正呢, 我无意保留一个没有多大实用价值的技巧，我更希望由你说出来…… (我会等你一个礼拜) 呵呵，也不能全怪我，因为几乎所有的公开的资料都是这样介绍的，事实上，实现函数的说明和分类根本用不着明示的代码，这意味着在open事件中使用MacroOptions根本就是多余的。因为EXCEL会记录并保存MacroOptions的操作，你只需在设计阶段，在立即窗口，执行需要的MacroOptions操作，或者写一段MacroOptions操作代码，然后再删除它就可以了。要记住的是， 执行后一定别忘了在VBE中点击保存。结果竟然如此简单～请参考我在后面给你提供的附件实例。 3(函数规范 有些朋友认为:Function和Sub的区别仅仅在于是否有返回值，在Sub中使用的代码同样可以在Function中使用。这种认识最多对了一半。是的，对于在代码中调用的Function和Sub，除了“返回值”，它们几乎没有区别，但是，全局宏的函数是准备给用户在工作表中使用，象我们前面的做的函数，用户在使用时，通常会在工作表的B2单元格中输入“=dx(A1)”，如果一个函数是为工作表准备的，代码就要受到限制。先来看一个错误的函数: Function TTT(rg As Range) As String rg.Value = rg.Value + 1 TTT = rg.Value End Function 上面的函数试图将参数区域的值先加1，然后返回其值。如果你在代码中使用 N=TTT([a1]) 不会有任何问题，现在，你在A2单元格输入公式“=ttt(A1)”看看。 限于篇幅，本文不再探讨理论问题，而是直接给出这些应该注意的结论: (1)严格遵循“黑匣子”理论，不要试图去改变外部对象的属性，包括参数对象，即使它们是传址方式的。对外部对象值的改变，会导致函数的结果错误，如你在上面看到的“#VALUE～”，其它属性的改变，通常会被忽略。 (2)不要使用Volatile 方法，除非你要构建的函数确实是象RAND类型的易失性函数。在函数中加入: Application.Volatile 会告诉系统该函数是易失性函数，则当工作表的任何区域发生改变时，该函数都会被重算。这将严重影响系统的效率。要做到这一点，应当将所有影响函数值变动的区域都作为参数，即使某些区域是固定的也要作为参数传递。 (3)函数中可以使用可选参数，但可选参数应避免为Range类型。因为使用Range类型的可 选参数，将不可避免地使用Application.Volatile。 [此贴子已经被作者于2007-8-10 14:25:18编辑过] 无论你走得多么远,你的心总和我连在一起; 无论黄昏时树的影子有多长,它总是和树根连在一起。 穿别人的鞋，走自己的路，让他找去吧～ 帖子评价 (0.0) 0(0%) 0(0%) 0(0%) [规则] [明细] 2007-7-26 14:16:41 ? 关注优秀会员评选与奖励计划 ? qee用 本人受好评明细查询 等级:贵宾 威望:13 文章:3712 精华:3 EH币:7733 积分:78 注册日期:2005年4月13日 行业:制造业 职业:人力资源 第 3 楼 ※邮件 三、处理加载宏过程 加载宏的另一个重用功能就是提供扩展的操作，这通常是写在模块中的Sub过程。设计加载宏过程应注意两个方面，一是代码实体，二是提供适当的接口方式以便用户可以使用这些操作。 在代码实体的设计上，加载宏代码与其它VBA代码看上去也许没有太多的差别，但它要求设计者更为密切地注意加载宏的运行环境，对象成员的使用也更应规范和严谨。比如，喜欢混用ThisWorkbook和ActiveWorkbook的朋友要好好看看帮助文档中二者的差别了。 现在请在模块中粘贴下面的代码: Sub HVCenter() ' 这段代码的含义很简单，让选定区域文字水平垂直居中 With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter End With End Sub 在接口处理上，一般有3种处理方式。 1(快捷键 为过程设置快捷键可以在另存为加载宏文件前在“工具”菜单上单击“宏”-“宏”，在“宏”对话框中，单击“选项”按钮完成。也可使用前面介绍过的MacroOptions方法设置。 Application.MacroOptions Macro:="", HasShortcutKey:=True, ShortcutKey:="A" 2(菜单 通过在系统菜单上增加菜单项可以将操作提供给使用者，加载宏是对EXCEL基本功能的扩充，一般不提倡使用自定义菜单来代替系统菜单。 3(工具栏 可以使用EXCEL现有的工具栏上增加按钮也可以通过新建工具栏来完成。下面代码将前面的过程关联到新建的菜单项和工具栏上。 Private Sub Workbook_AddinInstall() On Error Resume Next '新建菜单 With Application.CommandBars(1).Controls.Add(Type:=msoControlPopup) .Caption = "测试(&T)" With .Controls.Add(Type:=msoControlButton) .Caption = "居中" .OnAction = "HVCenter" End With End With '新建工具栏 With Application.CommandBars.Add(Name:="myCmdbar") .Position = msoBarTop With .Controls.Add .FaceId = 352 .Caption = "居中" .OnAction = "HVCenter" End With .Visible = True End With End Sub 由于工具栏和菜单生成后不会随EXCEL的关闭而消失，因此创建工具栏和菜单的代码一般应写在AddinInstall事件中，并且在加载宏卸载时随之卸载。下面代码卸载前面创建的工具栏和菜单。 Private Sub Workbook_AddinUninstall() On Error Resume Next Dim ctl As CommandBarControl '卸载工具栏和菜单 Application.CommandBars("myCmdbar").Delete For Each ctl In Application.CommandBars(1).Controls If ctl.Caption = "测试(&T)" Then ctl.Delete Next ctl End Sub 无论你走得多么远,你的心总和我连在一起; 无论黄昏时树的影子有多长,它总是和树根连在一起。 穿别人的鞋，走自己的路，让他找去吧～ 帖子评价 (0.0) 0(0%) 0(0%) 0(0%) [规则] [明细] 2007-7-26 14:17:44 ? ExcelHome技术社区2008第3期优秀会员名单揭晓～ ? qee用 本人受好评明细查询 等级:贵宾 威望:13 文章:3712 精华:3 EH币:7733 积分:78 注册日期:2005年4月13日 行业:制造业 职业:人力资源 第 4 楼 ※邮件 四、处理加载宏事件 原则上讲，加载宏事件应对所有打开工作簿发生的事件进行处理。加载宏事件的设计相对函数和过程的设计而言通常要复杂得多，它要求设计者具有一定的类知识和良好的代码组织能力。具体设计主要应解决两方面问题，一是事件代码本身，二是将预作的“事件代码”和对象关联。我们仍通过实例，继续前面的设计来介绍。 任务:让加载宏实现当用户在活动工作簿的活动工作表选择某一区域时，状态栏显示该区域的范围。 1(设计事件代码 (1)分析要用的对象和事件 不难知道，本例需要Workbook对象的SheetSelectionChange的事件。首先使用WithEvents关键字定义一个Workbook对象。 Dim WithEvents wkb As Workbook 注意，WithEvents关键字不能在标准模块中使用，因此，代码一般写在Thisworkbook中。 (2)书写事件代码 Private Sub wkb_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) Application.StatusBar = "你选择的区域:" & Replace(Target.Address, "$", "") End Sub 2(关联对象 (1)分析需要关联的所有情况 写好了上面的代码，如何将它与活动工作簿关联呢,首先应该分析可能进行关联的时机。以本例，大体我们将需要(改变)关联，即活动工作簿发生变化的情况列示如下: a.当新建工作簿时，关联对象为新建的工作簿 b.当新打开工作簿时，关联对象为新打开工作簿 c.当在两个工作簿之间切换时，关联对象为新切换到的工作簿 (2)完成关联代码 上面的分析知道，需要用到Application级的事件。定义Application对象，写入相应的事件，然后在Open事件中将它关联到当前的Application即可。汇合前面的代码如下: Dim WithEvents app As Application Dim WithEvents wkb As Workbook Private Sub app_NewWorkbook(ByVal Wb As Workbook) Set wkb = Wb End Sub Private Sub app_WorkbookOpen(ByVal Wb As Workbook) Set wkb = Wb End Sub Private Sub app_WorkbookActivate(ByVal Wb As Workbook) Set wkb = Wb End Sub Private Sub wkb_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) Application.StatusBar = "你选择的区域:" & Replace(Target.Address, "$", "") End Sub Private Sub Workbook_Open() '关联到Application Set app = Application End Sub 还有一个问题，上面处理的都是加载宏加载后的关联，当加载宏加载时，调用加载宏的工作簿呢,你是否认为可以在上面的Open事件中加入: Set wkb = ActiveWorkbook 就可以了呢,答案是否定的。正确的做法是使用属性过程和Auto_Open，首先在ThisWorkbook中加入下面的属性过程: Property Let ActiveWkb(ByVal wk As Workbook) Set wkb = wk End Property 然后再在模块代码中加入Auto_Open过程: Private Sub Auto_Open() ThisWorkbook.ActiveWkb = ActiveWorkbook End Sub 我把这个放在最后，目的是提请你注意Open和Auto_Open的区别。 (图) 此主题相关图片如下: 点击浏览该文件 无论你走得多么远,你的心总和我连在一起; 无论黄昏时树的影子有多长,它总是和树根连在一起。 穿别人的鞋，走自己的路，让他找去吧～ 帖子评价 (0.0) 0(0%) 0(0%) 0(0%) [规则] [明细] 2007-7-26 14:19:27 ? Excel服务器学习和下载园地 ? qee用 本人受好评明细查询 等级:贵宾 威望:13 文章:3712 精华:3 EH币:7733 积分:78 注册日期:2005年4月13日 行业:制造业 职业:人力资源 第 5 楼 ※邮件 五、加载宏制作的其它技巧 1(静态和动态数据 我们一直没有提到加载宏的工作表，它们可用来做什么呢,利用加载宏的工作表隐藏而具有良好的保护性，它们可以用来存储“系统”使用的大量数据(包括格式)，从而实现所谓代码和数据的分离，使代码更清晰简洁。这些数据是静态的，在运行过程中允许加载宏中使用代码对它们进行修改(包括增删工作表)，从而实现动态数据的要求。然而，这些修改在EXCEL关闭的时候，不会象普通文件那样给出类似“加载宏文件已经修改，是否保存,”之类的提示，当你下次打开EXCEL时，加载宏工作表仍然会回到它最初的样子。呵呵，是不是有点象“模板”啊,只有一种情况例外，就是在加载宏中使用了明示的保存代码如Thisworkbook.Save。 2(加载宏的调试 当加载宏文件需要修改的时候，有些朋友习惯于先关闭所有的EXCEL文件，然后单独打开加载宏文件进行修改。其实是不必要的，加载宏可以在加载的状态下调试并且进行修改，必要时可以先将它的IsAddin属性设置为False(调试结束记得改回去)，完成后在VBE中点击“保存”(注意不能在主界面下保存)。 3(使用窗体 加载宏可以使用窗体为用户提供友好的界面。其设计和普通工作簿没有什么区别，不再赘述。 [此贴子已经被作者于2007-8-10 14:24:29编辑过] 无论你走得多么远,你的心总和我连在一起; 无论黄昏时树的影子有多长,它总是和树根连在一起。 穿别人的鞋，走自己的路，让他找去吧～ 帖子评价 (0.0) 0(0%) 0(0%) 0(0%) [规则] [明细] 2007-7-26 14:20:00 ?《精粹》中的精粹:成为Excel高手的捷径 qee用 本人受好评明细查询 等级:贵宾 威望:13 文章:3712 精华:3 EH币:7733 积分:78 注册日期:2005年4月13日 行业:制造业 职业:人力资源 第 6 楼 ※邮件 有关链接: [原创]加载宏基础 Excel 加载宏 创建 Excel 加载宏 加载 Excel 加载宏 在 Excel 加载宏加载或卸载时自动运行代码 装载或卸载加载宏程序 无论你走得多么远,你的心总和我连在一起; 无论黄昏时树的影子有多长,它总是和树根连在一起。 穿别人的鞋，走自己的路，让他找去吧～ 帖子评价 (0.0) 0(0%) 0(0%) 0(0%) [规则] [明细] 2007-7-26 14:20:35 ? 新春巨献:15集Excel实战技巧视频教程免费下载 ? qee用 本人受好评明细查询 等级:贵宾 威望:13 文章:3712 精华:3 EH币:7733 积分:78 注册日期:2005年4月13日 行业:制造业 职业:人力资源 第 7 楼 ※邮件 后记 本层预留.... 谢谢 无论你走得多么远,你的心总和我连在一起; 无论黄昏时树的影子有多长,它总是和树根连在一起。 穿别人的鞋，走自己的路，让他找去吧～ 注释: This example shows how to register functions into user-defined catagories and provide descriptions for their arguments. The Auto_Open procedure registers the two functions, Multiply and Divide in two categories Multiplication and Division and provides descriptions of the input parameters. Const Lib = """c:\windows\system\user32.dll""" Option Base 1 Private Function Multiply(N1 As Double, N2 As Double) As Double Multiply = N1 * N2 End Function '========================================== Private Function Divide(N1 As Double, N2 As Double) As Double Divide = N1 / N2 End Function '========================================== Sub Auto_open() Register "DIVIDE", 3, "Numerator,Divisor", 1, "Division", _ "Divides two numbers", """Numerator"",""Divisor """, "CharPrevA" Register "MULTIPLY", 3, "Number1,Number2", 1, "Multiplication", _ "Multiplies two numbers", """First number"",""Second number """, _ "CharNextA" End Sub '========================================== Sub Register(FunctionName As String, NbArgs As Integer, _ Args As String, MacroType As Integer, Category As String, _ Descr As String, DescrArgs As String, FLib As String) Application.ExecuteExcel4Macro _ "REGISTER(" & Lib & ",""" & FLib & """,""" & String(NbArgs, "P") _ & """,""" & FunctionName & """,""" & Args & """," & MacroType _ & ",""" & Category & """,,,""" & Descr & """," & DescrArgs & ")" End Sub '========================================== Sub Auto_close() Dim FName, FLib Dim I As Integer FName = Array("DIVIDE", "MULTIPLY") FLib = Array("CharPrevA", "CharNextA") For I = 1 To 2 With Application .ExecuteExcel4Macro "UNREGISTER(" & FName(I) & ")" .ExecuteExcel4Macro "REGISTER(" & Lib & _ ",""CharPrevA"",""P"",""" & FName(I) & """,,0)" .ExecuteExcel4Macro "UNREGISTER(" & FName(I) & ")" End With Next End Sub Excel的工作表虽多，但我们还是常用到自定义函数。如果自定义函数返回了多个值，那怎么在单元格显示呢,答案就是用数组公式。 如下面的这个自定义函数，返回了公式所在单元格的工作簿名称，工作表名称，行号与列号: Function GetCallerInfo() As Variant With Application If TypeName(.Caller) = "Range" Then GetCallerInfo = Array(.Caller.Parent.Parent.Name, .Caller.Parent.Name, .Caller.Row, .Caller.Column) End If End With End Function 那在工作表中，先选择一行中的四个单元格，再用数组公式:{=GetCallerInfo()}，就可以列出返回值了。 如果要在一列中，就利用TRANSPOSE来转换区域，公式为:{=TRANSPOSE(GetCallerInfo())}。