电子表格VBA编程计算速成4.docx
《电子表格VBA编程计算速成4.docx》由会员分享，可在线阅读，更多相关《电子表格VBA编程计算速成4.docx（35页珍藏版）》请在冰豆网上搜索。
电子表格VBA编程计算速成4
电子表格VBA编程计算速成（4）
第四章 电子表格计算编程
学会了自己编制函数，掌握了单元格和工作表的基本编程代码和设置，就可以对您所需要的复杂专业项目进行编程自动化计算了。
如果您对编程是一无所知的初学者，也许前面的自编函数还没有完全看懂，这不要紧，下面介绍VB编程基本语法之后，自然就会清楚了；至于代码，那都是系统命令，知道它的含义和用途，动脑筋灵活用好它就可以了。
4.1 电子表格VB编程基本语法
ＶＢ编程计算是什么？
简单说就是通过命名的变量、常量来存储用于计算的数据，存储和传递计算的中间或最后的结果；通过一系列的代码语句来处理数据，输出数据，从而完成计算任务。
下面简明分述之：
4.1.1 变量和常量
一，标识符（变量、常量、数据类型、过程、函数等）命名规则：
⑴字母开头；
⑵不超过255个字符；
⑶只包含字母，数字，下划线；
⑷不能用系统的保留字（42个关键字），即系统命令、语句、函数的固定标识符。
如：
As，ByVai，Me，Set，For，To等
VB中不区分字母大小写。
在命名时最好采用好记、又好理解的命名方式：
一般用英语单词或其缩写加数字，下划线；国人也可以用汉语简拼加数字，下划线来命名，首字母一般大写。
二，声明变量
1.Dim语句：
用于创建过程级变量，声明变量的数据类型并分配存储空间。
可以在一个语句中声明几个变量，但必须将每一个变量的数据类型包含进来。
如下语句中，变量intX、intY、与intZ被声明为Integer类型。
DimintXAsInteger,intYAsInteger,intZAsInteger
而下面的语句中，变量intX与intY被声明为（缺省）Variant类型；只有intZ被声明为Integer类型。
DimintX,intY,intZAsInteger
2.Public语句：
声明公共模块级别变量。
公有变量可用于工程中的任何过程。
PublicstrNameAsString
如果公有变量是声明于标准模块或是类模块中，则它也可以被任何引用到此公有变量所属工程的工程中使用。
3.Private语句：
声明私有的模块级别变量。
私有变量只可使用于同一模块中的过程。
PrivateMyNameAsString
注意，在模块级别中使用Dim语句与使用Private语句是相同的。
不过使用Private语句可以更容易的读取和解释代码。
4.Static语句：
声明静态变量。
使用Static语句取代Dim语句时，所声明的变量在每次调用时仍保留它原先的值。
三，声明常数：
可用如下格式声明公用常数，数据类型也可用变量声明字符%、&、!
、#代表Integer（整型%）、Long（长整型&）、Single（单精度型!
）、Double（双精度型#）。
PublicConstM_SEC#=206264.8 '1弧度=206264.8″
PublicConstM_DEG#=57.2957795130823 '1弧度=57.2957795130823°
PublicConstM_RAD#=1.74532925199433E-02 '1度=1.74532925199433E-02弧度
PublicConstM_PI#=3.14159265358979 'π=3.14159265358979
可以在一个语句中声明数个常数,但必须将每一个常数的数据类型包含进来。
如下面的语句中，常数conAge和conWage被声明为Integer类型。
ConstconAge%=34,conWageAsCurrency=35000
四，声明数组：
数组的声明方式和其它的变量是一样的。
DimMyArray（10,10）AsInteger
第一个参数代表的是行；而第二个参数代表的是列。
与其它变量的声明一样，除非指定一个数据类型给数组，否则声明数组中元素的数据类型为Variant。
为了尽可能使写的代码简洁明了，则要明确声明的数组为某一种数据类型而非Variant。
4.1.2 数据类型
1.Byte（字节型），1字节，0到255
2.Boolean（布尔型），2字节，True或False
3.Integer（整型%），2字节，-32,767到32,767
4.Long（长整型&），4字节，-2,147,483,648到2,147,483,647
5.Currency（货币型），8字节，-922,337,203,685,477.5808到922,337,203,685,477.5807
6.Single（单精度型!
），4字节，-3.402823E+38到-1.401298E-45；
1.401298E-45到3.402823E+38
7.Double（双精度型#），8字节，-1.79769313486232E+308到-4.94065645841247E-324；4.94065645841247E-324到1.79769313486232E+308
8.Date（日期型），8字节，100年1月1日到9999年12月31日
9.String（变长字符串$），10字节，0到大约20亿
10.String*length（定长字符串），字符串长度
11.Object（对象，4字节），任何对象引用
12.Variant（变体型，数字），16字节，任何数字值
13.Variant（变体型，数字16字节；字符22字节，任何字符串值
数据类型缺省为Variant。
4.1.3 运算符与表达式
一，算术运算符：
+（加，也用于字符串拼接），-（减，也作为负号），*（乘），/（浮点数除），\（整数除），Mod（取模），^（乘方、开方），&（字符串拼接）
算术运算符的优先级为：
^，-（负号），*、/、\、Mod，+、-，&。
在运算表达式中，有括号，先算括号内，有多层括号，内层括号优先。
二，关系运算符：
<（小于）,<=（小于等于）,>（大于）,>=（大于等于）,=（等于）,<>（不等于）
关系运算符用来对两个表达式的值进行比较，结果是一个逻辑值真（True）或假（False）。
三，逻辑运算符：
运算符
含义
使用
意义
And
逻辑与
变量1And变量2
两个量均为True，才返回True
Or
逻辑非
变量1Or变量2
只要有一个量为true，就返回True
Xor
逻辑与或
变量1Xor变量2
两个量一个True，一个False，才返回True
Not
逻辑非
Not变量1
简单地把True变成False，把False变成True
Eqv
逻辑等于
变量1Eqv变量2
两个量同时为True或两个量同时为False，才返回True
Lmp
逻辑蕴含
变量1Lmp变量2
只要不是变量1为True且变量2为False，就返回True
四，表达式的执行顺序：
1.方式运算
2.算术运算
3.关系运算
4.逻辑运算
5.赋值运算
4.1.4 流程控制语句
一，顺序结构语句
1.赋值语句：
把一个表达式的值赋给一个变量或者控件对象的一个属性。
语句格式为：
变量名＝表达式 或者 对象名.属性＝表达式
示例1：
指定InputBox函数的返回值给变量yourName。
SubQuestion（）
DimyourNameAsString
yourName=InputBox（"Whatisyourname?
"）
MsgBox"Yournameis"&yourName
EndSub
示例2：
设置对于活动单元格Font对象的Bold属性
ActiveCell.Font.Bold=True
2.输入输出语句：
①InputBox函数：
在一对话框中显示提示，等待用户输入正文或按下按钮，并返回包含文本框内容的字符串。
其语法：
InputBox（prompt[,title][,default][,xpos][,ypos][,helpfile,context]）
Prompt必需的。
对话框中的提示文本。
prompt的最大长度大约是1024个字符，由所用字符的宽度决定。
如果prompt包含多个行，则可在各行之间用回车符（Chr（13））、换行符（Chr（10））或回车换行符的组合（Chr（13）&Chr（10））来分隔。
Title可选的。
显示对话框标题栏中的文本。
如果省略title，则把应用程序名放入标题栏中。
Default可选的。
显示文本框中缺省的字符串表达式。
如果省略default，则文本框为空。
Xpos可选的。
指定对话框的左边与屏幕左边的水平距离，数值表达式，成对出现。
如果省略xpos，则对话框会在水平方向居中。
Ypos可选的。
指定对话框的上边与屏幕上边的距离，数值表达式，成对出现。
如果省略ypos，则对话框被放置在屏幕垂直方向距下边大约三分之一的位置。
Helpfile可选的。
识别帮助文件，用该文件为对话框提供上下文相关的帮助，字符串表达式。
如果已提供helpfile，则也必须提供context。
Context可选的。
指定给某个帮助主题的帮助上下文编号，数值表达式。
如果已提供context，则也必须要提供helpfile。
说明
如果同时提供了helpfile与context，用户可以按F1（Windows）orHELP（Macintosh）来查看与context相应的帮助主题。
某些主应用程序，例如，MicrosoftExcel，会在对话框中自动添加一个Help按钮。
如果用户单击OK或按下ENTER，则InputBox函数返回文本框中的内容。
如果用户单击Cancel，则此函数返回一个长度为零的字符串（""）。
注意：
如果还要指定第一个命名参数以外的参数，则必须在表达式中使用InputBox。
如果要省略某些位置参数，则必须加入相应的逗号分界符。
下面是获取InputBox函数的返回值的赋值语句
Xzan%=InputBox（"0,清空；1,→算表面积；2.写入公式－10；3,计算－100；4,计算－150；5,计算－200；6,计算－250；7,计算－300；8,计算－350；9.计算－400；10,删除数据名…",”请选择项目”）
②MsgBox函数：
在对话框中显示消息，等待用户单击按钮，并返回一个Integer告诉用户单击哪一个按钮。
MsgBox（prompt[,buttons][,title][,helpfile,context]）
Prompt必需的。
显示在对话框中的消息文本。
prompt的最大长度大约为1024个字符，由所用字符的宽度决定。
如果prompt的内容超过一行，则可以在每一行之间用回车符（Chr（13））、换行符（Chr（10））或是回车与换行符的组合（Chr（13）&Chr（10））将各行分隔开来。
Buttons可选的。
数值表达式是值的总和，指定显示按钮的数目及形式，使用的图标样式，缺省按钮是什么以及消息框的强制回应等。
如果省略，则buttons的缺省值为0。
Title可选的。
显示对话框标题栏中的文本。
如果省略title，则将应用程序名放在标题栏中。
Helpfile可选的。
字符串表达式，识别用来向对话框提供上下文相关帮助的帮助文件。
如果提供了helpfile，则也必须提供context。
Context可选的。
识别帮助文件，用该文件为对话框提供上下文相关的帮助，字符串表达式。
如果已提供helpfile，则也必须提供context。
buttons参数常用设置值：
常数
值
描述
vbOKOnly
0
只显示OK按钮。
VbOKCancel
1
显示OK及Cancel按钮。
VbAbortRetryIgnore
2
显示Abort、Retry及Ignore按钮。
VbYesNoCancel
3
显示Yes、No及Cancel按钮。
VbYesNo
4
显示Yes及No按钮。
VbRetryCancel
5
显示Retry及Cancel按钮。
VbCritical
16
显示CriticalMessage图标。
VbQuestion
32
显示WarningQuery图标。
VbExclamation
48
显示WarningMessage图标。
VbInformation
64
显示InformationMessage图标。
vbDefaultButton1
0
第一个按钮是缺省值。
vbDefaultButton2
256
第二个按钮是缺省值。
vbDefaultButton3
512
第三个按钮是缺省值。
vbDefaultButton4
768
第四个按钮是缺省值。
按钮返回值常数：
常数
值
描述
vbOK
1
OK
vbCancel
2
Cancel
vbAbort
3
Abort
vbRetry
4
Retry
vbIgnore
5
Ignore
vbYes
6
Yes
vbNo
7
No
说明
在提供了helpfile与context的时候，用户可以按F1（Windows）orHELP（Macintosh）来查看与context相应的帮助主题。
像MicrosoftExcel这样一些主应用程序也会在对话框中自动添加一个Help按钮。
如果对话框显示Cancel按钮，则按下ESC键与单击Cancel按钮的效果相同。
如果对话框中有Help按钮，则对话框中提供有上下文相关的帮助。
但是，直到其它按钮中有一个被单击之前，都不会返回任何值。
注意：
如果还要指定第一个命名参数以外的参数，则必须在表达式中使用MsgBox。
为了省略某些位置参数，必须加入相应的逗号分界符。
二，分支结构语句
当要求有选择地执行指定操作时，采用条件分支程序结构。
1.If...Then...Else语句
根据给定条件的值，使用If...Then...Else语句运行指定的语句或一个语句块。
If...Then...Else语句可根据需要嵌套多级。
然而，为了可读性可能会使用SelectCase语句而不使用多级嵌套的If...Then...Else语句。
①如果条件为True则运行语句
当条件为True时，若只要执行一个语句，则可以使用单行的If...Then...Else语法。
下列的示例显示了单行语法，省略了Else关键字：
SubFixDate（）
myDate=#2/13/95#
IfmyDateEndSub
为了运行多行代码，必须使用多行的语法。
而此语法包含EndIf语句，如同下面示例所示：
SubAlertUser（valueasLong）
Ifvalue=0Then
AlertLabel.ForeColor="Red"
AlertLabel.Font.Bold=True
AlertLabel.Font.Italic=True
EndIf
EndSub
②如果条件为True，运行某些语句；条件为False，运行其它的语句
使用If...Then...Else语句可以定义两个可执行的语句块：
其中一个块会在条件为True时执行；而另一个块会在条件为False时执行。
SubAlertUser（valueasLong）
Ifvalue=0Then
AlertLabel.ForeColor=vbRed
AlertLabel.Font.Bold=True
AlertLabel.Font.Italic=True
Else
AlertLabel.Forecolor=vbBlack
AlertLabel.Font.Bold=False
AlertLabel.Font.Italic=False
EndIf
EndSub
③如果第一个条件为False时，测试第二个条件
如果第一个条件为False，则可以在If...Then...Else语句中加上ElseIf语句来测试第二个条件。
例如，下列的函数过程根据工作分类来计算奖金。
如果所有If和ElseIf语句中条件都是False，则会运行跟在Else语句之后的语句。
FunctionBonus（performance,salary）
Ifperformance=1Then
Bonus=salary*0.1
ElseIfperformance=2Then
Bonus=salary*0.09
ElseIfperformance=3Then
Bonus=salary*0.07
Else
Bonus=0
EndIf
EndFunction
2.SelectCase语句
从一个条件设置中选择某一分支。
下面是一个专业计算过程，根据输入框的输入数字计算相应的语句体。
PrivateSubbmjjsButton1_Click（）
DimxzanAsString
xzan=InputBox（"0,清空；1,→地形点计算；2.写入公式－10；3,计算－100",”请选择项目”）
Ifxzan=""ThenGoTojs
SelectCasexzan
Case"0"
语句体（清空）
Case"1"
语句体（→地形点计算）
Case"2"
语句体（写入公式－10）
Case"3"
语句体（计算－100）
EndSelect
js:
EndSub
请注意：
每个Case语句可以包含一个以上的值，一个值的范围，或是一个值的组合以及比较运算符。
如果SelectCase语句与Case语句的任何值相匹配，则可选的CaseElse语句运行。
FunctionBonus（performance,salary）
SelectCaseperformance
Case1
Bonus=salary*0.1
Case2,3
Bonus=salary*0.09
Case4To6
Bonus=salary*0.07
CaseIs>8
Bonus=100
CaseElse
Bonus=0
EndSelect
EndFunction
3.无条件转移语句Gotobz（标签）
当程序执行到Gotobz语句时，会无条件转到bz：
标志处，并继续往下运行。
很好利用这一特性可解决多条件分支和循环的问题。
如：
PublicHz_Button1（）
DimHxasInteger
DimQh,ZhAsDouble
Sheet2.Activate
'………………
Hx=3
sh2:
IfCells（Hx,1）=0ThenGoTojs2
IfCells（Hx,1）>=QhAndCells（Hx,1）<=ZhThen
Cells（Hx,4）.Select
WithSelection.Interior
.ColorIndex=36 '淡黄＝36
.Pattern=xlSolid
EndWith
'………………
Hx=Hx+1
GoTosh2
ElseIfCells（Hx,1）ZhThen
Hx=Hx+1
GoTosh2
EndIf
js2:
'………………
EndSub
4.跳转子程序语句GoSub...Return
当程序执行到GoSubbz语句时，会无条件转到标志bz开始的子程序，当碰到第一个Return语句时，程序就会返回到紧接在刚刚执行的GoSub语句之后的语句继续执行。
利用这一特性，我们可以将较长的相同过程代码，创建分开的过程，并使用GoSub...Return来调用，可以使程序更具结构化。
须注意的是，GoSub和与之相应的Return语句必须放在同一个过程中。
如：
SubGosubDemo（）
DimNum
'请求用户输入一个数字。
Num=InputBox（"Enterapositivenumbertobedividedby2."）
'如果用户输入一个正整型，则使用子程序。
IfNum>0ThenGoSubzcx
Debug.PrintNum
ExitSub '使用Exit命令来避免错误发生。
zcx：
Num=Num/2 '将数除以2。
Return ''将控制返回GoSub之后的语句。
EndSub
三，循环结构语句
当要求重复执行一组操作时，采用循环程序结构语句。
其中有些循环重复执行语句直到条件为False；而有些循环重复执行语句直到条件为True。
也有某些循环执行一指定次数的语句或是集合中的每一个对象。
1.Do...Loop语句
可以使用Do...Loop语句去运行语句的块，而它所用掉的时间是不确定的。
当条件为True或直到条件变成True时，此语句会一直重复。
①直到条件为True时重复语句
当使用While关键字去检查Do...Loop语句中的条件时，可以有两种方法。
可以在进入循环之前检查条件式，也可以在循环至少运行一次之后才检查条件式。
在下面的ChkFirstWhile过程中，在进入循环之前检查条件。
如果将myNum的值由20替换成9，则循环中的语句将永远不会
升级会员 