电子表格VBA编程计算速成14.docx

电子表格VBA编程计算速成14.docx

《电子表格VBA编程计算速成14.docx》由会员分享，可在线阅读，更多相关《电子表格VBA编程计算速成14.docx（84页珍藏版）》请在冰豆网上搜索。

电子表格VBA编程计算速成14

电子表格VBA编程计算速成

（1）

第一章 电子表格专业函数编程

1.1如何把“度.分秒”角度变成弧度？

在Excel中，自带了大量的商务、办公标准函数，唯独没有把“度.分秒”角度变成“弧度”和将“弧度”转换为“度.分秒”或“ °′″”这样的专业计算函数。

在测绘、工程、科学计算中，这经常是一个无法回避的问题。

怎么办？

打开Excel，选择“工具”→“宏”→“VisualBasic编辑器”→“帮助”→“VisualBasic语言参考”→“语句”→“A-L”→“Function”，就可以详细看到编写函数“Function”的相关语法：

[Public|Private|Friend][Static]Functionname[（arglist）][Astype]

[statements]

[name=expression]

[ExitFunction]

[statements]

[name=expression]

EndFunction

Function语句的语法包含下面部分：

部分

描述

Public

可选的。

表示所有模块的所有其它过程都可访问这个Function过程。

如果是在包含OptionPrivate的模块中使用，则这个过程在该工程外是不可使用的。

Private

可选的。

表示只有包含其声明的模块的其它过程可以访问该Function过程。

Friend

可选的。

只能在类模块中使用。

表示该Function过程在整个工程中都是可见的，但对于对象实例的控制者是不可见的。

Static

可选的。

表示在调用之间将保留Function过程的局部变量值。

Static属性对在该Function外声明的变量不会产生影响，即使过程中也使用了这些变量。

name

必需的。

Function的名称；遵循标准的变量命名约定。

arglist

可选的。

代表在调用时要传递给Function过程的参数变量列表。

多个变量应用逗号隔开。

type

可选的。

Function过程的返回值的数据类型，可以是Byte、Boolean、Integer、Long、Currency、Single、Double、Decimal（目前尚不支持）、Date、String（除定长）、Object、Variant或任何用户定义类型。

statements

可选的。

在Function过程中执行的任何语句组。

expression

可选的。

Function的返回值。

其中的arglist参数的语法以及语法各个部分如下：

[Optional][ByVal|ByRef][ParamArray]varname[（）][Astype][=defaultvalue]

部分

描述

Optional

可选的。

表示参数不是必需的。

如果使用了该选项，则arglist中的后续参数都必须是可选的，而且必须都使用Optional关键字声明。

如果使用了ParamArray，则任何参数都不能使用Optional声明。

ByVal

可选的。

表示该参数按值传递。

ByRef

可选的。

表示该参数按地址传递。

ByRef是VisualBasic的缺省选项。

ParamArray

可选的。

只用于arglist的最后一个参数，指明最后这个参数是一个Variant元素的Optional数组。

使用ParamArray关键字可以提供任意数目的参数。

ParamArray关键字不能与ByVal，ByRef，或Optional一起使用。

varname

必需的。

代表参数的变量的名称；遵循标准的变量命名约定。

type

可选的。

传递给该过程的参数的数据类型；可以是Byte、Boolean、Integer、Long、Currency、Single、Double、Decimal（目前尚不支持）、Date、String（只支持变长）、Object或Variant。

如果参数不是Optional，则也可以是用户定义类型，或对象类型。

defaultvalue

可选的。

任何常数或常数表达式。

只对于Optional参数时是合法的。

如果类型为Object，则显式缺省值只能是Nothing。

说明

如果没有使用Public、Private或Friend显式指定，则Function过程缺省为公用。

如果没有使用Static，则局部变量的值在调用之后不会保留。

Friend关键字只能在类模块中使用。

但Friend过程可以被工程的任何模块中的过程访问。

