加载宏学习.docx
《加载宏学习.docx》由会员分享，可在线阅读，更多相关《加载宏学习.docx（16页珍藏版）》请在冰豆网上搜索。
加载宏学习
Excel加载宏基础
一、简介
加载宏可以扩展Excel的功能，与Excel的内置功能一致，是为Excel提供自定义命令或自定义功能的补充程序。
某些加载宏还提供了可用于公式中的新的工作表函数。
使用某加载宏必须已安装，且须在加载宏对话框中进行调用。
加载宏默认安装在MicrosoftOffice\Office文件夹中的Library文件夹或Addins文件夹，或它们的子文件夹中。
其中，Excel加载宏是包含了所需要实现的功能的函数或过程的工作薄（保存为.xla文件）。
二、分类
1、Excel加载宏由ExcelVBA代码编写，扩展名为.xla。
2、COM加载宏由VB或VC或OfficeDeveloperEdition开发编写。
COM（ComponentObjectModel）加载宏，扩展名为DLL或EXE，可以与支持加载宏的所有Office应用程序一起使用。
其经过了编译后，有更可靠的安全性。
但其不能包含Excel的工作表或图表。
3、自动化加载宏由VB或VC编写
注：
本次学习只针对Excel加载宏，对COM加载宏和自动化加载宏仅略加介绍。
三、加载宏的特点
加载宏实现了代码和数据的分离，且不需要在Excel启动时加载，并为Excel提供了附加功能。
1、将自定义工作表函数作为加载宏，可以直接在其它工作薄中应用，而不必像在其它工作薄中运用自定义函数时必须加上函数所在工作薄的名称。
2、在存储加载宏时，若已对该加载宏进行属性设置，则在将该加载宏调入加载宏对话框中后可以清楚地看到该加载宏实现功能的说明。
3、当Excel启动时，可以自动打开已安装的加载宏，而不管加载宏存储在哪个目录下。
4、在关闭/卸载某加载宏时，不会显示诸如“是否保存对该加载宏的更改？
”的提示。
5、可以避免在打开工作簿时出现宏警告。
三、加载宏的调用
调用方法：
工具——加载宏（I）……，出现加载宏对话框，该对话框列出了所有可用的加载宏的名称，复选框已选中的加载框处于打开状态。
利用该对话框可以对加载宏进行添加和管理。
如果需要安装/卸载某加载宏，选中/取消选中相应的复选框即可。
也可以通过浏览按钮添加创建的自定义加载宏。
默认情况下，加载宏对话框中的文件类型有：
XLA文件（从XLS文件创建的加载宏）和XLL文件（单独编译过的DLL文件）。
若选中相应加载宏的复选框后，Excel的用户界面将发生一些微小的变化。
譬如，安装了“分析工具库”加载宏后，将在工具菜单中出现一个新的“数据分析（D）…”菜单项；安装了“欧元转换工具”加载宏后，会出现一个新的工具栏EuroValue。
若加载宏中只包含自定义工作表函数，则该函数会出现在“插入函数”对话框中（但如果用Private关键字声明了这个Function过程，则不会出现在此对话框中）。
如果在加载宏对话框中没有显示所需的加载宏程序，则需要按浏览按钮添加或创建加载宏（见下面如何创建加载宏介绍）。
注：
使用加载宏对话框中的自动化…按钮可以安装COM加载宏。
四、Excel中已有的加载宏
已有的加载宏简介：
1、分析工具库：
提供一组包括金融、统计和工程类的数据分析工具和函数，增添了Excel中没有包含的统计和分析功能。
主要有方差分析、相关系数、协方差、描述统计、指数平滑、F-检验双样本方差、傅利叶分析、直方图、移动平均、随机数发生器、排位与百分比排位、回归、抽样、t检验、z检验等。
2、分析工具库——VBA函数：
内容与上面的相同，允许开发人员用分析工具库的语法发布金融、统计及工程分析工具和函数。
为分析工具库提供的VBA函数。
3、条件求和向导：
提供了对列表中的数据根据不同的条件求和的工具。
4、欧元转换工具：
提供用于欧元转换的工具。
将数值的格式设置为欧元格式，并提供EUROCONVERT工作表函数用于转换货币。
5、InternetAssistantVBA：
开发人员可用InternetAssistant语法，将Excel数据发布到网站上。
6、查找向导：
创建在列表中查找数据的公式。
7、规划求解：
提供了公式求解和优化的工具。
对基于可变单元格和条件单元格的假设分析方案进行求解计算。
各加载宏详细的介绍见以后的学习专题。
五、如何创建Excel加载宏
不需要其他软件或工具，从Excel工作薄即可创建加载宏。
任何.xls文件也都可以转换为加载宏，但并不是所有的.xls文件都适合用作加载宏，一般将包含通用功能的工作薄转换成加载宏可以为使用提供很大的方便。
创建Excel加载宏的步骤如下：
1、新建一个Excel文档。
2、打开VB编辑器，插入一个模块。
3、添加一个函数或程序，即所需实现功能的函数或程序。
4、设置加载宏名称。
即返回Excel主界面，打开文件菜单下的属性菜单，输入相关内容，其中标题名即为加载宏的名称，备注栏中的说明即为对加载宏功能的描述，当选中这个加载宏时，这些说明将出现在加载宏对话框的底部。
5、保存工作薄。
打开文件菜单中的另存为…命令对话框，输入文件名，并在保存类型下拉列表中选择“MicrosoftOfficeExcel加载宏”，单击保存按钮。
现在，打开“加载宏”对话框，单击“浏览…”按钮打开刚创建的加载宏，则该加载宏将出现在对话框的列表中。
需要使用该功能时，只需在单元格中输入该函数或程序名即可。
注：
转换成加载宏的工作簿必须至少含有一个工作表，且工作表须处于活动状态。
六、处理加载宏的VBA过程
加载宏是AddIns集合的一个成员。
在Excel2003中，构成AddIns集合的部分加载宏的文件及其位置存储在Windows注册表中的下列位置：
HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Add-inManager
但Excel附带的标准加载宏不会出现在这个注册表中。
1、向AddIns集合添加新的AddIn对象的方法
（1）在Excel菜单中，选择工具——加载宏，单击浏览按钮，找到所需加载宏后确定即可。
（2）使用AddIns集合的Add方法。
如Application.AddIns.Add“<加载宏文件路径>”。
2、从AddIns集合中删除某加载宏
（1）编辑Windows注册表（使用regedit.exe），但并不适用于所有的加载宏文件。
（2）直接删除或移动该.XLA文件。
3、关于AddIns对象属性
共有14个属性，可以在帮助中查阅，其中5个为隐藏属性。
主要的属性有：
Name属性、Path属性、FullName属性、Title属性、Comments属性、Installed属性。
4、关于AddIns对象事件
有两个事件，即AddInInstall（安装时即会发生）和AddInUninstall（卸载时即会发生）。
EXCEL加载宏制作攻略
本文所述加载宏是指使用EXCELVBA制作的加载宏，这类文件的后缀默认为xla，因此又称XLA加载宏。
内容划分为五部分，第一部分为概述，简单介绍加载宏的一般知识；第二部分至第四部分介绍制作加载宏函数、过程和事件主要技巧；第五部分为制作加载宏的其它方面。
一、概述
1．制作加载宏的一般步骤
（1）创建一个新的工作薄，在其中添加代码。
（2）在“文件”菜单上单击“属性”。
在“文件名属性”对话框中，单击“摘要信息”选项卡，然后在“标题”框中为加载宏指定一个您希望在“加载宏”对话框中出现的名称，在“备注”框中输入您希望当用户从“加载宏”对话框选择该加载宏时在“加载宏”对话框下方出现的说明信息。
（3）在“文件”菜单上单击“另存为”。
在“另存为”对话框中，从“保存类型”框中选择“MicrosoftOfficeExcel加载宏（*.xla）”，然后再选择保存路径，并可在“文件名”框中修改文件名。
2．加载宏的加载和卸载
通过在“工具”菜单上单击“加载宏”，可以加载制作好的加载宏文件，如果它不在列表中，可以在“加载宏”对话框中单击“浏览”找到它，当某个加载宏名称前的复选框被选中时，说明它已经加载，取消选中复选框即为卸载该加载宏。
3．加载宏的作用
加载宏可以扩展EXCEL的功能，将代码写在加载宏中和写在普通的工作簿中相比，有如下优点：
（1）加载宏对所有打开的EXCEL文件都生效。
普通工作簿的代码一般只在特定的工作簿中才生效。
（2）加载宏不受宏安全级的限制，也不会有宏运行的提示。
即使将安全级设为“非常高”，加载宏就象EXCEL本身固有的功能一样工作。
同样，它也不会被按住的Shift屏蔽。
特别提请不习惯使用加载宏而专门VBA代码书写的朋友们注意的是，加载宏的所有好处和优点，都是属于EXCEL的常规操作者的，虽然加载宏中的代码可以被其他工作簿（中的代码）调用，但这样做是繁琐而低效的，千万不要把它当成了C中的“头”文件或链接库。
也许不会有很多人这样认为，但笔者初学VBA的时候，真的这样天真过。
（3）加载宏在运行时其工作簿窗口是隐藏的，因此普通用户并不会感觉到它的存在。
二、处理加载宏函数
加载宏函数似乎是制作加载宏中最简单的，通常它只需要在模块中写一段Function代码，它就会和EXCEL自身的函数一样使用，并且出现在“插入函数”对话框的列表中。
关于函数的制作，再没有其它的吗？
1．为函数添加说明信息
至少你可以通过对象浏览器设置“属性”为函数添加一段说明信息（对需要换行的说明可以在编辑时使用组合键CTRL+ENTER），这些信息当用户使用“插入函数”对话框时就会看到。
需要说明的是，在对象浏览器中为函数添加说明信息必须在“另存为”加载宏之前，一旦文件名的后缀变为了XLA，函数的属性设置将不被接受。
关于为函数定制说明信息，请参见：
图示自定义函数描述信息及有关介绍。
现在，请你新建一个工作簿，插入模块，粘贴下面的代码：
Functiondx（n）
'bygly1126金额小写转换为大写
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,"零元零角",""）,"零元",""）,"零角","零"）,"-","负"）
EndFunction
如果觉得必要，你可以设置文件属性（见一、1．
（2））并为该函数添加说明，先把它做为普通的工作簿保存（我们后面还会继续用它）再另存为XLA，然后感受一下它的效果吧。
对已经成为XLA文件的函数说明的添加及修改并不需要总是手工先还原为XLS文件，我们稍后就会提到MacroOptions方法。
在此之前，先来说一下与加载宏相关的Workbook事件：
（1）AddinInstall和AddinUninstall。
这两个事件分别发生在加载宏加载和卸载时的，因此使用这两个事件的工作簿一定是XLA工作簿，你需要记住的是，它们在加载宏作用期内各自只会发生一次，加载宏加载后每次EXCEL的打开和关闭都不会触发它们。
（2）Open和BeforeClose。
和上两个事件不同，这两个事件在加载宏作用期内会被多次触发，除了分别发生在AddinInstall后和AddinUninstall前之外，每次打开和关闭EXCEL时都会触发它们。
清楚了上述特性，你就应该知道如何合理安排加载宏的初始化代码。
2．为函数分类
默认情况下，当用户使用“插入函数”时，加载宏函数会被分类放在“用户定义”类别中。
哦，没有个性！
有的朋友甚至还会有被歧视的感觉呢，怎样才能和EXCEL自身的函数一样被放在“财务”类呢？
我们会想到MacroOptions方法：
Application.MacroOptionsMacro:
="dx",Category:
=1
不熟悉这个方法不要紧，看完后面的例子你再去查看一下它的帮助，很快就会掌握的。
现在的问题，上面的语句放在哪儿呢？
首先把它放在AddinInstall事件中看看，你会发现，在第一次加载时，目的确实达到了，但当你关闭EXCEL重新打开时，却发现它又回到了“用户定义”中，看来需要放在Open事件中：
PrivateSubWorkbook_Open（）
ThisWorkbook.IsAddin=False
Application.MacroOptionsMacro:
="dx",Category:
=1
ThisWorkbook.IsAddin=True
ThisWorkbook.Saved=True
EndSub
你会发现，除了我们需要的，又增加了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.MacroOptionsMacro:
="dx",Category:
="财务"
那么是否可以为加载宏函数增加一个新类别呢？
很简单！
只需要将上面的Category参数改变一下就可以了：
Application.MacroOptionsMacro:
="dx",Category:
="财务扩展函数"
上句会在“插入函数”对话框中增加一个新类别“财务扩展函数”，并把dx函数放入其中。
MacroOptions方法还有其它一些参数，可以帮助我们，包括为函数添加说明，下面语句在分类的同时会为函数增加说明。
Application.MacroOptionsMacro:
="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）”，如果一个函数是为工作表准备的，代码就要受到限制。
先来看一个错误的函数：
FunctionTTT（rgAsRange）AsString
rg.Value=rg.Value+1
TTT=rg.Value
EndFunction
上面的函数试图将参数区域的值先加1，然后返回其值。
如果你在代码中使用
N=TTT（[a1]）
不会有任何问题，现在，你在A2单元格输入公式“=ttt（A1）”看看。
限于篇幅，本文不再探讨理论问题，而是直接给出这些应该注意的结论：
（1）严格遵循“黑匣子”理论，不要试图去改变外部对象的属性，包括参数对象，即使它们是传址方式的。
对外部对象值的改变，会导致函数的结果错误，如你在上面看到的“#VALUE！
”，其它属性的改变，通常会被忽略。
（2）不要使用Volatile方法，除非你要构建的函数确实是象RAND类型的易失性函数。
在函数中加入：
Application.Volatile
会告诉系统该函数是易失性函数，则当工作表的任何区域发生改变时，该函数都会被重算。
这将严重影响系统的效率。
要做到这一点，应当将所有影响函数值变动的区域都作为参数，即使某些区域是固定的也要作为参数传递。
（3）函数中可以使用可选参数，但可选参数应避免为Range类型。
因为使用Range类型的可选参数，将不可避免地使用Application.Volatile。
三、处理加载宏过程
加载宏的另一个重用功能就是提供扩展的操作，这通常是写在模块中的Sub过程。
设计加载宏过程应注意两个方面，一是代码实体，二是提供适当的接口方式以便用户可以使用这些操作。
在代码实体的设计上，加载宏代码与其它VBA代码看上去也许没有太多的差别，但它要求设计者更为密切地注意加载宏的运行环境，对象成员的使用也更应规范和严谨。
比如，喜欢混用ThisWorkbook和ActiveWorkbook的朋友要好好看看帮助文档中二者的差别了。
现在请在模块中粘贴下面的代码：
SubHVCenter（）
'这段代码的含义很简单，让选定区域文字水平垂直居中
WithSelection
.HorizontalAlignment=xlCenter
.VerticalAlignment=xlCenter
EndWith
EndSub
在接口处理上，一般有3种处理方式。
1．快捷键
为过程设置快捷键可以在另存为加载宏文件前在“工具”菜单上单击“宏”-“宏”，在“宏”对话框中，单击“选项”按钮完成。
也可使用前面介绍过的MacroOptions方法设置。
Application.MacroOptionsMacro:
="",HasShortcutKey:
=True,ShortcutKey:
="A"
2．菜单
通过在系统菜单上增加菜单项可以将操作提供给使用者，加载宏是对EXCEL基本功能的扩充，一般不提倡使用自定义菜单来代替系统菜单。
3．工具栏
可以使用EXCEL现有的工具栏上增加按钮也可以通过新建工具栏来完成。
下面代码将前面的过程关联到新建的菜单项和工具栏上。
PrivateSubWorkbook_AddinInstall（）
OnErrorResumeNext
'新建菜单
WithApplication.CommandBars
（1）.Controls.Add（Type:
=msoControlPopup）
.Caption="测试（&T）"
With.Controls.Add（Type:
=msoControlButton）
.Caption="居中"
.OnAction="HVCenter"
EndWith
EndWith
'新建工具栏
WithApplication.CommandBars.Add（Name:
="myCmdbar"）
.Position=msoBarTop
With.Controls.Add
.FaceId=352
.Caption="居中"
.OnAction="HVCenter"
EndWith
.Visible=True
EndWith
EndSub
由于工具栏和菜单生成后不会随EXCEL的关闭而消失，因此创建工具栏和菜单的代码一般应写在AddinInstall事件中，并且在加载宏卸载时随之卸载。
下面代码卸载前面创建的工具栏和菜单。
PrivateSubWorkbook_AddinUninstall（）
OnErrorResumeNext
DimctlAsCommandBarControl
'卸载工具栏和菜单
Application.CommandBars（"myCmdbar"）.Delete
ForEachctlInApplication.CommandBars
（1）.Controls
Ifctl.Caption="测试（&T）"Thenctl.Delete
Nextctl
EndSub
四、处理加载宏事件
原则上讲，加载宏事件应对所有打开工作簿发生的事件进行处理。
加载宏事件的设计相对函数和过程的设计而言通常要复杂得多，它要求设计者具有一定的类知识和良好的代码组织能力。
具体设计主要应解决两方面问题，一是事件代码本身，二是将预作的“事件代码”和对象关联。
我们仍通过实例，继续前面的设计来介绍。
任务：
让加载宏实现当用户在活动工作簿的活动工作表选择某一区域时，状态栏显示该区域的范围。
1．设计事件代码
（1）分析要用的对象和事件
不难知道，本例需要Workbook对象的SheetSelectionChange的事件。
首先使用WithEvents关键字定义一个Workbook对象。
DimWithEventswkbAsWorkbook
注意，WithEvents关键字不能在标准模块中使用，因此，代码一般写在Thisworkbook中。
（2）书写事件代码
PrivateSubwkb_SheetSelectionChange（ByValShAsObject,ByValTargetAsRange）
Application.StatusBar="你选择的区域:
"&Replace（Target.Address,"$",""）
EndSub
2．关联对象
（1）分析需要关联的所有情况
写好了上面的代码，如何将它与活动工作簿关联呢？
首先应该分析可能进行关联的时机。
以本例，大体我们将需要（改变）关联，即活动工作簿发生变化的情况列示如下：
a.当新建工作簿时，关联对象为新建的工作簿
b.当新打开工作簿时，关联对象为新打开工作簿
c.当在两个工作簿之间切换时，关联对象为新切换到的工作簿
（2）完成关联代码
上面的分析知道，需要用到Application级的事件。
定义Application对象，写入相应的事件，然后在Open事件中将它关联到当前的Application即可。
汇合前面的代码如下：
DimWithEventsappAsApplication
DimWithEventswkbAsW
升级会员 