Excel制表实例操作之(8)宏与VBA编程运用

第八章宏与VBA编程运用8.1表格制作中宏的运用8.2VBA编程应用宏与VBA编程运用CHAPTER8226227CHAPTER8宏与VBA编程运用宏与VBA编程运用CHAPTER8226227CHAPTER8宏与VBA编程运用8.1表格制作中宏的运用例1Excel加载宏的方法我们都知道Excel是一个强大的数据处理工具，然而，当我们要进行特殊操作时，Excel并不一定能满足我们独特的需要。还好，当我们感觉Excel不够用时，可以利用加载宏来实现。1.加载宏的含义加载宏也就是利用外部程序来增加Excel的功能，加载宏程序就是Excel的插件，可以补充Excel的各种命令或功能。这些功能往往都是不常用的，但是在有特殊需要的时候很实用。比如说分析数据库加载宏程序提供了一套数据分析工具，在进行复杂统计或工程分析时，可以简化操作步骤，从而提高工作效率。2.加载宏的类型Excel有两种类型的加载宏程序可以使用，分别是Excel加载宏和自定义的组件对象模型(COM)加载宏。后者是用各种微软的编程工具（如：VisualC＋＋）或能够创建COM、DLL和可执行文件的其他编程工具编写的，目前比较流行的是Excel加载宏。3.载入加载宏Excel载入宏的过程，实际上就是将以.xla为扩展名的文件加入到软件中，选择“工具”菜单中的“加载宏”命令，然后点击“浏览”按钮，找到要载入的加载宏，接着选择刚才加入的宏，按确定按钮，相关命令就添加到Excel的对应菜单中。其实在安装Excel时已经附带了一些加载宏，只要在对话框中选中复选框就可以使用相应的功能。宏与VBA编程运用CHAPTER8226227CHAPTER8宏与VBA编程运用宏与VBA编程运用CHAPTER8226227CHAPTER8宏与VBA编程运用加载宏命令例2利用宏加载“扩展工具”这里以实际例子介绍加载“扩展工具”的方法，到网络中下载“扩展工具”加载程序。其中包含了货币换算、改变大小写、时间提示、中文大写金额转换等辅助功能。这在某些时候会大大提高我们的工作效率。启动Excel并运行“工具→加载宏”，在弹出的窗口中点击“浏览”，找到刚才那个解压过的文件之后按下“确定”按钮即将其加载到Excel中。加载之后，我们可以发现Excel增加了一个“新增”菜单，点击之后将查看到各种新增的辅助功能，都可以方便地使用。新增加载宏宏与VBA编程运用CHAPTER8228229CHAPTER8宏与VBA编程运用宏与VBA编程运用CHAPTER8228229CHAPTER8宏与VBA编程运用例3利用宏给文档自动添加密码在Excel中给工作簿文档添加密码时，需要通过选项一个一个的设置，比较麻烦。下面，我们利用一个自动运行的宏，让软件自动给文档添加密码。（1）启动Excel，执行“工具→宏→VisualBasic编辑器”命令，进入VBA编辑状态。（2）在左侧的“工程资源管理器”窗口中，选中“VBAproject(PERSONAL.XLS)”(个人宏工作簿)选项。（3）执行“插入→模块”命令，插入一个模块(模块1)。（4）将下述代码输入到右侧的代码编辑窗口中：SubAuto_close()ActiveWorkbook.Password=123456ActiveWorkbook.SaveEndSub退出VBA编辑状态。输入代码注意这是一个退出Excel时自动运行的宏，其宏名称(Auto_close)不能修改。（5）以后在退出Excel时，软件自动为当前工作簿添加上密码(123456，可以根据需要修改)，并保存文档。宏与VBA编程运用CHAPTER8228229CHAPTER8宏与VBA编程运用宏与VBA编程运用CHAPTER8228229CHAPTER8宏与VBA编程运用例4用窗体调用“宏”通过运行“宏”对话框调用宏不太方便，通过快捷键调用宏，如果宏多了又记不住，那就添加一个按钮吧。如果宏仅对当前工作簿文档有效，我建议大家在工作簿文档中添加一个“窗体”命令按钮，用来调用宏：（1）执行“视图工具栏窗体”命令，展开“窗体”工具栏。（2）点击工具栏上的“命令按钮”按钮，然后在工作表中拖拉出一个按钮来。视图工具栏窗口（3）此时系统自动弹出“指定宏”对话框，选中需要调用的宏，确定返回。（4）将命令按钮上的字符修改为一个合适的内容，调整好命令按钮的大小，将其定位在工作表合适位置上。按一下该按钮，即可执行相应的宏。例5Excel禁止自动运行宏你可能遇到过可恶的宏病毒，其中有一部分是在打开文件时自动运行并产生危害。在你打开一个Excel文件时，可以很容易地阻止一个用VBA写成的在打开文件时自动运行的宏的运行。从“文件”菜单中选择“打开”，选择你所要打开的文件的名字，在点击“打开”时按住“Shift”键，Excel将宏与VBA编程运用CHAPTER8230231CHAPTER8宏与VBA编程运用宏与VBA编程运用CHAPTER8230231CHAPTER8宏与VBA编程运用在不运行VBA的情况下，打开该工作薄。按住“Shift”键阻止宏运行的方法同样适用于选择打开菜单底部的文件（最近打开的几个文件）。同样，在你关闭一个Excel文件时，也可以很容易地阻止一个宏的运行。从“文件”菜单中选择“关闭”，在点击“关闭”时按住“Shift”键，Excel将在不运行VBA过程的情况下关闭这个工作薄。注意按住“Shift”键同样适用于点击窗口右上角的“×”关闭工作薄时阻止宏的运行。例6Excel中宏的录制在录制宏命令时，Excel将自动记录并储存用户所执行的一系列菜单命令的信息。（1）打开带有宏的原始文件，切换到自己编辑的工作表中。（2）选择“工具→宏→录制新宏”菜单项。录制新宏（3）随即会弹出“录制新宏”对话框，然后在“宏名”文本框中输入“标题”，将“快捷键”设置为“Ctrl+A”,并且在“保存在”下拉列表中选择“当前工作簿”选项。参数设置宏与VBA编程运用CHAPTER8230231CHAPTER8宏与VBA编程运用宏与VBA编程运用CHAPTER8230231CHAPTER8宏与VBA编程运用（4）单击“确定”按钮即可开始宏的录制操作。（5）选择“格式→单元格”菜单项，弹出“单元格格式”对话框，切换到“对齐”选项卡中，然后分别在“水平对齐”和“垂直对齐”下拉列表中选择“居中”选项，并且选中“文本控制”组合框中的“合并单元格”复选框。对齐选项卡（6）切换到“字体”选项卡中，然后在“字体”列表框中选择一个自己喜欢的字体。在颜色列表中选择自己喜欢的颜色。字体的选择（7）切换到“边框”选项卡中，在“线条”组合框中的“样式”列表框中选择一种合适的样式，然后单击“预置”组合框中的“外边框”按钮。宏与VBA编程运用CHAPTER8232233CHAPTER8宏与VBA编程运用宏与VBA编程运用CHAPTER8232233CHAPTER8宏与VBA编程运用选择外边框（8）选择“工具→宏→停止录制”菜单项。即完成了宏的录制操作。停止录制例7如何在Excel中运行宏对于刚刚录制好的宏，可以通过运行宏命令来验证下运行的效果。（1）打开刚才录制的宏文件。（2）单击“启动宏”按钮进入到工作表，然后选中单元格区域“A1:F1”。（3）选择“工具→宏→宏”菜单项。选择命令（4）随即会弹出“宏”对话框，然后在“宏名”列表框中选择“标题”选项。宏与VBA编程运用CHAPTER8232233CHAPTER8宏与VBA编程运用宏与VBA编程运用CHAPTER8232233CHAPTER8宏与VBA编程运用设置宏名（5）单击“执行”按钮，系统会自动地执行宏。例8编辑与修改定制的宏其实宏就是VBA的一个子过程，他和VBA紧密相关。录制完毕后，我们也可以对宏进行修改和编辑。具体操作步骤如下:（1）打开自己录制好的宏文件。（2）单击“启动宏”按钮进入到工作表，然后选中单元格区域“A1:F1”。（3）选择“工具→宏→宏”菜单项，弹出“宏”对话框，然后在“宏名”列表中选择“标题”选项。（4）单击“编辑”按钮，弹出刚才我们录制宏的名字的文件窗口。再此窗口中显示了录制宏的命令。编辑按钮弹出编辑窗口（5）查找出设置字体的代码，将字体“楷体-GD2312”改为“隶书”，字号设置为“16”。宏与VBA编程运用CHAPTER8234235CHAPTER8宏与VBA编程运用宏与VBA编程运用CHAPTER8234235CHAPTER8宏与VBA编程运用（6）单击“运行→运行子过程/用户窗体”按钮，运行此程序。然后单击“视图MicrosoftExcel”按钮。此时选中的单元格区域中的字体已经变为你重新设置的大小了。运行子过程/用户窗体注意如果你想删除宏，直接点击“宏”对话框中的删除按钮即可。8.2VBA编程应用例1VBA中的数组应用数组是包含相同数据类型的一组变量的集合，对数组中的单个变量引用通过数组索引下标进行。在内存中表现为一个连续的内存块，必须用Global或Dim语句来定义。定义规则如下：Dim数组名([lowerto]upper[,[lowerto]upper,….])astype;Lower缺省值为0。二维数组是按行列排列，如XYZ(行，列)。除了以上固定数组外，VBA还有一种功能强大的动态数组，定义时无大小维数声明；在程序中再利用Redim语句来重新改变数组大小，原来数组内容可以通过加preserve关键字来保留。如下例：Dimarray1()asdouble:Redimarray1(5):array1(3)=250:Redimpreservearray1(5,10)宏与VBA编程运用CHAPTER8234235CHAPTER8宏与VBA编程运用宏与VBA编程运用CHAPTER8234235CHAPTER8宏与VBA编程运用例2使用注释和赋值语句（1）注释语句是用来说明程序中某些语句的功能和作用；VBA中有两种方法标识为注释语句。（2）赋值语句是进行对变量或对象属性赋值的语句，采用赋值号=，如X=123：Form1.caption=“我的窗口”。对对象的赋值采用：setmyobject=object或myobject:=object。例3重要函数Choose和Switch1.choose函数choose(index,choce-1,choice-2,…,choice-n)可以用来选择自变量串列中的一个值，并将其返回，index必要参数，数值表达式或字段，它的运算结果是一个数值，且界于1和可选择的项目数之间。2.Switch函数Switch(expr-1,value-1[,expr-2,value-2_[,expr-n,value-n]])switch函数和Choose函数类似，但它是以两个一组的方式返回所要的值，在串列中，最先为TRUE的值会被返回。expr必要参数，要加以计算的Variant表达式。value必要参数。如果相关的表达式为True，则返回此部分的数值或表达式，没有一个表达式为True，Switch会返回一个Null值。例4VBA中循环语句的使用结构化程序使用以上判断和循环语句已经足够，建议不要轻易使用下面的语句。（1）Gotoline语句为跳转到line语句行。（2）Onexpressiongosubdestinatioinlist或者onexpressiongotodestinationlist语句为根据exprssion表达式值来跳转到所要的行号或行标记。（3）Gosubline…line…Return语句，Return返回到Gosubline行，如下例：宏与VBA编程运用CHAPTER8236237CHAPTER8宏与VBA编程运用宏与VBA编程运用CHAPTER8236237CHAPTER8宏与VBA编程运用Subgosubtry()DimnumNum=inputbox(“输入一个数字，此值将会被判断循环”)Ifnum0thenGosubRoutine1：Debug.printnum：ExitsubRoutine1:Num=num/5ReturnEnd