VBA 代码的健壮性及代码调试

2019-06-03 16:30:31

一、 什么是健壮性

1.1 除法引发的血案

在讨论代码的健壮性之前，我们开看这样一段简短的代码，在Excel VBA中，求一个单元格对另一个单元格的“除法”，当然可以简单地写成如下的VBA代码形式：

Function MyDevide(A As Double, B As Double) As Double

MyDevide=A/B

End Function

那么，这个程序有没有啥问题呢？除法当然是A/B，这本身没有错。可是，如果我们用这样定义的“除法”运算，就会发现一个潜在的漏洞，即当第二个数字为0的时候，也就是说一旦我们将这个算法运用到一个空单元格的时候，就会发生运算错误。即出现#Value的结果。

也许你会说，对啊，0不能做除数，这一点我们大家都清楚。可是作为程序开发者来说，应该对所有可能出现的情况做充分的判断，上面那一段代码出现这种情况的究其原因，是这个程序的代码不够健壮。

1.2 健壮性

所谓的健壮性，就是程序在用户任意的输入情况下，都可以得到稳定并且正确的输出。当然，用一些手段限制用户输入的情况除外，因为这种情况已经在某种程度上拒绝了程序中错误的出现。以上面这个例子，用户在使用这个函数时，并不知道（或者不期望其知道）第二个参数（除数）不能为0，所以，他们用0作为除数的行为是应该可以得到谅解的。

通过上述的讨论，我们有必要将这样一段简单的程序加以修改，以保证运行的正确性。

Function MyDevide(A As Double, B As Double)

If B=0 Then MyDevide=”Error, DevBy 0”: Exit Function

MyDevide=A/B

End Function

这一次的运行结果，如果用户选择了空单元格（除数为0）则显示“Error! DevBy 0”，也就是说告诉用户你参数错了，错的原因是，DevBy 0（用0做除数）

二、如何解决程序中隐含的Bug

2.1 什么是Bug

Bug就是在程序的运行中，没有得到程序预想的结果。这些Bug大部分是由于用户没有按照希望的形式或者方法进行数据输入。当然，我们不能希望用户像编写程序的程序员那样熟知每一个函数或者过程的具体细节，程序编写的精华也在于将函数或者过程的具体细节进行“封装”，只给用户提供一个“入口”。由此可见，在程序编写的过程中，程序中存在的Bug是一个避免不了，又无法逃避的问题。例如，尽管编程者通过提示让用户输入一个数字，但无果不加以额外的手段，谁也保不齐用户会输入一个字符，或者让用户按某一个顺序进行某项输入，但谁也不能保证用户会100%地按照正确的顺序进行输入。

2.2 如何解决程序中的Bug

第一个方法是写一个类似Manu之类的手册，并且通过某种方式强制让用户进行阅读。这种方法见于早期，由于软件功能有限，加之编写水平不高，所以出现了这种”使用手册“之类的东西。但是这种基于用户的行为是程序中不推荐使用的。

第二种方法，对用户的每一种可能输入情况进行判断，如果输入不符合规则，则提示用户“输入错误”或者让用户再次输入一遍。例如对于“性别”这个变量，只允许用户输入“男”或者“女”，此时可以加入一个判断，如果用户输入了“男”或“女”以外的字符，则显示输入错误，并且让用户重新再出入一遍。这种方法的好处是，可以保证程序按照希望的方法正确运行，但是，这样做的不好的地方在于用户体验会比较差，这样的程序甚至可以作为一个教材，逼着用户成为下一个程序员。

第三种方法，按照一般人的习惯对输入的情况进行编写。例如，一般人都会用”>”表示“大于“，而不是用诸如”GT“这样的英文字母。但是这种方法也存在一定的差异，例如某些人习惯用”！=“表示”不等于“。另外一些人则习惯用”<>“表示”不等于“。

第四种方法，在程序中列出所有的可能出现情况，并一一加以解决，当然大多数情况下，作为程序的编写者，不大可能预见所有的情况，也只好将程序做到尽量完善。

第五种方法，遇到Bug时使用错误处理语句，通过错误处理语句，可以高效的、快速地构建一个健壮性很好的程序。VBA提供了很多种处理异常（Error）的方法，例如：On Error Resume Next，On Error Goto 0等。

三、对出现的错误进行操作

1. 使用On Error Goto语句

可以用 On Error Goto+行标的方法对程序中可能出现的错误进行操作，例如，在平方根的运算中，当被开平方的数为负数时，我们可以让它显示成“虚数”的结果。

Function MySqr(A As Double)

On Error Goto L1

MySqr=Sqr(A)

Exit Function

L1:

A=-A

MySqr=Sqr(A) & “i”

End Function

当然对于这个简单的例子，也可以先判断A是否大于0，然后分别调用两个不同的函数。下面我们再来看一下On Error Goto语句的用法

例如，求一元一次方程ax+b=0的解，这里，如果a=0则需要对方程做额外处理。

Function MyF(A As Double, B As Double)