Friend过程不会在其父类的类型库中出现，且Friend过程不能被后期绑定。

小心Function过程可以是递归的；也就是说，该过程可以调用自己来完成某个特定的任务。

不过，递归可能会导致堆栈上溢。

通常Static关键字和递归的Function过程不在一起使用。

所有的可执行代码都必须属于某个过程。

不能在另外的Function、Sub或Property过程中定义Function过程。

ExitFunction语句使执行立即从一个Function过程中退出。

程序接着从调用该Function过程的语句之后的语句执行。

在Function过程的任何位置都可以有ExitFunction语句。

Function过程与Sub过程的相似之处是：

Function过程是一个可以获取参数，执行一系列语句，以及改变其参数值的独立过程，而与子过程不同的是：

当要使用该函数的返回值时，可以在表达式的右边使用Function过程，这与内部函数，诸如Sqr、Cos或Chr的使用方式一样。

在表达式中，可以通过使用函数名，并在其后用圆括号给出相应的参数列表来调用一个Function过程。

请参阅Call语句关于如何调用Function过程的详细说明。

要从函数返回一个值，只需将该值赋给函数名。

在过程的任意位置都可以出现这种赋值。

如果没有对name赋值，则过程将返回一个缺省值：

数值函数返回0，字符串函数返回一个零长度字符串（""），Variant函数则返回Empty。

如果在返回对象引用的Function过程中没有将对象引用赋给name（通过Set），则函数返回Nothing。

下面的示例说明如何给一个名为BinarySearch的函数赋返回值。

在这个示例中，将False赋给了该函数名，表示没有找到某个值。

FunctionBinarySearch（...）AsBoolean

...

   '值未找到，返回一个False值。

   Iflower>upperThen

       BinarySearch=False

       ExitFunction

   EndIf

...

EndFunction

在Function过程中使用的变量分为两类：

一类是在过程内显式声明的，另一类则不是。

在过程内显式声明的变量（使用Dim或等效方法）都是局部变量。

对于那些没有在过程中显式声明的变量，除非它们在该过程外更高级别的位置有显示地声明，否则也是局部的。

小心过程可以使用没有在过程内显式声明的变量，但只要有任何在模块级别中定义的名称与之相同，就会产生名称冲突。

如果过程中使用的未声明的变量与另一个过程，常数，或变量的名称相同，则会认为过程使用的是模块级的名称。

显式声明变量就可以避免这类冲突。

可以使用OptionExplicit语句来强制显式声明变量。

小心VisualBasic可能会重新安排数学表达式以提高内部效率。

若Function过程会改变某个数学表达式中变量的值，则应避免在此表达式中使用该函数。

Function语句示例

该示例使用Function语句来声明Function过程的名称、参数、以及构成Function过程主体的代码。

最后一个例子中使用了确定类型的、初始化的Optional参数。

'下面的用户自定义函数返回

'它的参数的平方根。

FunctionCalculateSquareRoot（NumberArgAsDouble）AsDouble

   IfNumberArg<0Then   '评估参数。

       ExitFunction   '退出调用过程。

   Else

       CalculateSquareRoot=Sqr（NumberArg）   '返回平方根。

   EndIf

EndFunction

使用ParamArray关键字可以使函数接收数目可变的参数。

在下面的定义中，FirstArg是按值传递的。

FunctionCalcSum（ByValFirstArgAsInteger,ParamArrayOtherArgs（））

DimReturnValue

'如果用如下代码调用该函数：

ReturnValue=CalcSum（4,3,2,1）

'则局部变量被赋予以下值：

FirstArg=4，

'OtherArgs

（1）=3，OtherArgs

（2）=2，等等。

'假设缺省数组下界=1。

Optional参数可以带缺省值，可以是除Variant之外的任何类型。

'如果函数的参数定义如下：

FunctionMyFunc（MyStrAsString,OptionalMyArg1As_Integer=5,OptionalMyArg2="Dolly"）

