电子表格计算编程.docx
《电子表格计算编程.docx》由会员分享,可在线阅读,更多相关《电子表格计算编程.docx(41页珍藏版)》请在冰豆网上搜索。
电子表格计算编程
电子表格VBA编程计算速成(4)
已有8172次阅读2007-10-416:
56|个人分类:
科普园地
第四章电子表格计算编程
学会了自己编制函数,掌握了单元格和工作表的基本编程代码和设置,就可以对您所需要的复杂专业项目进行编程自动化计算了。
如果您对编程是一无所知的初学者,也许前面的自编函数还没有完全看懂,这不要紧,下面介绍VB编程基本语法之后,自然就会清楚了;至于代码,那都是系统命令,知道它的含义和用途,动脑筋灵活用好它就可以了。
4.1电子表格VB编程基本语法
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,则循环中的语句将永远不会运行。
在ChkLastWhile过程中,在条件变成False之前循环中的语句只执行一次。
SubChkFirstWhile()
counter=0
myNum=20
DoWhilemyNum>10
myNum=myNum-1
counter=counter+1
Loop
MsgBox"Theloopmade"&counter&"repetitions."
EndSub
SubChkLastWhile()
counter=0
myNum=9
Do
myNum=myNum-1
counter=counter+1
LoopWhilemyNum>10
MsgBox"Theloopmade"&counter&"repetitions."
EndSub
②直到条件变成True才重复语句
当使用Until关键字去检查Do...L