ExcelVBA程序开发自学宝典第Word格式文档下载.docx
《ExcelVBA程序开发自学宝典第Word格式文档下载.docx》由会员分享，可在线阅读，更多相关《ExcelVBA程序开发自学宝典第Word格式文档下载.docx（84页珍藏版）》请在冰点文库上搜索。
图中设计宏程序“新建菜单”的快捷键是【Ctrl+q】。
图“宏”对话框图设置Sub过程的快捷键
3.按钮执行
在工作表中建一个按钮，并将按钮与Sub过程关联，从而实现单击按钮执行程序。
将按钮关联到Sub过程的步骤为：
1.单击菜单【开发工具】→【表单控件】→【按钮】；
2.在工作表中按下左键并向右下方拖动，从而绘制一个控件按钮；
3.在弹出的对话框中选择“新建菜单”，如图所示；
4.返回工作表后即可单击名为“按钮1”的按钮来执行程序“新建菜单”。
图关联过程与按钮
4.菜单调用
最常见的是编写一个自定义菜单或者工具条来调用Sub过程。
菜单与工具条的设计方法参见本书第19章及第20章。
5.事件引发
对于部分需要自启动的程序，通常利用事件引发，不需要人工干预。
例如工作簿开启时就自动执行某程序，或者关掉窗体、鼠标移过窗体时执行某程序……
对于事件过程的运用参见本书第8章。
6.工作表中使用公式调用
Function过程即自定义函数，可以像使用内置的工作表函数一样在公式中使用。
调用Function过程的步骤如下：
1.单击菜单【插入】→【模块】；
2.在模块中录入以下代码：
Function成绩（rng）
成绩=IIF（rng>
=60,"
及格"
"
不及格"
）
3.返回工作表中，在A1输入数值50，在B1输入公式：
=成绩（A1）
可以发现公式可以像内置函数一样运行，它返回“不及格”，正是期望的结果。
插入过程的方式
编写过程时可以手工录入代码，也可以让利用VBA提供的列表自动产生程序外壳。
一个Sub过程分为程序外壳部分和主体部分。
如图所示。
图过程的外壳与主体部分示意图
其中外壳部分可以手工录入，也可以利用VBE提供的方式完成。
1.非事件过程
对于非事件的Sub过程，VBA提供了一个专用窗体来选择性录入过程的外壳。
具体步骤如下：
1.在VBE界面中单击菜单【插入】→【模块】；
2.单击菜单【插入】→【过程】打开“添加过程”对话框；
3.在“名称”框中录入“汇总”，并将“类型”选择“子过程”，将“范围”设为“私有的”，如图所示。
然后单击“确定”按钮。
图添加Sub过程外壳图
执行以上程序后在模块中可以看到产生的代码为：
PrivateSub汇总（）
如果是Function函数过程，也可以按照上述方法录入过程的外壳。
2.事件类过程
VBA支持很多类事件，大部分事件的代码都需要参数。
而这些参数是很难记忆的，包括所有VBA专业程序员。
为了快速且准确地录入事件类过程，可以通过VBE提供的对象与过程窗口的下拉列表完成。
例如输入工作表SelectionChange事件的过程，方法如下：
（1）使用快捷键【Alt+F11】进入VBE界面，并用快捷键【Ctrl+g】打开工程资源管理器窗口；
（2）双击Sheet1或者其他需要录入工作表事件的工作表名；
（3）从对象窗口的下拉框中选择“Worksheet”，代码窗口默认产生以下代码：
PrivateSubWorksheet_SelectionChange（ByValTargetAsRange）
图从下拉列表选择对象
因为VBA默认状态下就是弹出“Worksheet_SelectionChange”事件的代码，所以当选择对象为“Worksheet”后就产生了需要的代码。
如果需要录入“Worksheet_Change”事件的代码，则需要在选择对象“Worksheet”后，再选择过程“Change”，然后将产生的“Worksheet_SelectionChange”事件的代码删除，仅保留以下代码：
PrivateSubWorksheet_Change（ByValTargetAsRange）
对于此类包含参数的事件过程，应该尽量选择通过对象与过程窗口的下拉列表产生代码的方式，手工录入很容易产生误差。
在用户窗体中很多事件也支持参数，而且有多个参数，通常也需要从列表中选择对象与过程的方式来录入代码。
例如在窗体中录入鼠标移过事件的过程代码，步骤如下：
1.单击菜单【插入】→【用户窗体】；
2.使用快捷键【Ctrl+g】显示工程资源管理器，并在UserForm1（或者别的名称）上单击右键，选择菜单【查看代码】；
3.从对象窗口选择“UserForm1”，此时默认产生“UserForm_Click”事件的代码；
再从过程窗口选择“MouseMove”，代码窗口中将产生以下代码：
PrivateSubUserForm_MouseMove（ByValButtonAsInteger,ByValShiftAsInteger,ByValXAsSingle,ByValYAsSingle）
4.删除“UserForm_Click”事件的代码。
过程的命名规则
过程的命名与变量的命名规则一致。
但需要补充的一点是：
过程名可以和本过程的私有变量同名，但却不能和公有变量同名。
例如：
Sub身份证（）
Dim身份证AsString
身份证=[a1].Text
以上代码中过程与变量同名，但这是允许的。
这段代码却是非法的，只要运行程序就会弹出编译错误。
为了避免错误及便于识别，需要使本过程的私有变量也尽量保持与过程名不相同。
编写Sub过程
本节开始了解关于Sub过程的基本概念，以及编写简单的Sub过程。
过程的语法解析
Sub过程即利用Sub语句声明的子过程。
所有宏录制器产生的过程全是Sub过程，无法通过录制宏产生Function过程或者属性过程。
Sub语句声明过程的语法如下：
Private|Public|Friend][Static]Subname[（arglist）]
[statements]
[ExitSub]
其中各参数的详细功能如表6-1所示。
表6-1 Sub语句参数详解
参数部分
功能解释
Public
可选的。
表示所有模块的所有其他过程都可访问这个Sub过程。
如果在包含OptionPrivate的模块中使用，则这个过程在该工程外是不可使用的
Private
表示只有在包含其声明的模块中的其他过程可以访问该Sub过程
Friend
只能在类模块中使用。
表示该Sub过程在整个工程中都是可见的，但对对象实例的控制者是不可见的
Static
表示在调用之间保留Sub过程的局部变量的值。
Static属性对在Sub外声明的变量不会产生影响，即使过程中也使用了这些变量
name
必需的。
Sub的名称；
遵循标准的变量命名约定
arglist
代表在调用时要传递给Sub过程的参数的变量列表。
多个变量则用逗号隔开
statements
Sub过程中所执行的任何语句组
Sub过程与所有变量一样，也区分公有和私有，而在说法上稍有区别。
过程分模块级过程和工程级过程。
1.模块级过程
模块级过程即只能在当前模块调用的过程，它的特征有三个：
（1）声明Sub过程前使用Private；
（2）只有当前模块可以调用，例如在“模块1”中有以下代码：
PrivateSub过程一（）
MsgBox123
PrivateSub过程二（）
Call过程一
执行过程二时可以调用过程一，但如果过程二存放于“模块2”中，则将弹出“子过程未定义”的错误提示。
（3）不出现在“宏”对话框中，即使用快捷键【Alt+F8】所打开的对话框中无法查看到当前过程的名称列表。
如果是Function过程，则无法在函数向导中查看到函数名。
提示：
所有事件的代码都是过程级的，默认状态下只能在当前过程可以调用。
2.工程级过程
工程级过程是指在当前工程中任意地方都可以随意调用的过程。
它的特征刚好与模块级过程相反：
在“Sub”语句前置标识符“Public”、非当前过程可以调用，可以出现在“宏”对话框中。
如果一个过程没有使用“Public”和“Private”标识，则默认为公有过程，任何模块或者窗体中都可以调用。
Sub过程也支持参数，其参数的用法与Function过程的参数用法一致，本小节不详述，请参阅本书节。
与End的作用与区别
Sub过程可以在程序执行期间随时退出程序，通常是设定若干个条件。
当满足条件时使用“ExitSub”语句来终止程序。
“ExitSub”语句后面的代码不再执行。
也可以使用“End”语句来退出程序。
“End”和“ExitSub”在使用中有相同处，也有明显的差别。
相同处是都可以中途终止运行程序，不同处则有以下两点。
（1）是否释放公有变量
从以下三段代码可以体现“End”和“ExitSub”的差异：
DimxAsLong
SubA（）
x=888
ExitSub
SubB（）
End
SubC（）
MsgBoxx
代码中X是公有变量，当执行过程A后执行过程C，那么变量X的值为888，表示X变量的值在过程中并没有释放，“ExitSub”仅仅退出程序执行，公有变量的值保持不变。
如果执行过程B再执行过程C，那么X的值则为0，说明在过程B中的“End”语句已经释放变量X的值。
（2）是否终止所有程序
仍然用三个过程来演示“End”和“ExitSub”的差异：
CallB
MsgBox"
终止"
执行程序A的结果是弹出对话框“终止”，而将过程A中的“CallB”修改为“CallC”，那么什么反应也没有。
也就是“ExitSub”是退出它所在的程序，而“End”则中止所有程序，包括调用它的程序。
如果在窗体代码中，“ExitSub”仅仅退出事件，而“End”则退出事件后关掉窗体，窗体中声明的所有变量全部释放。
过程的执行流程
如果录制宏并执行宏，可以看出宏代码的执行流程永远是从上到下。
可以使用调试功能来查看流程。
例如执行以下代码：
Sub设置A1单元格（）
Range（"
A1"
）.Select
）="
中华人民共和国"
）.=65535
）.=3
）.=xlContinuous
）.="
黑体"
）.=20
）.
将VBE窗口缩小，使自己能同时看到代码及A1单元格的情况下再按下快捷键【F8】，从而进入逐句调试阶段。
注意：
在VBE中使用【F8】键表示调试代码语句，每按一次【F8】键即执行一句，忽略变量与常量的声明语句，直到“ExitSub”或者“End”、“EndSub”为止。
在编写代码时非常有用，可以借助它检查代码的准确性，同时也可以查看程序间的跳转是否正常（当有标签设置和嵌套调用的时候）。
当按下调试键【F8】时，当前执行的语句呈黄色显示，再次按下【F8】键时，则下一句呈黄色显示，而操作对象A1则对应产生变化。
图中已执行到第四句，所以A2单元格同步后的状态就是已录入“中华人民共和国”，且并设置了黄色背景色。
图逐步执行代码
当继续通过【F8】键执行完成的代码后，可以得出结论：
所有录制的宏和未特别指定程序跳转的VBA代码总是按照从上至下的流程逐句执行。
那么是否有例外呢？
通常在以下三种情况会有例外。
1.使用冒号实现一行执行多句代码
VBA中允许借助冒号将多句代码写在同一行执行。
对同行中的代码按从左向右的顺序执行。
:
）.=3:
以上代码在借助冒号将前四行代码缩至两行，但执行过程仍然为四步。
对于同行中有多句代码时，按从左向右的顺序执行。
那么读者一定可以想到，使用冒号和不使用冒号的执行结果岂不是完全一致？
仅仅改变了行数？
答案是“有时一致，有时不一致”。
如果以上的代码按如下方式编写，那么通过冒号改变行数后执行结果完全一致：
而在下面的情况中，使用冒号后却可以得到完全不同的结果：
Sub判断是否及格1（）
IF[B2]>
=60Then[C3]="
IF[B3]>
Sub判断是否及格2（）
假设工作表中有图所示数据，执行过程“判断是否及格1”时，C3单元格将出现“及格”；
而执行过程“判断是否及格2”时则无任何反应。
也就是说“ExitSub”语句与IF同行时，只有单元格B2的值大于或等于60，“ExitSub”语句才会执行。
在本例中不符合条件，那么没有退出程序，可以继续执行其后的代码。
而“ExitSub”语句单独占据一行时，不管单元格B2是否符合条件，“ExitSub”都会执行，从而退出程序，不再对B3的值进行判断。
图数据
2.使用标签改变执行流程
VBA可以在代码中设置一个或者多个标签，然后让程序在满足某条件时跳转到标签处，从而改变过程执行流程。
标签的规则是：
◆可以是标点符号以外的字符组合
◆以冒号（:
）结尾
◆与大小写无关
◆必须位于一行的最左端
◆配合GoTo使用
例如，建立一个名为“总表”的工作表，代码如下：
Sub新建总表（）
Fori=
IFSheets（i）.Name="
总表"
ThenGoToerr
Nexti
="
err:
已经存在总表"
以上代码首先利用For循环逐一检查工作表的名字，如果某个工作表的名字等于“总表”则执行标签“Err”之后的代码，否则继续执行For循环，直到循环完成并新建一个工作表且命名为“总表”。
使用标签完成当前程序间的跳转时需要注意两点：
（1）标签名后面必须带有冒号。
（2）在标签之前根据需要，及时退出程序。
在本例中，按照设计意图，只要工作簿中存在“总表”则执行标签“Err”之后的语句，反之不执行。
所以标签之前必须加入“End”或者“ExitSub”来退出程序，否则任何情况下Err后的语句都会被执行。
在一个过程中还可以定义多个标签。
=trueThenGoTo已加密
ThenGoTo已存在
已存在:
已加密:
当前工作簿窗口已锁定，无法建立新表"
在此过程中，首先判断当前工作表的窗口是否锁定，如果锁定则执行“已加密”标签后的语句；
然后再检查是否存在“总表”，当有“总表”时执行“已存在”标签后的语句。
本例中两个标签没有顺序上的差异，谁前谁后不影响代码的结果。
过程的嵌套调用方式
过程与过程之间是可以相互调用的，从而使代码的执行流程改变。
通过VBA代码调用Sub子过程主要有两种方式。
◆Call语句
Call语句的功能是将一个过程的控制权转移到另一个过程。
它的语法为：
[Call]name[argumentlist]，即Call过程名参数。
其中Call是可选的，即在其他过程调用过程一时可以有以下两种形式：
Sub过程一（）
你好！
"
过程一
PrivateSub过程三（）
过程二和过程三都是合法的过程调用。
◆Run方法
Run方法可以运行一个宏或者调用一个函数。
该方法可用于运行一个用VisualBasic或Excel宏语言编写的宏或者运行DLL或XLL中的函数。
实例如下：
Sub过程四（）
过程一"
其中“”也可以简写为“Run”。
过程的递归
所有过程都是可以递归的，即可以调用自己来完成任务。
实际工作中需要调用过程本身的实例极少，通常进入递归都是编码有问题而误入递归状态，结果耗尽系统资源。
在某些情况下也可以故意调用自己来完成任务。
例如下面2例。
1.按条件新建工作表
Sub建立10个表（）
>
=10ThenExitSub
Sheets,1
Call建立10个表
以上代码中，首先利用IF查找并检测当前工作簿的工作表数量，如果大于或等于10则退出程序，否则在最后位置新建一个工作表，最后再调用自身继续执行，直到满足条件“大于或等于10”为止。
因代码中人为设置了退出递归的条件，所以这类递归不会造成程序崩溃，资源耗尽。
如果将代码中的“>
=10ThenExitSub”删除，那么程序循环执行的结果就是电脑死机，除非中途人工中断程序执行：
使用快捷键【Ctrl+Break】。
2.设计时钟
Sub时间（）
[a1]=（Now（）,"
hh:
mm:
ss"
（）+TimeValue（"
00:
01"
）,"
时间"
Sub终止（）
,false
以上代码实现的效果是在单元格显示当前时间，包括时、分、秒，且每秒钟更新一次。
通过递归方式让程序每秒钟执行一次实现时钟的效果，同时再利用另一个过程随时退出递归。
当然也可以用快捷键【Ctrl+Break】。
过程实例演示
为了更好地理解Sub过程，通过两个实例来展示。
1.统计选区信息：
不带参数的Sub过程
要求：
对任意选区统计单元格个数、数值个数、非空单元格个数、空白单元格个数及选区之和。
代码如下：
Sub选区统计（）
DimmsgAsString
msg="
单元格个数：
&
Chr（10）
msg=msg&
数字个数：
（Selection）&
非空单元格：
空白单元格个数：
选区之和：
（Selection）
MsgBoxmsg,64,"
选区统计"
假设工作表中存在图所示数据，选择A1:
D9区域后利用快捷键【Alt+F8】执行“选区统计”过程，其统计结果如图所示。
图 工作表数据图 选区统计结果
2.将单元格数据转换为首字母大写：
带有参数的Sub过程
在工作表中选择任意一个带英文的单元格时，将其转换为每个单词首字母大写。
（1）插入模块1，并录入以下代码：
Sub转换（Target）
Selection
（1）=StrConv（Target,vbProperCase）
（2）双击工程资源管理器中的“Sheet1”，进入工作表代码窗口后录入代码：
Call转换（Target
（1））
（3）返回工作表“Sheet1”，单击任意单元格，如果存在英文单词，则每个单词首字母大写，否则保持不变。
如单元格中有句子“Youareonit”，那么单击该单元格后将被转换为“YouAreOnIt”。
认识Function过程
Function过程即自定义函数，在插件中应用极广。
本节介绍关于Function过程的语法及调用方法。
Function过程的特点
Function过程的功能较Sub过程的应用范围稍小，Function过程仅仅用于返回一个值或者多个数的组合，即数组，而Sub过程既可以返回值，还可以对引用的对象进行修改。
例如，引用单元格A1的值后对单元格A1设置新的格式，或者修改工作表名称等。
Function可以获取工作表名称，但无法修改工作表的名称。
Function过程可以不使用参数，类似于工作表函数Rand和Now等，但绝大部分函数是需要一个参数或者多个参数的，最多时可达255个参数。
Function的语法解析
Function的语法如下：
[Public|Private|Friend][Static]Functionname[（arglist）][Astype]
[name=expression]
[ExitFunction]
Function语句的各参数详解如表6-2所示。
表6-2 Functio
升级会员 