On Error GoTo L1

MyF = -B / A

Exit Function

L1:

If B = 0 Then

MyF = "Any"

Else

MyF = "Inf"

End If

End Function

这一段程序的作用：当方程系数A不为0时，方程的解是-B/A，当A等于0的时候，如果B=0则显示“Any”表示任意一个数，如果B≠0，则显示“Inf”表示结果为无穷大。

2.使用On Error Resume Next 语句

On Error Resume Next 语句可以使系统自动忽略掉运行中出现的错误，而继续向下运行。

我们以修改一个区域的颜色为例说明On Error Resume Next的用法。例如，让用户输入一个RGB颜色，然后把Sheet1的A1单元格背景填充成这种颜色。一般地，RGB颜色由3个0~255之间的整数构成，但是如果用户只输入了两个或者一个怎么办？当然，另外的数值只好认为是0了。

Sub MyColor()

On Error Resume Next

Dim r As Integer,g As Integer,b As Integer

Dim StrL

StrL=InputBox("输入RGB颜色")

r=Val(Split(StrL,",")(0))

g=Val(Split(StrL,",")(1))

b=Val(Split(StrL,",")(2))

Sheet1.Range("A1").Interior.Color=RGB(r,g,b)

End Sub

这一段的作用，用户输入三个以逗号分隔的数字（0~255之间整数），然后作为RGB的值给A1单元格着色，由于用户可能会输入完整的信息。例如255,0,0，也有可呢个输入不完整的信息，例如255,0，无论怎么用户如何输入，总之第一个逗号前是R的数值，两个逗号中间是G的数值，最后一个数为B的数值。

VBA 代码调试

一、 代码调试的阶段性

任何工程都不是一蹴而就的事情，特别是例如像VBA的大段代码，在开发运用过程中少不了需要对代码进行多次的调试。

代码的调试过程就是从调试中寻找代码的漏洞，以进一步完善工程。初略来说，代码的调试大致可以分为3个阶段：

第一个阶段是写代码的作者本人对代码的调试，调试的目的是发现代码中的一些错误，让代码能够“如期运行”。根据作者个人经验，代码中的错误主要有如下的形式：

（1）书写错误，例如之前定义了一个变量MyRow，以后再次用到这个变量的时候错误地拼写成了MyRoe，当然计算机不会知道w键和e键比较接近，从而可能导致的MyRow错拼写成MyRoe，从而把MyRoe当成另一个新的变量。杜绝错误拼写的方法有：使用VBA强制声明语句，即Option Explicit，如果这样做，VBA遇到这种问题时就会发出警告，告诉用户这个变量没有声明，或者将变量名称大小写区别，在定义变量时，定义成MyRow的形式，如果以后用到时，直接键入小写myrow，如果书写正确，则VBA会自动将小写的myrow变成大写的MyRow形式，如果拼写错误，则还是保持小写的形式。（作者通常使用第二种方法）。

（2）缺少Set关键字，在给一个例如Range等类类型赋值的时候，往往会缺失这个Set关键字。对于这类问题，作者并没有什么好的方法，往往这个问题会比较困扰大家一点。这里只能告诉大家的是，如果出现运行时错误‘91’，基本上都是缺少Set关键字。

（3）在While循环或者Until循环中缺少例如i=i+1的语句，While循环是根据条件是否满足而进行的循环，与For循环不同的是，For循环中，循环变量会自行增加，而While循环中，我们需要添加例如i=i+1的变量增加语句。如果在While循环中忘记增加这样的语句，将导致Excel陷入死循环的状态。

（4）If缺少End If或者Select Case缺少End Select。与While语句中缺少循环变量变化的语句一样，初学者经常犯的错误也包括If缺少End If或者Select Case缺少End Select，这类错误在编译的时候VBA会自动停止，报告错误。

（5）逻辑错误，这类错误比较隐蔽，但往往也会出现比较严重的问题。例如Not(A>10 And B>12)等价于Not A>10 Or Not B>12,而不是Not A>10 And Not B>12。这样的问题往往能让用户纠结一段时间。

（6）变量重复定义，例如之前已经定义了一个变量为N，后面又在同一段中重新定义了一次N。有时候这个错误并不是用户有意为之，例如，在没有强制声明的时候，用户使用了一个变量N，后面又有Dim N As Integer这样的语句，就会被系统当成重复定义。

第二个阶段是将写好的代码给从事相关工作的同事进行调试，这种调试也叫做“内部调试”。内部调试用来发现代码中不合理的设计部分或者说发现代码的Bug部分。例如，作为程序的作者，其熟悉程序的每一个细节，可以将这个程序的功能发挥到极致，但是同样一个程序，不同的用户可能就会出现完全不同的结果。例如本书的作者早期开发了一个比较简单的程序，自己使用完全没有问题，提交给同事使用时，由于同事忘记输入了一个关键的参数导致程序整体崩溃。于是后来作者将这个参数的输入方式进行了修改，即用户如果不输入确定的数值时，弹出对话框询问是否采用内部指定的数值，这个数值有可能并不是用户需要的数值，但是总比完全依赖用户行为要好得多。根据个人经验，这一阶段可能会发生如下的问题：