DimRetVal

'则可用如下代码调用该函数：

RetVal=MyFunc（"Hello",2,"World"）   '提供了所有3个参数。

RetVal=MyFunc（"Test",,5）   '省略了参数2。

'参数1和参数3使用了命名的参数。

RetVal=MyFunc（MyStr:

="Hello",MyArg1:

=7）

********************************************************************

请认真反复学习和理解Function语句的语法和示例。

我们的任务是编制自己的专业函数去完成特定的专业计算任务。

编制把“度.分秒”角度变成弧度的函数自然是公用的，用于进行通用三角函数的计算，用Public（公用的）声明；参数通过关键字ByVal（通过值）声明，选择按值传递，是因为按值传递，改变过程内部的参数时将不会影响到原来的变量，所以，我们的专业计算函数的编程的一般格式应为：

PublicFunctionRad（ByValangleAsDouble）AsDouble

DimAAsDouble,BAsDouble,CAsDouble,DAsDouble

DimangAsDouble,signAsInteger

ang=Abs（angle）+0.0000000000001:

sign=Sgn（angle）

A=Int（ang）:

B=（ang-A）*100#:

C=Int（B）:

D=（B-C）*100#

Rad=sign*（A+C/60#+D/3600#）*M_RAD

EndFunction

说明：

第1行：

表示定义函数名为弧度Rad；参数angle按值传递，数据类型为双精度Double，函数的返回值的数据类型也是Double；

第2～6行：

函数的语句体，其中：

第2～3行：

用Dim语句，声明函数过程变量A,B,C,D,ang的数据类型为双精度型Double，sign为整型Integer；

第4～5行：

“度.分秒”的角度值angle变换成十进制的度，其中调用了系统函数取绝对值Abs（x），取±Sgn（x），取整数Int（x）；

第6行：

函数弧度Rad返回值；其中M_RAD是一常数，可用定义常数语句进行定义：

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

第7行：

函数结束语句。

接下来，就是进行模块代码输入、调试了：

打开Excel的VB编辑器，选择“插入”→模块，在模块代码编辑窗口中输入：

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

PublicFunctionRad（ByValangleAsDouble）AsDouble

DimAAsDouble,BAsDouble,CAsDouble,DAsDouble

DimangAsDouble,signAsInteger

ang=Abs（angle）+0.0000000000001:

sign=Sgn（angle）

A=Int（ang）:

B=（ang-A）*100#:

C=Int（B）:

D=（B-C）*100#

Rad=sign*（A+C/60#+D/3600#）*M_RAD

EndFunction

上述带下划线（实际输入不带下划线）的项目（数据类型）在输入as加空格后会出现编辑提示窗口，继续输入do…就会出现double项，打空格键就上屏。

输入完毕，打开调试菜单→编译VBAProject，如果输入有误，即会弹出提示窗口，否则，函数编译成功！

即可在Excel中像使用系统函数一样使用这个自编专业函数了。

1.2 学会编写函数过程

我们成功地编写了第一个把“度.分秒”角度变成“弧度”的函数RAD（）。

这一个RAD（）函数代表了将“度.分秒”角度转变成“弧度”的整个运算过程，所以，也把函数叫做Function过程。

一个应用程序就是有许许多多子过程和Function过程有机地组织起来的。

有了这个认识，让我们再举一个用三角截柱法进行土方计算的例子，学会将三角截柱法土方计算的过程变成函数。

要测算某工程区的平场土石方量：

挖、填方量，用地形测量的方法，按一定的网度（15～30m）实测工程区内的地形变化点的三维坐标Xi、Yi、Hi，按相邻、最近距的原则组成工程区三角网，每个三角形与平场面构成正（垂直）三角截柱体，计算出工程区所有三角截柱体的体积，即挖、填方量的总和，就是工程区的平场土石方量。

在整个计算过程中，单一的正三角截柱体的体积计算过程是共同的，将它编成函数：

