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