（1）试用者重复工作，大多数时候，重复的确是一件比较好的事情。但涉及到文件保存的时候就不见得是这么一回事了，因为第二次保存的时候，可能会对文件的内容发生了一些意想不到的修改，例如用户不小心点错了一个键什么的（例如点击到了删除），然后再次保存。出现这种情况的原因是，第一次保存的时候，用户其实并没有那么“放心”，以为数据没有保存成功。所以，需要在用于保存成功以后通过一个对话框或者别的什么形式告知用户数据已经保存。

（2）测试者输入了一个不大恰当的数据，大多数时候，我们需要尊重测试者的智商。出现这种问题的根源，往往在于开发者没有给出详细的提示或者没有做出错误数据的处理办法，就像上述的例子，作者一开始的提示比较小，不大醒目，从而造成了用户的误操作。经过改版以后，增加了用户误操作时的默认处理方式。

（3）测试者使用的Excel版本问题，有时候，开发者所使用的Excel版本与测试者使用的版本存在一些版本的区别，会使得API的一些函数无法运行的情况。这种情况下，需要开发者使用最为基础的一些命令来完成其需要的操作。大家不用担心书中的代码，因为书中的所有代码都是通过Excel VBA最为基础最为底层的操作来完成的，不涉及到API函数和接口。

第三个阶段：进行外部测试，即将已经做好的VBA程序发布到一定的社交平台或者网站上，让一些用户来试用。这个阶段中可以发现一些更加影藏的程序问题，例如，在内部使用时，往往温度一栏中填写的是37℃，在网站的测试中，这个温度可能为任意的数值。这个阶段就比较接近于软件发布的阶段了。用户可以就界面的设计、程序的功能以及提示等部分提出意见。

二、 代码调试的方法

VBA代码调试可以采用很多种方法，综述如下：

（1）点击“运行“按钮，如果代码没有问题则直接出现结果，如果出现问题则会弹出错误，点击”调试“后会高亮显示错误代码处。

（2）单步执行：点击单步执行，以后每一次按下F8键，就可以看到每一步（每一个语句）执行后每个变量的变化。

（3）执行到光标处：执行到光标处可以具体分析每一个”小片段“是否存在问题。

（4）逐过程执行：逐过程执行可以单一执行一个过程。

（5）添加断点和切换断点（删除断点），可以用鼠标点击确定的行来标注断点，断点的作用是使得程序在断点处暂停，这样可以进一步分析前面的语句都做了哪些操作。

关于用Excel做数据库的个人想法

Access数据库虽然比较强大，但是这些数据库语言还得慢慢地去琢磨，而且，Excel调用Access数据还存在一个明显的难点。最近我们实验室这边希望弄一个数据库，可以方便查询每一次的实验数据，以方便项目总结或者年终总结。

鄙人不才，利用一点点Excel VBA的知识写了一个工作的模板。最基本设想是，用一台计算机做服务器，把每一个批次的实验数据做成Txt的列表格式储存在服务器上。用Excel读取Txt文本中的内容（因Txt列表在列表的时候是用列表符Tab作为分隔符的，所以再以Tab作为分隔符一个一个读取出来就好）

然后另外做一个文件，文件后改为index（表示为所有实验批次数据的"引索"，即简单记录了每一个批次的批次信息）。模块工作时，先将这个index文件“加载”到Excel工作簿的一个Sheet里面（index文件也是按照Tab键分割的），然后第二个sheet里特定的单元格引用这个sheet的特定的列。（就是储存每一个批次的批号的列）。在第二个sheet的Change事件里面添加一段代码，打开该批次的文件（因之前的每个批次实验都是以批号为文件名的），然后再把文件中的内容加载过来。

需要添加实验批次时，就用另一个Excel工作簿，每一次将新一个批次的实验数据按照Tab分割符写到一个Txt里面，保存。然后打开index文件，写入该批次的信息。

需要修改时，可以用上述两个端口的任意一个端口，将新数据写入原来的文件，并且将index文件的相应位置修改一下。

这样做的原因：（1）我们之前的工作习惯是，每一个批次结束后，并不按照实验的项目进行分类，而是按照时间（月份）分类。这样做总结的时候，就要历边每一个月份的文件夹。用这种方式，即保证可以按照时间分类，也按照项目分类（上述两个模块可以复制）。（2）另外保存一份Txt文件总是比保存一份Excel文件要小的多。（3）可以用一个读取窗口（界面）读取多个txt的内容，并在一个Excel工作簿中对多个批次的实验结果进行比较。（4）可以按照index文件中的内容“按条件搜索”找到相应的批次并显示出来。

现在也就会这么多了，个人还想加入一个命令行之类的东西，可以按照用户自己的命令进行工作，而不再是按照我之前写好的程序工作。