填方量Tfl（），挖方量Wfl（），这样，整个计算就简单了。

下面就来编写填方量函数Tfl（）这个Function过程。

首先，在编写函数代码之前，必须先搞清楚计算填方量过程的具体过程和方法：

1，计算正三角截柱体体积的基本公式：

见图7

Zsjjzv＝S·（h1+h2+h3）/3

2，工程区任一测点的标高或＞平场标高；或＜平场标高；或＝平场标高，下面是工程区任一测点的标高或＞平场标高；或＜平场标高可能出现的基本图形，此外还有任一测点的标高＝平场标高的情况。

不过，任一测点的标高＝平场标高和图7，图8可直接由公式求解；图1～图6中含四边形的图形必须分成三个正三角截柱体的特例图形（三角截柱体部分棱高＝0）分别解算。

3，此外，还要考虑连续解算（自动填充）自动终止的条件设置…。

4，填方量函数Tfl（）的代码：

参见1.4自编函数示例D-25。

1.3 VBA程序调试

在设计VBA程序过程中，特别是设计复杂过程的程序，总会或多或少存在这样那样的错误，因此，需要对每一个自编函数、事件过程的VBA程序进行严格地调试，使程序运行结果与编程的预期结果完全一致，在专业的数值计算中，要特别注意数据和除数为0的情况的调试。

程序错误大致可分为以下几类：

语法错误：

指程序的某一语句的语法出现错误，如左右引号或括号不匹配等。

当程序设计人员输入完一行语句时，VisualBaisc编辑器会自动检测语法错误，并提醒程序员错误所在。

编译错误：

指在程序的编译过程中检测出来的错误。

只检测单行语句是不能发现编译错误的。

例如，以ForEach开始的循环结构没有Next语句。

通常语法错误和编译错误是容易发现和改正的。

逻辑错误：

指思维错误——导致程序运行结果与程序员的预期结果不同的编程思路错误。

例如，想把工作簿的标题改为“MyWorkbook”，却拼写成“MyWerkbook”；应该是“>=”却写成“>”等等。

逻辑错误是最常见也是最麻烦的一类错误，程序调试的大部分时间都耗费在发现和纠正逻辑错误上。

一般可通过设置断点、单步执行、观察值的变化来发现和纠正逻辑错误。

实时错误：

指在程序运行过程中发现的错误。

有时过程中的某条语句在某些条件下能正确执行，而在另一些条件下就不能正确执行。

例如，有一条语句除数是一个变量，设计时忽略了可能出现“0”值的情况，这样，程序运行时，当这个变量等于“0”时，就会出错。

在实时错误中，有些是VisualBaisc能指出错误所在的，有些是程序员能预料的、能让VisualBaisc自动处理的。

在程序调试过程中，VBA提供了各种强有力的调试工具来查找和纠正错误。

1.使用中断模式

中断模式是指在程序执行过程中被暂时停止。

此时所有变量、属性、表达式之值都维持在最新的状态，可以进行分析、测试或是重新设置等，以便检错纠错。

进入中断模式的方法有以下几种：

单步执行：

按F8键。

单步执行类似在下一条语句上设置断点，执行当前语句并清除断点。

单步执行时，可观察每条语句的执行情况。

设置断点：

单击要设置断点的语句行的左侧边界区域；或从“调试”菜单中选“切换断点”命令。

相应语句行左侧出现一个深红色圆点，该行也以深红色背景显示。

设置临时断点（即只用一次的断点）：

单击要设置断点的语句行，从“调试”菜单中选“运行到光标处”命令。

在宏程序中需中断处加入Stop语句；在宏执行时，按Ctrl+Break键；在宏执行时，产生实时错误，程序自动进入中断模式。

符合〈监视表达式〉的条件与设定，程序进入中断模式。

2.利用各种窗口

在VBA程序设计环境中，提供了“代码”窗口、“本地”窗口、“立即”窗口、“监视”窗口、“工程”窗口、“属性”窗口和“对象浏览器”窗口等7个窗口。

从“查看”菜单中选择相应的命令可以显示出这些窗口。

其中，“代码”窗口、“本地”窗口、“立即”窗口、“监视”窗口是调试程序的得力工具。

“代码”窗口：

在中断模式下运行宏时，可以通过“代码”窗口来仔细地观察宏的执行过程，也可以通过将鼠标指针指向某变量来检测变量的值。

此时，在“代码”窗口内，准备执行的语句以黄色高亮方式显示，在其左边有一个黄色箭头。

“本地”窗口：

在运行一个宏时，可以通过“本地”窗口观察宏程序所使用的变量、表达式、对象的变化来寻找程序错误；也可在“本地”窗口直接改变属性值，这与用VBA语句改变属性具有同样的效果；还可以通过“本地”窗口快速浏览某个对象的所有属性。

它清楚地显示了哪些是包含值的属性（在值栏中有值的属性）和哪些是包含对象引用的属性（在旁边有加号的属性）。

通过单击属性的值看它能否改变，能很容易地发现具有值的属性是不是只读的。

“立即”窗口：

在程序调试中，有时需要给某一变量指定一个新值，或输出显示某些变量的值，对此使用“立即”窗口最为方便。

在“立即”窗口，可以输入任何语句并立即执行它。

例如，要查看活动工作簿中所有工作表的名称，则可在“立即”窗口中键入语句ForEachxinWorkskeets:

?

x.Name:

Nextx。

通常，在“立即”窗口中，一条语句占一行，当多条语句排列在一行上时，要用冒号将其分开。

并且在“立即”窗口中大都使用简短、无意义的变量名，这并不影响语句的执行。

“监视”窗口：

在执行宏前，或进入中断模式后，打开“监视”窗口，添加监视表达式以便观察关键的测试变量或表达式随宏执行的变化情况。

在“监视”窗口中添加监视表达式的方法是：

使用鼠标选中监视表达式，再单击“调试”菜单上的“添加监视”或“快速监视”命令。

要移去监视的变量，只需单击该变量，再按Del键。

3.错误处理

当发生实时错误时，VBA一般会显示一个错误信息对话框，进入中断模式。

对于这样的错误，在宏设计中可以采用程序加以控制、监测错误，这种方法称为“错误捕获”。

它可检测出错误并控制程序的流向。

设置忽略错误（容错处理）

语句1：

OnErrorResumeNext

表示：

若发生错误，则忽略它，跳到下一条语句继续执行。

语句2：

OnErrorGoTo行号（或标号）

表示：

若运行有错，则跳到标号指定位置，转去执行错误处理例程。

获取错误信息

在宏执行过程中，可以通过专用调试对象Err的属性值来了解是否发生实时错误以及发生了什么样的错误。

若Err.Number值为0，则表明没有产生错误，反之有错误。

下面通过两个例子说明如何使用错误捕获技术。

例1，假设有一学生档案工作簿StudentBook，其中已有若干学生档案工作表Student1

Student2…以及其它一些工作表。

现需要建立一个新的Student学生档案工作表，但又不想删除已有的Student工作表，如同Excel增加新工作表一样，只是将工作表名称的后缀加1。

则实现这一功能的VBA宏程序如下所示：

SubMakeNextStudent（）

DimSheetAsWorksheet

DimBaseAsString

DimSuffixAsInteger

SetSheet=WorkSheets.Add

Base=“Student”

Suffix=1

OnErrorResumeNext

Sheet.Name=Base&Suffix

DoUntilErr.number=0

Err.Clear

Suffix=Suffix+1

Sheet.Name=Base&Suffix

Loop

EndSub

MakeNextStudent宏的执行过程是：

先建立一个新工作表，再试着以Student为基本名、1为后缀构成的名称给新工作表命名。

OnErrorResumeNext语句的作用是：

若已有同名工作表存在，Excel不能给新工作表命名时，