EXCEL VBA与数据统计

2019-06-06

第一章 序

第一节 VBA是什么

1.1 VB以及计算机编程的好处

Microsoft Visual Basic（简称VB）是微软公司开发的面对对象的一种编程语言，因其语言结构清晰，语法易于学习，成为大多数软件开发者学习的首选语言。

相信在大学阶段，各位或多或少地接触过计算机语言和编程，那么，通过计算机语言编程的方法处理实验中所得到的数据有哪些优势或者好处呢？

（1）实现对实验数据的收集整理

在实验中，我们得到许多实验数据，这些数据对于以后的数据分析和实验设计都是至关重要的。在对于某项目实验数据收集时，往往可能由于实验过去的时间太过长久，或者实验数据的采集和汇总不够及时，造成整理时遗漏数据（数据遗漏）或者整理时找不到数据（数据丢失）的情况。

采用计算机对数据自动收集和整理，首先可以减轻在数据整理时耗时耗力的工作量，其次，可以做到历遍所有数据，杜绝数据遗漏的可能性。

（2）实现对实验数据的自动化分析

对于实验得到的所有数据，进行科学的分析和准确的解读，是科学地进行实验设计的必要前提，每一次实验设计需要根据之前的实验结果进行相应的调整，这样才能够确保实验工作有序地进行。

采用计算机对实验数据自动化分析，可以极大程度地减少实验设计者在工作中的一些运算，保证运算的迅速性和准确性，为实验设计提供科学的、可靠的保障。

（3）实现对实验数据的回顾，完成人机互动共同分析

对于实验数据的数理统计分析，计算机毕竟只能够完成最为基本的、设定好的计算和操作，对于一些有异常情况的数据或者需要人工经验的数据，计算机则显示出能力不足的特点。

采用计算机对数据进行收集整理以及自动分析后，还可以轻易的调出某一个具体的数据，对于这个具体的数据，可以用人工的方法进一步进行分析，这样做到人机互动，进一步提高分析的准确性。

1.2 VBA以及Microsoft Office

Microsoft Visual Basic for Applications（简称VBA）是由Microsoft公司开发，在其桌面应用程序中执行通用自动化任务（OLE）的编程语言。在Microsoft Office软件中（Microsoft Word、Excel、PowerPoint等），嵌入了该编程语言模块，用户可以利用Microsoft 嵌入的VBA模块实现对相关软件的操作。那么，使用VBA与传统的使用Office方式相比，有哪些优点呢？

（1）使用VBA程序实现自动录入的功能

使用VBA可以实现快速录入。如果录入的内容具有某种规律性或者模块性，可以用VBA的方式实现快速录入。例如，在Excel文档的B列输入0-11，共计12个数字，然后反复输入10遍。

传统录入：在B1格输入0，B2格输入1…… 然后一直到B12格，输入11。然后把这10个格子复制一下，在B13格选择“粘贴”…… 一直粘贴9遍。

这样输入有两个问题：第一，录入所花费的时间较长；第二，录入时，容易造成录入错误或者多复制了一遍或者少复制一遍的错误。

VBA方式录入：进入Excel以后，按下Alt+F11快捷键，打开VBE窗口（下一章会介绍VBE）点击“插入”→“模块”，然后敲入下列代码：

Sub InsertB()

Dim RngB As Range, i As Integer, j As Integer

Set RngB=Range(“B1:B12”)

For i =1 to 10

For j=0 to 11

RngB(j+1)=j

Next j

Set RngB=RngB.Offset(RngB.Rows.Count,0)

Next i

End Sub

然后按下运行按钮

（2）运用VBA实现自动计算的功能

使用VBA实现快速运算将是本书的主题，我们知道，在Excel单元格中可以实现各种各样的计算，Excel中有许多运算的“函数”，使用这些函数就可以对表格进行计算。然而，Excel中的函数还是具有其有限性，有时不能支持我们进行“随心所欲”的计算。而VBA恰恰提供给我们自己写函数的模块，通过VBA，可以自己“随心所欲”的写自己想要的函数。例如：随机在Excel中选择一些单元格，计算单元格中的所有数字的总和。

传统方法：Excel并没有提供这样的函数，Sum函数只能计算某一个连续的区域的数字总和，所以，采用Excel的Sum函数时，往往需要用好几次Sum函数才能最终算出这个和是多少。

这样的方法的问题是：比较耽误时间，而且可能造成重复输入和遗漏，结果一个区域的数据可能重复计算了几遍或者有些区域被遗漏了。

VBA解决方案：进入Excel以后，按下Alt+F11快捷键，打开VBE窗口，点击“插入”→“模块”，然后敲入下列代码：

Function SumAny( ParamArray Group()) As Double

Dim i , j

For Each i In Group

For Each j In I

SumAny=SumAny+j

Next j

Next i

End Function

然后在Excel的任意单元格输入“=SumAny()“ 这个自定义的SumAny函数将和Excel自带的函数Sum()一样运行，不同的是，这个函数中可以自主地选择若干个不连续的区域。

（3）利用VBA实现自动提取数据的功能

在进行数据分析过程中，往往我们需要对数据中的一部分进行特殊的处理，此时，就需要将符合这一特殊条件的数据提取出来。例如：在表格1（Sheet1）中，提取B列中内容为“S3”的数据，放在另一个表格（Sheet2）中。

传统做法：从Sheet1的第一行开始，一行一行的读数据，遇到B列中内容为“S3”，就把这一列复制到Sheet2中。

这样做的问题是：第一，工作时有可能会有遗漏或者重复，特别是当Sheet1中的行数特别多的时候。第二，有可能看花眼，把不符合条件的复制了过来。

VBA解决方案：进入Excel以后，按下Alt+F11快捷键，打开VBE窗口，点击“插入”→“模块”，然后敲入下列代码：

Sub Abstract()

Dim i As Integer, j As Integer, RngB As Range

Set RngB=Sheet1.Range(“B:B”)

i = 1 : j=1

While RngB(i) <>””

If RngB(i) = “S3” Then Sheet1.Rows(i).Copy Sheet2.Rows(j) : j = j + 1

i = i + 1

Wend

End Sub

然后按下运行按钮

第二节 VBA可以做哪些事情

2.1 所有在Excel中可以做的事情，VBA都可以做。

（1）在某一个单元格中输入具体的数据

最常见的Excel操作是，在某一个单元格中输入具体的数据，在VBA中，有3种引用单元格的方法。

第一：通过Range()引用。在VBA中，Range(“A1”)就代表Excel中的A1单元格，所以，将A1单元格中输入数据“1.5”的命令就是：Range(“A1”)=1.5

第二，采用Cells()引用。在VBA中，允许通过Cells(i,j)的方式引用单元格，Cells(i,j)表示工作表中第i行，第j列的单元格。因此，Cells(2,2)就表示B2单元格，给B2单元格赋值为2.7的命令就是Cells(2,2)=2.7

第三，允许采用[A1]方式引用单元格，中括号里面的就是单元格的名称。

（2）套用方式引用单元格

采用套用方式引用单元格，是一种比较高级的、灵活的解决方案。在VBA中，Range（）除了可以引用一个单元格，还可以引用一个单元格区域，例如：Range(“A1:B5”)就表示A1到B5单元格的一个区域。

套用方式引用单元格，就是在一个区域中，再引用某一个单元格，例如：

Range(“A3:C5”).Cells(2,2)表示在A3到C5区域中，第2行第2列的单元格，即B4单元格。

（3）使用公式

在VBA中，同样可以使用Excel公式对单元格进行赋值和计算。例如：Range(“A5”).Formula=”=Sum(A1:A4)”表示将A1单元格到A4单元格中的数求和，然后放在A5单元格中。

（4）使用相对位置方式引用单元格或者区域

采用相对位置引用区域或者单元格将会更加灵活地应用工作表区域，相对位置用Offset（）实现，例如：Range(“A1:C4”).Offset(1,1)表示引用A1到C4区域，向右移动一个单元格，向下移动一个单元格以后的区域，即B2到D5区域。

（5）改变单元格背景颜色、字体颜色、字体等。

（6）绘图

（7）单元格操作（合并、拆分等）

2.2 自己开发属于自己的函数或者过程

之前提到，在计算若干个不连续单元格的数字之和的时候，采用VBA编写函数的过程得以实现。下面再举一个Excel本身不具备的功能，在Excel表的第一行第一列的位置显示现在的日期和时间。

Sub T()

Cells(1,1)=Now

End Sub

第三节 如何学习VBA

3.1 培养兴趣

兴趣是做好一件工作的基础，如果各位能够通过上述的一些例子看到VBA的快捷，或者对数据整理分析有一定的兴趣，那么就已经具备了学习VBA的最基础条件了。

3.2 多看多想

读者可以通过阅读本书籍，特别是其中的一些代码，想一想为什么这么做？还有没有别的简便的编写方法来提高自己抽象思维的能力。学习任何一门计算机编程语言，抽象思维的能力非常重要，如何把一个任务抽丝剥茧地分成若干个小任务，又如何通过一行一行的命令来实现这些小任务，只有通过抽象的思维和仔细地思考才可以领会其中的奥秘。

3.3 实践、实践、再实践

学习VBA（或任何一门计算机语言）最好的办法是实践，VBA因为不用安装其他的任何软件，实践起来比较容易，只需要安装了Microsoft Office软件，就自带VBA模块，所以相对于其他语言，实践起来比较容易。

在任何实践中，大家需要记住一条：所有的错误都是自己的不小心或者逻辑问题产生的，不要去怀疑电脑有问题，因为电脑会忠实执行你的代码！所以，一旦产生错误，就只能是自己在写代码的时候有些地方没有预料到或者实际结果与预想不完全一致导致。

3.4 数理统计学

为了实现本书的“传递思想”的任务，本书在书写相应VBA代码的时候，也会对数理统计学的相关知识点做有关的介绍（将在第7章实战代码中做相关的介绍）

第二章 VBA代码保存和运行环境

第一节VBE环境

2.1 如何编写、存放和运行代码

2.1.1 VBE简介

也许有的读者从第一章的内容中已经知晓，在运用VBA进行工作时，需要在Excel表格中进入VBE。那么，什么是VBE呢？VBE全写为Visual Basic Editor，是微软公司为Visual Basic程序编写和运行而开发的一个集成环境，在这个环境中，可以对VBA代码进行编辑，包括代码键入、复制、修改、等。

2.1.2如何进入VBE界面

在Excel中进入VBE的方法是：打开Excel表格，然后按下Alt键和F11键，将弹出VBE窗口，图2.1显示了VEB默认窗口。

图2.1 VBE窗口

VBE界面简介

2.1.2.1 VBE界面由菜单、工具栏、帮助窗口、资源管理器、属性窗口组成。

2.1.2.2 菜单窗口：大部分VBA程序的运行、编译和调试均可在菜单栏中实现。

2.1.2.3 资源管理器：可以查看、打开、关闭目前VBA项目所设计的工作簿、工作表等。

2.1.2.4 属性窗口：显示和修改目前选定对象的属性，在界面设计一章中，将专门讲解利用属性窗口美化界面的方法。

2.2 模块和类模块

2.2 模块：

模块就是为了一系列相同或者相似，彼此之间有逻辑联系的问题而设定的一个整体的“过程”可以人为地给这个“过程”添加一些功能，在VBE中进行编辑时，一般情况下，需要插入一个或者多个模块。通常情况下，将一段具有某种功能的VBA程序编写到一个特定的模块中，就可以编译运行该程序了。

2.2.1 如何插入模块

进入VBE界面以后，点击菜单栏中的“插入”，然后再点击“模块”，就可以插入一个模块了，插入模块以后的VBE界面如下：

图2.2 插入模块后的VBE

插入模块后，VBE自动建立模块代码窗口（当前鼠标闪烁位置）在这个代码窗口中输入特定的VBA代码，就可以提运行了。

2.2.2 如何在模块中编写代码

插入模块后就可以在其中编写代码，编写代码一般可以采用直接写入或者复制的方式进行

2.3 类模块

与模块不同，类模块并不是为解决某一个具体的问题而设计的，而是为了某一类问题而设计的某种“通用”的模板，或者独立于模块而存在，为模块所调用的一些基层的功能或者函数。类模块这一部分将在第9章进行详细的讲解。

第二节 过程和函数

VBA的前身是宏（Macro），关于宏的相关知识，有兴趣的读者可以自己寻找相关的材料进行了解，这里只需要记住一点，VBA中定义的过程的过程名就是宏名称，即可。

2.1过程

前面已经提到了过程一词。在VBA中，过程可以理解为“为了达到某一个具体工作目的而编写的一小段具有完整功能的VBA代码“

与插入模块和类模块相似，VBA中需要用相应的代码来定义一个过程，VBA中定义过程的格式为：

[Private | Public | Friend] [Static] Sub {Name} ([Arglist])

[Statements]

[Exit Sub]

[Statements]

End Sub

下面一一讲解这些最基础单元的意义以及用法

(1)总原则：在上述格式中，中括号”[]”中的部分可以这个部分为可选项，即在VBA过程中这个部分可以省略。带有竖线”|”的部分表示三个选项中可以选择其中的一个（最多选择一个选项，这3个选项，即Private、Public、Friend将在后面的章节中做详细介绍）。花括号“{}”里面的部分表示，这一部分内容必不可少，但是可以用任意的字母或者任意的字母、数字、下划线组合。

(2) [Private | Public | Friend] 这一部分指定这个过程可以运用的范围，当使用Private时，表示这个“过程”是“私有”的，即只有当前的窗体或者同一模块中口语调用这个过程，Public时，表示“公共”的，即这个过程可以被任何窗体或者模块调用，选择Friend时，表示过程为“友员”过程，所有的“友员”模块或者窗体可以调用。

(3) [Static] 这个选项为变量的生命周期变量，每一次运行该过程时，如果过程中的变量想使用上一次运行过程后的变量的数值，则可以添加该选项。

(4) Sub 申明过程的关键词，这个不可以缺少，也不可以修改。

(5) {Name} 过程名称，这一部分用户可以自己使用任何符合规定的名称，名称可以使用英文字母、数字或者下划线，以及其任意组合。但是不可以使用空格、引号、感叹号等符号。

(6) ([Arglist]) 这一部分指明过程所需要的参数，过程可以没有参数，但是不可以省略两边的小括号，即“（）”。所谓的参数，就是一个过程在运行的时候，需要外界向这个过程提供的一些信息，这些信息独立于过程而存在，又是过程执行中所需要的。

(7) [Statements] 过程执行的语句段，根据过程的需要，这些语句段由用户自行编写，可以只有一段，也可以有许多段。

(8) [Exit Sub] 在执行语句段时，根据特殊的需要的特别的情况，提前结束整个过程。

（9）End Sub 表示过程结束，即整个过程执行完成。

2.2 函数

与过程相同的是，函数也是为了实现某一个具体的任务而编写的一个具有完整功能的语句段（代码）。与过程不同的是，（1）过程和函数都可以调用函数，函数却不可以调用过程。（2）在Excel单元格中可以使用函数，却不可以直接使用过程。(3)函数具有具体的返回值，过程没有返回值。

VBA中，对于函数的定义格式为：

[Private | Public | Friend] [Static] Function {Name} ([Arglist]) [As Type]

[Statements]

[Exit Function]

[Statements]

End Function

(1)总则：参见1.1.4.1过程，这里的原则和过程定义时一致。

(2) [Private | Public | Friend] 指定该函数可以被调用的范围，意义与过程定义一致。

(3) [Static] 指定该函数调用时变量是否采用Static形式，意义与过程一致。

(4) Function 定义函数的关键字，不可缺少，不可更改。

(5) {Name} 函数名称，用户可以任意命名，命名原则同过程名的命名。

(6) ([Arglist]) 函数的参数，这一部分可以没有，也可以有很多种形式，后面将这一部分的语法详细解释。

(7) [As Type] 设定函数的返回值的类型。这一部分内容，请读者在了解了第三章变量以后自行领悟。

(8) [Statements] 函数具体功能的实现，用户根据自己具体需要进行编写。

(9) [Exit Function] 根据特殊情况，提前结束函数的执行过程。

(10) End Function 函数执行完毕

(11) [Arglist] 部分语法：[Optional] [ByVal | ByRef] [ParamArray] {VarName} [()] [As Type] [=defaultvalue]

这一部分语法解释如下：

(12) [Optional] 表示这个参数为可选参数，函数在执行时，一般情况下可以不用这个参数，该参数可以起到改变函数功能的作用，此时可以选择用Optional命令。

(13) [ByVal | ByRef] 函数参数的传递方式，ByVal为数值传递，ByRef为地址传递，两者的区别请阅读函数相关的章节。

(14) [ParamArray] 函数的参数个数不确定，用参数数组的形式参与运算。

(15) {VarName} 参数名称，即函数需要的参数的名称，这一部分命名原则和函数名称命名一致。

(16) [As Type] 参数的类型，即参数的变量类型。这一部分也将在变量一章中进行解释。

(17) [=defaultvalue] 指定参数的默认数值，即忽略参数时或者参数可选时，这个参数的默认数值。

第三节 运行第一个简单代码

终于可以运行第一个简单代码了！我们来看一看运行代码的具体过程是怎样的。

2.3.1 进入Excel VBE界面

打开Excel表格，按下Alt+F11快捷键，进入VBE界面

2.3.2 插入模块

点击VBE界面菜单栏中的“插入”按钮，然后选择“模块”

2.3.3 敲入代码

敲入“sub Show()”，并按下回车键。注意到，当按下回车后，VBE自动将这一段改为：

Sub Show()

End Sub

并且光标自动停止在Sub和End Sub之间，从这里，我们可以看出，VBE界面比我们想象中更加的有好强大，其可以自己对用户输入的部分进行修改

2.3.4 继续录入完整的代码

Sub Show()

MsgBox Now

End Sub

2.3.5 运行代码

在录入完整代码后，可以尝试运行一下代码，在VBE中，运行代码的方法有

（1）在VBE界面中直接按下运行按钮运行按钮为一个绿色的三角形

图 2.3 运行按钮

（2）在VBE界面中按下F5快捷键

（3）关闭VBE，回到Excel，按下Alt+F8快捷键，然后点击“执行”

（4）在Excel中插入按钮控件，然后将控件与编写的宏名称相关联，插入控件以及关联宏的方法将在第10章界面设计中详细讲述。

第四节 注释以及Excel的保存形式

2.4.1 注释

在VBA代码中，允许插入一段或者若干段解释代码作用的文字，这样的文字叫做注释，给代码加注释的作用，好比如学生做的课堂笔记，对于以后代码的维护起到关键性的作用，在学习编写VBA程序的过程中，给VBA程序编写注释是一个很好的习惯，由于在本书中，已经在一段程度的后面通过文字的方法对程序进行了说明，因此程序中不再加入注释的部分。在VBA编辑环境中，加入注释的方法是在需要注释的开头加上一个单引号“’”或者使用关键字Rem，再加上注释的内用。注释的内用并不参与命令和程序的编译，只是给程序的编写者或者相关人员阅读程序提供方便。

在使用单引号的方式加入注释时，单引号可以出现在这一行的任意位置，单引号后面的内用（即与单引号后面同一行的内容）会被认为是注释的内容。使用Rem关键字加入注释时，Rem关键字只可以出现在一行的首位置，这一行的所有内容都会被当作注释内容。注释的部分内容会被VBE环境表示为绿色。

图2.4 注释

2.4.2 Excel保存的格式

在一段VBA编写完成后，需要对Excel表格进行保存，在保存格式中，一般选择xls格式，这是因为：

（1）在Excel 2007之前的版本，保存格式为xls。保存问xls时，可以兼顾对VBA代码的保存以及兼容低版本Excel。

（2）保存为xlsx格式时，不能兼顾对VBA代码和宏的保存，为了保存VBA代码以及宏，Excel 2007之后的版本需要保存成xlsm的格式。而低版本的Excel（Excel 2003版本）不能打开xlsm格式文件。

第三章 变量

第一节 计算机中对信息的保存形式

3.1.1进制

在小学阶段，我们就知道，在书写一个数字的时候，按照从高位到低位的原则书写，高位上的1代表相邻低位上的10. 我们不妨再回忆一下这一部分的内容，这将对于我们理解进制有着非常大的帮助：

例如156这个数字，我们读作”一百五十六”。而当我们写下这个数字的时候，为啥就默认它等于1个一百，5个十，以及6个1组成的呢？这是因为，一般情况下，我们采用10进制来记录一个数字，所谓10进制，就是”逢10进1”，如果我们进一步把这个156用10进制的方式拆开，就是1*102+5*101+6*100.这里可以看到，”个位”上的数字自动认为是100,”十位”上的数字自动认为是101，”百位”上的数字自动认为是102.

然而，在现实生活中，并不是所有的进制都是10进制，最典型的例子是时间，在时间的表示中，1天等于24小时，这个是24进制，也就是”逢24进1”；1小时等于60分钟，1分钟等于60秒，这里是60进制，即”逢60进1”；1年等于12个月，这是12进制……

那么现在有一个问题，2天13小时12分20秒等于多少秒，这个时间由如何表示？对于这个时间我们是否可以表示为2:13:12:20 ? 当然可以，这里每两个”小结”之间的进制不再是10进制了。那么，不同的进制有什么不一样呢？我们知道，在10进制中，所有的数字为0~9，共计9个数字，所有的数都是由这9个数组成。而在过去的年代，我们采用”十六两称”的时候，即一斤等于16两。如何表示1斤12两这样的数呢？此时，由于12两不等于1斤，也不到当时的一斤，所以我们这样表示当然是不对的，正确的表示方式是：C两。在16进制中，我们引入A、B、C、D、E、F这五个字母表示大于等于10，且没有达到16的5个数字，即A代表10，B代表11……，那么相对于10进制，16进制就需要有16个”数字”构成，分别是从0~F。在音乐中，这个进制为8，即逢8进1,（只有7个音符，第8个音符与第一个音符唱名相同）。在8进制中，10等于10进制中的8.因此，8进制数需要用0~7，共计8个”数字”构成。

从上面的论述可以看到，任何一个进制的数，其采用的数字的个数等于进制单位。如何表示不同进行的数字呢？一般情况下，10进制的数字按照原来的方式写即可，其他进制的数字，在书写的时候，数字两边加上小括号，并在右下角写上进制的方法，用于相互区分，例如，16进制中的数字12，表示为(12)16. 8进制中的数字27，表示为(27)8.二进制中的数字101，表示为(101)2.

3.1.2 进制直接的相互换算

（1）其他进制转换为10进制

其他进制转化为10进制的方法比较简单，只需要”安位置”计算即可。我们可以参考10进制的计算原则，例如137=1*102+3*101+7*100，计算一下16进制的12C：

(12C)16=1*162+2*161+12*160=300.

同样的原理，可可以计算一下8进制下的45. (45)8=4*8+5=37.

(2) 10进制数转换为其他进制

将10进制数转化为其他进制数的方法是：用10进制数除以需要转换成为的进制，余数作为”各位”，然后商再除以进制，余数作为”十位”，然后依次类推，直到是这个十进制数除以进制后商等于0，将最后的余数作为最高位。

例如：将10进制数的25转换为2进制数，则进行如下运算：

第一轮：25除以2，商为12，余数为1.于是，第一位是1，商不等于0，进行第二轮。

第二轮：12除以2，商为6，余数为0,于是，第二位是0，商不等于0，进行第三轮。

第三轮：6除以2，商为3，余数是0,于是，第三位是0，商不等于0，进行第四轮。

第四轮：3除以2，商为1，余数是1,于是，第四位是1，商不等于0，进行第五轮。

第五轮：1除以2，商为0，余数是1,于是，第五位是1，商等于0，结束

于是。25化为2进制数便是(11001)2. (即上面的余数按照从低位到高位的顺序写)。

3.1.3计算机对信息的保存形式

计算机中，所有的信息都是以2进制的方式进行保存的。为什么计算机采用2进制保存信息？简单来说，2进制保存信息时，所需要的原件种类最少，原件结构最为简单！不妨想象一下，假设计算机以10进制来保存信息，那么势必需要一种能够表示10个不同状态的原件。而在电子原件中，让一个原件可以有10种不同的状态，远远比让一个原件只有两种不同的状态复杂！

在日常生活中，最常见到的2进制是开关，开关对于电路来说最为简单，假设开的状态我们表示为1，而关的状态表示为0，那么，我们只需要4个开关，就可以表示16进制中从0~F，这16个不同的数。

第一个开关第二个开关第三个开关第四个开关表示的数字

00000

00011

00102

00113

01004

01015

01106

01117

10008

10019

1010A

1011B

1100C

1101D

1110E

1111F

3.1.4 原码和补码

计算机中，最基本的储存单位是字节Byte，一个字节由8个上面说到的”元件”组成。一个字节可以表示的数字范围为0~255（在上面的例子中，4个开关最多表示16种不同的情况，即16个数字）以计算机中最常见的整型为例，一个整形数据是有2个Byte组成，读者可以思考一下其表示的数字范围是多少？

原码：在计算机中，一个数字转化为2进制后，就是这个数字在计算机中的”原码”。例如，数字5在计算机中的原码为：0000000000000101。

补码：在原码的基础上，将原码中的0转换为1,1转换为0，最后再加上1。例如，数字5在计算机中的补码为：1111111111111011。

在计算机中，正数以原码的形式表示，而负数以补码的形式表示，上面的例子中，5，在计算机中为0000000000000101，而-5为1111111111111011。

负数储存为补码的好处：当负数表示为补码以后，减法运算可以简化为加法运算！例如，5-5=0，在计算机中，其运算为5+(-5)，即0000000000000101+1111111111111011。我们可以看到，因为2进制中为”逢2进1”因此，0+0=0，1+0=1，0+1=1，1+1=10，这个0000000000000101+1111111111111011运算后，等于10000000000000000.而计算机中，一个整形最多保存16位，所以，最前面的那个1将会被”丢失”掉，最后这16个位置上的数正好全部归零。

3.1.5 ASCII码表

以上我们探讨了计算机对于整数的保存和运算形式，接下来我们来探讨一下计算机对于字母和符号的保存。与数字的保存形式一致，计算机也是采用2进制的方式保存字母和符号，计算机中，将特定的符号按照ASCII字母表的方式同一个byte（0-255）中的数相互对应。所谓ASCALL表，其全称是American Standard Code For Information Interchange，即美国信息交换标准代码。

ASCII数值字符ASCII数值字符ASCII数值字符ASCII数值字符

0NUL32Space64@96·

1SOH33！65A97a

2STX34“66B98b

3ETX35#67C99c

4EOT36$68D100d

5ENQ37%69E101e

6ACK38&70F102f

7BEL39‘71G103g

8BS40(72H104h

9HT41)73I105i

10LF42*74J106j

11VT43+75K107k

12FF44,76L108l

13CR45-77M109m

14SO46.78N110n

15SI47/79O111o

16DLE48080P112p

17DC149181Q113q

18DC250282R114r

19DC351383S115s

20DC452484T116t

21NAK53585U117u

22SYN54686V118v

23TB55787W119w

24CAN56888X120x

25EM57989Y121y

26SUB58:90Z122z

27ESC59;91[123{

28FS60<92/124|

29GS61=93]125}

30RS62>94^126`

31US63?95_127DEL

第二节 VBA数据类型

3.2.1 数据类型和VBA的数据类型

数据在计算机中的储存方式成为数据类型，在上面一节中，曾经中提到，在计算机中，一个整形数据的储存形式，是2个字节范围。数据只有以一定的数据类型储存起来才具有意义。VBA中支持的数据类型以及该种数据类型表示的数据范围如下。

数据类型储存空间能够表示的范围

Byte1个字节0~255

Boolean2个字节True或者False

Integer2个字节-32768~32767

Long4个字节-2147483648~2147483647

Single4个字节-3.4E38~-1.4E-45，1.4E-45~3.4E38

Double8个字节-1.79E308~-4.8E-324,4.8E-324~1.79E308

Currency8个字节-9.22E18~9.22E18

Decimal14个字节±7.9E20范围内整数，28位小数：±7.9，最小非零1E-25

Date8个字节100年1月1日~9999年12月31日

String（变长）10个字节+0~2E9字符

String（定长）字符串长度1~6E4字符

Variant（数字）16个字节任何数字，最大可以到Double范围

Variant（字符）22个字节+与String（变长）相同

Type（用户自定义）Type中字节+等于Type中所有基础数据类型表示范围

对象型与对象有关等于对象最大表示范围

3.2.2 VBA数据类型

1用于储存逻辑型数据和整数型数据的类型

有Byte型、Boolean型、Integer型、Long型。Byte型，用于储存一个介于0~255之间的整数。Boolean型，用于储存逻辑数值，仅仅可以储存两个”逻辑数值”，即”True”或者”False”。Integer型和Long型，分别用于储存整形数据，Integer型的储存空间为2个字符，Long的储存空间为8个字符。

一个Integer型的数据，可以储存的最大数据是32767，如果让一个32767的整形数据再加上1，将会发生著名的”溢出错误”，即加完之后等于-32768。为什么会发生如此的情况呢？

在计算机中，最高位上的数表示这个数据的”符号”，最高位上等于0时，表示这个数是正数，按照”原码”的原则储存的；最高位是1时，表示这个数是负数，按照”补码”的原则储存的。（详见3.1.4原码和补码），所以，在计算机中进行32767+1运算时会发生如下的情况：32767=(0111111111111111)2,32767+1=(0111111111111111)2+(1)2=(1000000000000000)2,而这个数，正好是32768的补码，所以，等于-32768。

2 用于储存小数的数据类型

有Single型、Double型、Currency型、Decimal型，其中，Single型为4个字节，Double型为8个字节，Currency型为”货币类型”，可以储存没有小数的数据（整数），也可以储存有小数的数据，并且根据小数点的位置，其表示范围是可以变化的，Decimal型也具有这种储存方式，其精确程度在所有数据型类型中最高。

这里要说明的是，这些类型除了同样会有”溢出错误”以外，对于一个小数，计算机并不能完全储存小数点后的无限多的位置，所以，在计算机中，保存的小数位有限的情况下，后面的小数位将自动被”四舍五入”忽略。其中一个例子是，一个Single型的数据，等于1/3，然后让这个Single型数据再乘以3，结果将会是0.9999999999……。

3 用于保存日期的数据类型

在VBA中，有一个用于保存日期的数据类型，即Data型，Data型的数据可以直接想减，得到的数字为两个日期直接相隔的天数。

4 用于保存字符串的数据类型

String（变长）型：当字符串的长度不确定时，用String（变长）型来储存，用这种类型来保存字符串时，会自动在字符串前端加上字符串的长度，此时，占有的储存空间大小为10个字符+字符串长度。

String（定长）型：当字符串长度确定时，用String（定长）型来存储，这种类型保存字符串，保存长度等于字符串长度。

注意的一点是，当字符串中有汉字时，一个汉字或者一个汉字符号等于两个字符！（详见国标2312，GB2312）

5 变体型

当储存类型暂时不能确定时，VBA给用户提供了一种可以”通用”的类型，即Variant，使用这种类型数据时，用户不必担心所需要储存的数据是那种类型，而是编译器会自动根据实际数据的类型进行转换，然后再储存。

6 Type型

VBA允许用户自定义数据类型，自定义的数据类型由上述基础的数据类型构成，这种数据类型叫做结构体，我们将在数值与结构体一章中详细讲解。

用户自定义的结构体的储存空间大小，等于结构体中所有成员的储存空间大小之和。

7 对象类型

实际上，可以把对象类型看成一个结构体类型，不同的是，这种结构体是系统自带的，即VBA内部所支持的一个”类”。

第三节 变量和运算

3.3.1 变量

变量就是计算机中被命名的一个储存位置。不妨把变量想象成一个容器，在这个容器中，储存的是程序运算过程中可以改变的”数据”或者”对象”。也可以把变量想象成中学课本中的”未知数”，这些”未知数”可以改变，也可以参与求解运算。

3.3.2 变量的声明

在使用一个变量前，需要在计算机中进行声明该变量，即向系统说明，现在有这么一个变量。VBA声明变量的命令是: Dim 变量名 As 类型、Public 变量名 As 类型、Private 变量名 As 类型、Static 变量名As 类型。这里，只讲解第一种声明形式，剩下的声明形式在变量使用范围中讲解。

在命令”Dim 变量名 As 变量类型”中，变量名可以用英文字母、数字、下划线以及其组合，类型可以用上述类型中的任何一个类型。

例如：

Dim A As Integer

这一条命令即声明一个名字叫做”A”的整形变量。

Dim B As String(5)

这一条命令声明了一个名字叫做”B”的字符串变量，这个B的长度为5个字符。

Dim C As String

这一条命令声明了一个名字叫做’C”的字符串变量。C长度不确定。

Dim A As Integer, B As String

这一条命令一次性声明了两个变量，第一个是名字为A的整形变量，第二个是名字为B的字符串型变量。

对于数字和字符串的规定：VBA中规定，所有双引号中的内容，均被当作字符串进制处理，而没有双引号的内容当作数字、变量或者函数、过程处理。对于变量名称大小写的规定：VBA中，变量名称不区分大小写，即在VBA中，整形变量A和a，VBA认为是一个变量，VBE在第二次遇到相同的变量时，会自动把变量名称修改为与第一次的名称形式相同。

对于隐式声明和显示声明的规定：在VBA中，可以不用声明一个变量而直接使用，这时，编译器自动将第一次使用的该变量（隐式地）声明为变体型。变体型数据将会造成程序运行速度减慢，为了杜绝这一现象，可以让VBA强制采用显示声明的办法，即在程序开头的位置加上Option Explicit。加上该命令后，必须先显示声明变量才可以使用该变量。

3.3.3 变量的运算

既然变量是一个”数据”那么，就有同数据一样的参与运算的功能，那么，对变量进行的任何操作都可以称为变量的”运算”，我们可以根据在变量中储存的东西是数据或者对象，将变量分为数值变量和对象变量。下面将讲解变量的运算。

1. 赋值运算

变量最基础的运算便是”赋值运算”，也就是把一个数或者对象通过”赋值”的方法”复制”到变量中。在VBA中，赋值运算的方法有两种：Let 变量=表达式 或者Set 变量=表达式。Let用于数值变量的赋值，Set用于对对象变量的赋值。在”Let 变量=表达式”中，Let可以省略 所以，对于数值变量的赋值就简化成了”变量=表达式”

在上面这个式子中，变量一定要位于”=“符号的左边，而”=“符号的右边可以是任意的数学运算、判断运算或者字符串运算等。这个”=“不再是数学中的”等于”的意思，而是将右边的数字、字符或者逻辑值进行运算，然后把右边的结果复制给左边的变量。例如

a=a+1

这一个命令是，把原来变量a的数值加上1，然后再赋值给变量a，命令结束后，a的数值在原先基础上加了1.

2. 数学运算

VBA中支持许多数学运算，相关的数学运算有：

（1）四则运算：加法”+”,减法”-”，乘法”*”，除法”/”，整除””，取余数”Mod”运算。例如：”5+2”，”7-4”，”4/3” ,”3”，”17 Mod 3” 等。下面我们着重讲解除法和整除运算。

除法运算，即计算两个数的除后的结果，这里，如果运算后赋值给一个Single或者Double型将不会产生任何问题，但是，如果赋值给一个Integer或者Long型，将会产生一个小问题，请尝试一下下面的程序：

Sub Test()

Dim A As Integer, B As Single

A=1/2

B=1/2

MsgBox(“A=“& A &” B=“ & B)

End Sub

将显示”A=1 B=.5。这里，B=.5的输出我们暂且不去管它，这个输出等价于B=0.5，也就是说，B的运算时完全正确的，但是A为什么处于2以后任然等于1呢？

这是因为，我们在把例如0.5这样的小时赋值给一个整数时，VBA系统自动给我们”四舍五入”了！

整除运算，在进行整除运算时，计算机自动给出除后的整数部分，而忽略小数部分，还是上面的程序，现在修改一下：

Sub Test2()

Dim A As Integer, B As Single

A=1

B=1

MsgBox(“A=“ & A & “ B=“ & B)

End Sub

再次运行，发现此时结果是”A=0 B=0”这是因为，在进行整除运算时，计算机只取计算结果的整数部分，所以把一个整数赋值给一个小数或者整数，结果还是这个整数！

求余数运算，Mod运算符可以求除法运算中的”余数”，这个运算可以判断一个数是否为偶数（奇数）（偶数对2的余数等于0）。表达式 a Mod b表示求a对b的余数。

Sub Test3（）

Dim A As Integer

A=17 Mod 3

MsgBox（a）

End Sub

程序将输出17对3的余数，即2.

（2）取整运算：取整运算的结果是把一个小数取成整数。取整运算有两种，即Int() 和Fix（）。这两者是有区别的，Int()运算是求不大于这个小数的整数，当小数大于0时，将返回小数的整数部分，当小数小于0时，将返回这个小数的整数部分减去1，而Fix()作用是返回小数的整数部分，无论小数大于0或者小于0，例如：Int(8.7)等于8，Fix(3.6)=3,而Int(-7.2)=-8，Fix(-7.2)=-7

（3）函数运算：VBA支持的函数运算有：幂运算”^”,表示求幂，例如3^2表示求3的2次方幂。Exp()表示求底数为e的幂。对数运算：Log() 表示求以e为底的对数。三角函数:Sin()、Cos()、Tan()表示求弧度情况下的正弦、余弦、正切（注意括号内的数的单位为弧度）。随机函数：Rnd()，表示生成一个0~1之间的随机数。

3.逻辑运算

所谓逻辑运算，就是得到逻辑值True（真）或者逻辑值False（假）的运算，逻辑运算中，有逻辑值运算和逻辑值之间的运算。逻辑值运算就是得到一个命题为真或者为假的运算

VBA的逻辑值运算有：”>“,”<“,”<>“,”>=“,”<=“以及”=“

（1）大于运算 “>“ 判断左边是否大于右边，例如3>5，结果为False请尝试以下程序：

Sub Logisic（）

Dim a As Boolean

a=3>5

Msgbox（a）

End Sub

正如所预料，得到结果”False”

（2）小于运算”<“判断左边的是否小于右边。

（3）不等于运算”<>“ 判断左右两边是否不等。

（4）等于运算”=“ 判断左右两边的是否相等。

（5）大于等于运算”>=“判断左边是否大于或等于右边。

（6）小于等于运算”<=“判断左边是否小于或等于右边。

VBA中的逻辑数值之间的运算有：And、Or、Not、Eqv、Imp、Xor。

(1) And运算：计算两个逻辑数值的”逻辑且”，只有当And两边的两个逻辑数均为Ture时，计算结果才为Ture。

(2) Or运算：计算两个逻辑数值的”逻辑或”，当参与Or运算的两个逻辑值其中一个为True时，结果为Ture，只有两个逻辑值均为”False”时，结果才为”False”

(3) Not运算：计算单一逻辑数值的”逻辑非”，当参与运算的逻辑值为Ture时，计算结果为False；当参与运算的逻辑值为False时，运算结果为True。

(4) Eqv运算：计算两个逻辑数值的”逻辑等价”，当参与Eqv运算的两个逻辑值的数值一致时，即两边都为True或者都为False，结果为True；否则结果为False

(5) Imp运算：计算两个逻辑数值的”逻辑蕴含”，当参与Imp运算的第一个逻辑数值为Ture，且第二个逻辑数值为False时，运算结果为False，其他情况下运算结果均为True。

(6) Xor运算：计算两个逻辑数值的”异或”结果，即当两个逻辑值中其中一个是True，一个是False时，运算结果为True，其他情况下，运算结果为False.

4. 字符串运算

(1) 判断字符串大小运算，字符串大小比较的运算符，同逻辑比较运算符。在比较两个字符串时，先比较字符串首字母ASCII码的大小，如果一致，则进行比较第二个字符的ASCII……，如果全部一致，则两个字符串大小一致。

(2) 求字符串长度函数Len()，函数Len() 将返回一个整数，这个整数等于字符串的长度，请尝试如下程序：

Sub StrLen（）

Dim A As String, N As Integer

A=“I Love China”

N=Len(A)

MsgBox(N)

End Sub

运行结果显示，N的数值为12,（空格也算字符！）

(3) 左侧取字符函数Left(). 函数Left()返回从左侧取字符串的结果，函数的用法是：Left(字符串,N)表示从字符串的左侧取长度为N个字符后的结果。请尝试一下下面的程序。

Sub L()

Dim A As String, B As String

A=“Microsoft Cooperation”

B=Left(A,5)

MsgBox(B)

End Sub

运行结果，从字符串”Microsoft Cooperation”中，从左侧取5个字符，等于Micro。

(4) 右侧去字符函数Right(). 函数Right()的用法和Left()一致，不同的是，这个结果为从右侧取字符。

(5) 从中间取字符函数 Mid().Mid()的用法是: Mid(字符串,开始位置,N)，表示从字符串中开始位置（第多少个字符开始）取N个字符，请尝试下列程序

Sub M()

Dim A As String, B As String

A=“I Came From China”

B=Mid(A,3,4)

MsgBox(B)

End Sub

运行结果：在字符串”I Came From China”中，从第3个字符开始取4个字符，得到”Came”。

(6) 获取字符串2在字符串1中的起始位置。Instr()函数可以返回字符串1在字符串2中的位置。用法是Instr(开始位置,字符串1,字符串2)，表示从字符串1的开始位置查找字符串2.请尝试以下程序，若字符串2中没有找到字符串1，则返回开始位置的数值减去1.

Sub I()

Dim A As String, B As String

A=“Microsoft Visual Basic For Applications”

B=“For”

MsgBox(Instr(1,A,B))

End Sub

运行结果。返回字符串”For”在字符串”Microsoft Visual Basic For Applications”中的位置，24.

这里需要说明的是，如果字符串2中包含两个或者两个以上的字符串1，函数只会给出字符串1第一次在字符串2中出现的位置。例如

Sub I2()

Dim A As String, B As String

A=“Microsoft Visual Basic For Applications Is Suit For Beginners”

B=“For”

MsgBox(Instr(1,A,B))

End Sub

结果还是输出24.

(7) 反向获取字符串2在字符串1中的起始位置，InStrRev()函数，可以获得字符串1在字符串2中的位置，用法为InStrRev(字符串1,字符串2,起始位置)，与上面函数不同的是，这个函数将在其实位置处从后向前查找，查找到的位置还是按照从前往后计算的！

(8) 将字符串中的英文字符换成大写UCase()函数。UCase(字符串) 作用是，将字符串中的英文字符写成大写。在转换后，数字或者其他符号并不会改变。

(9) 将字符串中的英文字符换成小写LCase() 函数。LCase(字符串) 作用是，将字符串中的英文字符写成小写。

(10) 获取字符的ASCII码：Asc()函数。ASCII(字符)作用是，获取字符的ASCII码。

(11) 按照ASCII码获取字符：Chr()函数。Chr(ASCIICode)作用是，根据ASCIICode（数字）获得ASCII码对照表中数字相对应的字符。

(12) 重复输入String()函数，String(N,字符)将获得字符重复N遍的字符串。

(13) 空格函数Space()。Space(N)将返回N个空格。

(14) 分割字符串函数Split()。Split()的用法是：Split(字符串,分割字符)，它的意思是，在字符串中，按照分割字符分割字符串，分割后的结果，是一个字符串数组。请尝试下列程序（关于数组的相关内容，请阅读第4章）

Sub S()

Dim A As String, B() As String, I As Variant

A=“Red,Yellow,Blue,Black,Orange,Pink”

B=Split(A,”,”)

For Each I In B

MsgBox(I)

Next I

End Sub

这一段程序的作用是，先声明一个字符串变量A，这个字符串变量A中储存了各种颜色，即”Red”、”Yellow”、”Blue”、”Black”、”Orange”、”Pink”。每种颜色之间以逗号”,”连接。然后再定义一个数组B，用Split函数分割数组A，分割的依据是逗号”,”。分割完了之后，B这个数组中，有6个元素，这6个元素分别等于上面的”颜色”，然后再用一个I变量把B中的元素一个一个显示出来。所以结果是，第一次信息框显示”Red”，点击”确定”后，第二次显示”Yellow”……，一直到显示”Pink”。

(15) 字符串连接函数Join()。Join()函数正好和Split函数相反，即将一个数组合并为一个字符串。使用方法是，Join(数组,分隔符号)，表示用分隔符号连接数组。请尝试下列程序：

Sub J()

Dim A(2) As String, B As String

A(0) = "Red"

A(1) = "Green"

A(2) = "Blue"

B = Join(A, "+")

MsgBox (B)

End Sub

这一程序作用是，先定义了一个数组A，数组A中的三个元素分别赋值为”Red”，”Green”和”Blue”，然后用加号”+”将3个元素连接起来，连接后的字符串为”Red+Green+Blue”。

(16) 字符串连接符号 “&”。”&”符号用于对两个字符串的”连接”。例如：”12” & “34” 连接后结果为”1234”，”Hello” & “World”连接后结果为”HelloWorld”，记住，”&”符号只是简单是”连接”，并不能在连接的两个字符之间加入空格或者其他特殊字符，需要加入这些字符时，可以采用 “&”字符的方式，例如，想连接”Hello”和”World”，中间加入Tab（制表符）时，可以写成”Hello” & Chr(9) & “World”. (制表符的ASCII码为9)。”&”也可以用于字符串和数字的连接，连接效果是，先把数字转化成字符串，再连接，因此，”12” & “34” 与 “12” & 34效果是一样的，均为”1234”。

(17) 字符串连接字符 “+”，”+”也可以用作字符串连接符，与”&”不同的是，”+”的行为比较”诡异”，其连接效果往往不是我们所需要的效果。连接规律为：当连接的两侧均为字符串时，连接效果与”&” 相同，即 “12” & “34” 与”12” + “34” 效果一致，均为”1234”。当连接的一侧为数字字符，另一侧为数字时，会进行数字的加法运算，然后再转换为字符，例如”12” + 34，结果为”46”。而当连接的两侧一侧为字符（含有字母）一侧为数字时，则不可以连接。

3.3.4 变量类型的转换

正如我们之前所见，变量在进行运算时，VBA可以自动将变量进行转换。但是有些转换就不可以自动实现了。VBA中，提供了各种转换类型的函数。我们可以用这些函数进行数据转换，然后再运用于各种运算。

（1）转换成整形变量CInt()。CInt()的作用是，将一个字符或者小数转换成整数，转换过程中，所需要转换的内容为字符时，这个字符中的所有字符必须全部是数字，例如CInt(“2.6”)可以转换成功，如果含有其他字符，例如CInt(“My1”)则会显示类型不匹配。CInt()转换时，无论正数或者负数，均采用”五舍六入”原则，即只有小数部分大于0.5时，才会”入”。例如CInt(2.5),结果是2,CInt(2.51)结果是3，CInt(-2.5)结果是-2，CInt(-2.51)结果是-3。

（2）转换成数值型变量Val()。Val()函数的作用是，将一个字符串转换成数值，转换的数值，当第一个字符是数字的时候，等于第一个数字，当第一个字符不是数字的时候，结果等于0，例如Val(“My21thBook2”)结果为0，而Val(“21th2”)结果为21。

（3）转化成字符串型变量Str()和CStr()，Str()的作用是，将一个数字转换成字符串，转换规律是，如果原先数字大于0，则会在字符串最前端多出一个空格，如果原先数字小于0，则最前面是表示复数的符号，即”-“。CStr()作用也是数字转换成字符，不同的是，如果原先数字大于0，则转换后的字符前面不会多出一个空格。

3.3.5 变量的命名法则

当一个程序比较复杂，使用的变量比较多的时候，有必要引用变量的命名法则。变量的命名法则并不是VBA强行规定的，只是为了程序编译人员方便而人为规定的，可以想象，如果一个程序有1000行命令，涉及到100个变量，如果这些变量随机地按照a1、a2、a3……这样命名，那么用不了多久，程序的编写人员自己都会忘记当初这些变量每一个是什么作用，是什么类型了。

（1）变量命名的总原则：”见字知意”，即看到变量名称就知道这个变量在程序中的作用。例如，定义一个向用户显示信息的字符串变量为Dim MyMsg As String。由于MyMsg这个名字，由My（我的）加上Msg（信息=Massage）所以以后看到MyMsg就知道这个变量当初的用途是向用户显示信息，或者需要向用户显示信息时候，就用这个MyMsg变量。

（2）变量名称尽可能地短，毕竟，不会有人用像例如Get_The_Information_Of_

FileName_As_My_Massage这么长的变量名称（打字都得大半天）。

（3）大小写混合，可以感受一下下面四个变量名： MyName, myname, MYNAME, Myname, myName。尽管VBA中变量名和函数名不区分大小写（后面章节中会说到函数名），但是，在这几种表达方式中，最为舒服的表达方式为MyName。

（4）用变量类型的前3个字母表示这个变量的类型，例如，定义一个字符串的变量，用StrL这个名字比用A这样的名字更能显示这个变量的类型。

（5）使用公认的变量名称，例如，i、j、k一般用于循环变量名，i，j还用于表示行和列的行数、列数，M、N一般用于表示一个二维数组的大小（M为列，N为行），File一般用于文件操作时的文件名称、Path一般用于文件操作时的路径名称，等。

第四节 变量的使用范围

3.4.1 内部变量、私有变量、公共变量、静态变量

(1) 内部变量

正如之前的例子用Dim在Sub内部可以定义变量，但是，这种用Dim在一个过程（Sub）内部定义的变量，在过程的外部却访问不到，不同的过程中，同样的变量名的变量也是不同的两个变量。例如，下面一段程序。

Sub Test1()

Dim MyAge As Integer

MyAge=27

Msgbox(MyAge)

End Sub

Sub Test2()

MyAge=MyAge+1

MsgBox(MyAge)

End Sub

分别运行这两个过程，可以看到，第一个过程结果是27，第二个过程结果是1.因为在Test2过程中的MyAge与Test1中的MyAge是不同的变量。在Test1中声明的MyAge变量，赋值为27。但是在Test2中，并不能访问Test1中的变量而是又重新定义了一个变量MyAge，因为没有给MyAge赋值，所以这个变量的值自动为0，把此时再把这个变量进行加一运算，结果是1.

像这种在过程或者函数内部，随着函数或者过程的消失而消失的变量叫做内部变量，由于内部变量总是随着过程或者函数的结束而消失，所以，在不同的函数或者过程内部可以有相同名称的内部变量。

如果我们想在过程Test2中访问过程Test1中的MyAge变量怎么办？我们可以把过程Test2加上一个参数，并且让这个参数等于过程Test1中的MyAge，然后用过程Test1来调用过程Test2，如下所述。

Sub Test1()

Dim MyAge As Integer

MyAge=27

MsgBox(MyAge)

Call Test2(MyAge)

End Sub

Sub Test2(MyAge As Integer)

MyAge=MyAge+1

MsgBox(MyAge)

End Sub

这里，过程Test1中的MyAge变量通过参数的形式传递给过程Test2，需要注意的是，过程Test2的参数可以用任意的名称，例如写成下列形式：

Sub Test2(Age As Integer)

Age=Age+1

MsgBox(Age)

End Sub

运行结果是一样的。这里有两点值得我们注意，第一，在Test1中，运用Test2过程的时候，前面加了一个关键字：”Call” 这个关键字的作用是，在Test1执行的过程中，执行到这一条语句时，转到Call后面的过程或者函数起始位置开始执行命令。第二，Test2通过参数传递的方式，从Test1中传递了参数MyAge，执行完毕后，Test1中的这个变量MyAge的数值也会发生改变，这一条读者先行记住即可，我们将在代码实战部分讲解参数的传递。

（2）私有变量

可以在一个模块开始的时候定义这个模块所使用的”私有变量”,定义私有变量的方法为Private 变量名 As 类型，定义在一个模块中的私有变量，在这个模块中随处可以访问，只有当模块结束的时候，这个变量才会消失。例如，在同一个用户模块中编入下列程序：

Private MyAge As Integer

Sub T1()

MyAge = 27

MsgBox (MyAge)

End Sub

Sub T2()

MyAge = MyAge + 1

MsgBox (MyAge)

End Sub

在这个模块中，T1过程和T2过程均可以访问变量MyAge，所以运行结果，如果先运行第一个，再运行第二个，第一次结果为27，第二次结果为1.如果直接执行第二个过程，则运行结果为1。（第一个过程相等于给MyAge变量赋值为1，而第二个过程是把MyAge的数值加上1）

私有变量只能在模块内部处处可见，而在模块外部却不可以调用，因此，不同的模块可以有相同名称的私有变量。

（3）公共变量

可以在模块开始的时候声明一个公共变量，声明公共变量的方法是: Public 变量名 As 类型。定义的公共变量，在这个工程中随处可见，不同的模块之间也可以调用。例如，首先插入一个模块，写入：Public MyAge As Integer，然后再插入一个模块写入以下两个过程。

Sub T1（）

MyAge=27

MsgBox(MyAge)

End Sub

Sub T2()

MyAge=MyAge+1

MsgBox(MyAge)

End Sub

可以看到，尽管在这个模块中并没有定义私有变量MyAge，但是因为在之前的模块中，已经把MyAge定义成为了公共变量，所以，T1和T2仍然能够正常调用。

（4）静态变量

静态变量是介于内部变量和私有变量之间的一种变量，这种变量只有函数或者过程本身可以调用，其他函数或者过程不可以调用，但是，每调用一次后，静态变量的数值将会得到保存。举个例子说明一下，首先我们来看一下下列程序段。

Sub S()

Dim a As Integer

a=a+1

MsgBox(a)

End Sub

这样一段程序段，无论执行所少次，a的数值始终等于1，这是因为这个过程中的这个变量a随着过程的结束就会消失了，下一次再执行的时候，再重新声明变量a，在变量a没有赋值的时候，VBA自动给这个变量a赋值为0，所以a加上1，始终等于1。

但是，我们只需要把上面过程中的Dim 改成Static，情况就会发生改变了！像下面的这一段程度：

Sub S()

Static a As Integer

a=a+1

MsgBox(a)

End Sub

这样一段程序，随着执行次数的增加，a的数值会一直增加，每一次增加1，只要不关闭这个Excel工作表，a的数就会增加！这是因为，这个过程中，声明了一个静态的变量a，这个a的数值并不会因为过程的结束而消失，下一次再运行的时候，a的数值会保留上一次的运行结果。

第五节 常量

3.5.1 定义常量的意义

计算机中有变量，就有常量。所谓常量就是不能更改的量，例如数字123，字符串”My Work” 等，显然，我们可以用常量给变量赋值，例如Dim a As Integer a=1,但是却不可以给一个常量“赋值”，显然，数字3不可以“赋值”成数字5.即除了赋值运算，常量可以参与前面提到的所有运算。

为什么要定义常量呢，我们来看一下一个例子，假如我们现在要计算一个圆形的表面积、一个圆柱体的体积、和一个球的体积。这时候就需要用到圆周率这样一个常数π，而这个常数π等于3.1415926（假设我们只取小数点后7位数），那么，这个程序就需要这样写。

Sub CalC()

Dim R1 As Double, R2 As Double, H As Double, R3 As Double

Dim S1 As Double, V1 As Double, V2 As Double

R1=Inputbox(“输入圆的半径”)

S1=3.1415926*R1*R1

MsgBox(“圆形的面积为” & S1)

R2=Inputbox(“输入圆柱的半径”)

H=Inputbox(“输入圆柱的高”)

V1=3.1415926*R2*R2*H

MsgBox(“圆柱体积为” & V1)

R3=Inputbox(“输入球的直径”)

V2=4/3*3.1415926*R3*R3*R3

MsgBox(“球的体积为” & V2)

End Sub

这个程序中，Inputbox()的作用是，用一个对话框提示用户输入数据，在计算圆形的面积的时候需要用户输入圆形的半径，在计算圆柱体体积的时候，需要用户输入圆柱的半径和高，而需要计算球体体积的时候，需要用户输入球体的半径。MsgBox()这个在我们之前许多程序中已经见到了，它的作用，是向用户输出程序的结果和相关信息。关于Inputbox和MsgBox的作用，我们将在后续章节中详细讲解。

从这个程序中，我们可以看到，3.1415926这个数字被一个程序用了很多次。像这种常量，因为我们需要不断地引用，所以如果按照每一次重新输入的做法，一是比较麻烦，二是有可能在某一次输入的时候输入错误。这时，就可以定义一个常量。定义常量的命令是： Const 常量名 = 常量。

3.5.2 常量的定义和使用

还是用上面这个例子，如果我们定义了一个常量PI=3.1415926那么，上面这一段程序可以写成：

Sub CalC()

Dim R1 As Double, R2 As Double, H As Double, R3 As Double

Dim S1 As Double, V1 As Double, V2 As Double

Const PI=3.1415926

R1=Inputbox(“输入圆的半径”)

S1=PI*R1*R1

MsgBox(“圆形的面积为” & S1)

R2=Inputbox(“输入圆柱的半径”)

H=Inputbox(“输入圆柱的高”)

V1=PI*R2*R2*H

MsgBox(“圆柱体积为” & V1)

R3=Inputbox(“输入球的直径”)

V2=4/3*PI*R3*R3*R3

MsgBox(“球的体积为” & V2)

End Sub

可以看出，我们这里用常量PI代替了3.1415926，在程序中，每一次需要使用数字3.1415926的时候，就用PI来代替。这样做有三个好处：第一，在语法上更加接近我们传统的数学公式，毕竟，PI*R1*R1比3.1415926*R1*R1“更像”圆面积公式。第二，减少输入，只需要在开始定义的时候把PI定义成3.1415926，后面就可以直接用PI来代替这个数字了。第三，减少输入的错误，假设程序有100行，3.1415926这个数字需要使用100次，那么，如果直接用数字，可能会在某一次输入的时候输入成3.14或者3.14159265358979这样的数（毕竟人的大脑容易产生遗忘）。

第四章 对象

第一节 什么是对象Excel VBA中的对象

4.1.1 对象和类

所谓对象就是自然界存在是一切实体，所谓类就是这些实体在人脑中的一个“划分”。例如，“人类”是一个类，而一个具体的人，例如张王李赵这样的个体，就是“人类”这个类中的一个对象。“电脑”也是一个类，具体到“这台电脑”就是电脑这个类的一个对象了。

在VBA中，“对象”一词来源于英语“Object”，指Excel中可以操作的具体对象。在VBA中，除了类和对象之外，还有父类和子类、父对象和自对象的概念。

父类和子类，在类别划分的领域，人们总是习惯地把一个类规划为另一个类中的一部分。例如，“人类”和“猿类”同属于“灵长类“，”圆珠笔“和”笔记本“同属于”办公用品“。在这种一个类中包含另一个类的划分结构中把包含其他类型的类称为“父类“，而被包含是类称为“子类”。在上面的两个例子中，“灵长类“是”人类“和”猿类“的”父类“，而”笔记本“和”圆珠笔“是”办公用品“的”子类“

父对象和子对象，与父类和子类的定义相同，一个”父对象“中包含了许多可以操作的”子对象“，就好比一个具体的人，是一个具体的对象。而这个人的四肢、眼睛、鼻子、耳朵就是这个人这个父对象的”子对象“。显然，无论是父对象还是子对象，都是可以直接操作的对象，也就是具体的事物，但是这些事务之间又有着”包含“的关系。

4.1.2 Excel VBA中的对象

在Excel VBA中，有着4大对象，即Application对象、WorkBook对象、WorkSheet对象和Range对象。这四大对象之间是上级和下级，父对象和子对象的关系。

Application对象：Excel VBA中最高级别的对象，这个对象就是整个Excel VBA应用程序，在这个对象中，可以实现对应用程序级别的任何调用。

WorkBook对象 Excel VBA中的工作簿对象，这个对象是在Excel VBA中的参与操作的工作簿。

WorkSheet对象 Excel VBA中的工作表对象，这个对象是在一个工作簿中的一个工作表。在Excel 工作簿中，可以添加和删除工作表。这些工作表的关系就是这个工作簿的”子集“

Range 对象 Excel VBA中的单元格对象，这个对象是一个工作表中的一个单元格或者一个单元格区域，这一部分是Excel VBA操作的重点，也是本书重点阐述的内容。

第二节 对象的属性和方法

4.2.2 对象的属性

操作对象在某一个方面的特征，称为这个对象的”属性“例如，对于一个具体的人，这个人的姓名就是他的一个属性，性别是另一个属性，显然，对于一个具体的对象，其属性也是一个具体的数值。

对于确定的对象，可以读取这个对象的某一个属性，例如，对于一台电脑，可以获知这台电脑的厂家、品牌、内存大小、处理器情况等。

对象的属性，有些属性可以修改，修改对象的属性并不会影响对这个对象的其他操作，例如在World中修改正文的字体和字号，其实是修改了这个字的”字体“属性和”字号“属性，并没有改变正文的本身内容。

而对象的另外一些属性只能读取不能修改，例如，对于一部手机，可以获知这部手机的型号和厂家，但是却不能修改。想获得另外一个厂家和型号的手机，只有另外再买一部。

4.2.3 对象的方法

对象的操作方法称为对象的方法，即对于一个具体的对象，可以采用什么样的操作方法。例如，对于一本书，我们可以通过”阅读“的方法阅读这本书中的内容。对于一壶冷水，我们可以通过”加热“的方法使得水的温度上升。

4.2.4 对象的默认属性

对象的默认属性，是对象呈现在外界时的属性，即外界通过这一个属性可以直接识别对象本身。例如，对于一个人来说，他的默认属性是姓名，其他的人总是通过姓名在直接识别这个人，而不是通过这个人的身高、体重、肤色等其他属性。对于一本书，总是以书名为默认属性向外界呈现。

4.2.5 对象的默认方法

既然对象有默认的属性，那么对于一个确定的对象，也有一个默认的方法来操作该对象。在VBA中，对象的默认方法为显示方法，即这个对象总是通过图形、数字等形式显示在屏幕上。在VBA中无论调用一个对象的属性或方法或它的子对象，均是在这个对象后面加上一个点，即英文状态在的句号 ”.” 来实现。用上面的例子，人的姓名在VBA中的“表示方法“就是：人.姓名。

4.2.6 声明一个对象变量

声明对象变量的方法是：Dim 变量名 As 对象。

4.2.7 Active对象

在VBA中，Active对象成为活动对象，是指程序当前处理或者选择的具体对象。例如ActiveSheet就是当前正在编辑的工作表对象，ActiveCell就是当天正在编辑的单元格或者区域对象。

4.2.8事件

所谓的“事件”通俗来说就是发生了的一个事情，例如，“刚刚下雨了”就是一个事件，“火车启动了”，也是一个事件。通常来说，事件往往伴随着一系列的“后果“，例如”刚刚下雨了“，会导致”出门要带雨伞“的后果。事件的发生在VBA中称为”触发“。

第三节 Workbook对象的属性和方法

由于Application对象作用于整个Excel VBA工程，所以我们这里从它的子对象WorkBook开始讲解。由于每一个对象的属性和方法十分复杂，正常运用也不会涉及这么多，所以这里只挑选几个比较重要的讲解，需要进一步了解其他属性和方法的读者可以去CSDN查询相关的内容

第三节 Workbook对象

4.3.1 Workbook 和ThisWorkbook对象的属性

ThisWorkbook 这个对象指当前打开的VBE所在的工作薄，ThisWorkbook以及Workbook对象中常用的属性有：

Name属性，返回当前工作簿的名称，例如，在桌面上的新建文件夹中一个名字叫做”工作簿3“的Excel表，打开后，在VBE环境下尝试下列程序：

Sub W()

MsgBox(ThisWorkbook.Name)

End Sub

运行结果，返回该Excel工作薄（文件）的名称：工作簿3.xls。

Path属性，返回当前工作簿所在的保存路径，还是上面的例子，假如程序改成：

Sub W()

MsgBox(ThisWorkbook.Path)

End Sub

运行结果，返回工作簿所在的保存路径，即C:UsersAdministratorDesktop新建文件夹。

利用ThisWorkbook的Path属性获得工作簿所在目录下的所有Excel工作簿名称（所有xls格式工作簿名称）

Sub W()

Dim Path As String, File As String

Path = ThisWorkbook.Path & ""

File = Dir(Path & "*.xls")

MsgBox (File)

While File <> ""

File = Dir

If File = "" Then Exit Sub

MsgBox (File)

Wend

End Sub

上面这个例子中，利用 Thisworkbook.Path返回当前工作簿所在的路径，并且使用Dir函数一一读取这个路径下所有的xls格式文件的文件名。对于Dir函数，其使用规则如下：Dir(Path,[,attr]),当第二个参数缺失时，Dir函数返回路径下没有设置属性的文件的名称。第一次使用Dir函数时，返回路径下的第一个文件名称，再次使用这个函数，并且不加参数，返回第二个文件名称……当路径下没有文件或者所有文件名称均被返回，则返回一个空字符。Dir函数的第二个参数可以是下列参数之一：vbNormal（没有属性的文件）、vbRedOnly(只读文件)、vbHidden(隐藏文件)、vbSystem(系统文件)、vbVolume(卷标文件)、vbDirectory(文件夹的名称和文件名称)

与ThisWorkbook一样，任何Workbook对象都有Name和Path属性，用法与ThisWorkbook中的用法一致，例如，可以定义一个WorkBook对象变量，并获得该对象的名称和路径。

4.3.2 Workbook对象常用到的事件

在VBA中，打开工作簿，关闭工作簿，激活工作簿都是一个“事件“。

打开工作簿事件：打开工作簿时，触发”打开工作簿“事件，在VBE界面窗口中，点击资源管理器中的“ThisWorkbook”就可以进入ThisWorkbook的工作簿事件。

从这里可以看出，Workbook的事件编辑窗口与一般的之前我们的模块编辑窗口没什么不同，只是多了一行选择菜单。这里，在“通用”下拉菜单中找到“Workbook”，就可以在后面“声明”下拉菜单中找到Workbook的可利用事件。这里我们只讲解其中4个事件。

Open事件：打开Workbook时触发Open事件，例如，我们如果想每一次打开这个工作簿就显示欢迎界面，可以在ThisWorkbook的事件编码器里面找到Workbook的Open事件，并加上一行代码： MsgBox(“你好！”)，整个程序段如下：

Private Sub Workbook_Open()

MsgBox ("你好！")

End Sub

图4.2 在Workbook的Open事件中添加欢迎界面“你好！”

保存一下，以后我们每一次打开这个工作簿的时候，都会弹出这个欢迎的对话框“你好！”

图4.3 欢迎界面

BeforClose事件：在关闭工作簿时，触发BeforeClose事件，例如，我们想让每一次关闭工作簿的时候，弹出一对话框“Good Bye！”，可以在ThisWorkbook的事件编码器里面找到Workbook的BeforeClose事件，并且添加一行 MsgBox(”Good Bye！“)完整代码如下：

Private Sub Workbook_BeforeClose(Cancel As Boolean)

MsgBox ("Good Bye!")

End Sub

保存后，每次关闭工作簿时就会弹出这个对话框了。

图4.4 关闭时显示“Good Bye!”

BeforeSave事件 保存工作表前，触发BeforeSave事件，例如，我们想自己添加一个是否保存的对话框，并且点击”是“时进行保存，”否“不保存，可以在ThisWorkbook的事件编码器里面找到Workbook的BeforeSave事件，并且加上命令，完整代码如下。

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

MsgBox ("保存是个好习惯")

Cancel = Not (MsgBox("保存？", vbYesNo))

End Sub

这一程序的运行结果，每一次保存工作簿前都会给出提示“保存是个好习惯“，然后提示是否保存，注意到，这个程序中的Cancel是”取消保存“，所以，前面用了一个否定运算符的Not，后面MsgBox，一是给用户显示”是否保存“，二是显示两个按钮，即”是“和”否“。用户按下”是“则返回逻辑值”Ture“，否则返回”False“。

AfterSave事件 保存工作簿后，触发AfterSave事件，不同的是，AfterSave时间中没有”取消“选项了，我们也可以在AfterSave中添加代码，用于提示保存成功。

Private Sub Workbook_AfterSave(ByVal Success As Boolean)

MsgBox ("保存成功！")

End Sub

给工作簿添加打开密码：通过Workbook的Open事件，可以给工作簿添加打开密码，完整代码如下：

Private Sub Workbook_Open()

Dim PassWord As String

PassWord = InputBox("请输入密码")

If PassWord = "123456" Then

Exit Sub

Else

MsgBox ("密码错误！")

ThisWorkbook.Close

End If

End Sub

这一段代码作用是，每一次打开工作簿的时候要求用户输入密码，如果输入的密码不是123456，则显示“密码错误“，并且关闭工作簿。

图4.5 给Excel工作簿添加打开密码

第四节 worksheet对象

4.4.1 worksheet对象的引用

对于一个worksheet对象，可以采用的引用方法有

（1）直接引用Sheet+数字的形式，Sheet1即这个Excel工作簿中的第一个工作表，Sheet2为Excel工作簿中的第二个工作表……

（2）采用worksheets(n)的形式，表示工作簿中第n个工作表（按照从左向右的顺序）。

（3）采用worksheets(“字符串”)的形成，表示名子为字符串的工作表。

（4）采用worksheets(“Sheet”+数字)的形式，表示第n个工作表。

4.4.2 worksheet对象的属性

Name属性：与Workbook中的Name属性一样，worksheet也有Name属性，我们可以访问这些名称。例如，获取工作簿中的工作表名称，可以写成下列程序

Sub GetWSN()

Dim wsh As Worksheet

For Each wsh In Worksheets

MsgBox wsh.Name

Next wsh

End Sub

程序中，Worksheets即整个Excel工作簿中饭的所有工作表，这个例子中，运用For Each……Next对工作簿中的工作表一一遍历，然后输出每个工作表的名称。

也通过修改工作簿中的工作表的Name属性修改工作表名称，例如。

Sub ChWHN()

dim wsh As WorkSheet, i as Integer

i = 1

For Each wsh In WorkSheets

wsh.Name=”表” & i

i =i +1

Next wsh

End Sub

运行结果，用一个循环变量i，给Excel工作簿中所有的工作表编号。

Visible属性：工作表的Visible属性决定这个工作表是否可见，如果我们想影藏工作表1，只需要 Sheet1.Visible= False, 或者Worksheets(1).Visible=False即可。

Sub Hiden()

Worksheets(1).Visible = False

End Sub

显示所有的工作表：与上过程相反，显示所有的工作表可以用下列语句

Sub ShowAll()

Dim wsh as Worksheet

For Each wsh in WorkSheets

wsh.Visible=True

Next wsh

End Sub

语句也是通过一个循环变量，将所有工作表的Visible属性修改为True。

Index属性：返回工作表的序号，例如，在Excel工作簿中有一个名字为“My Work Sheet”的工作表，可以用过Worksheets(“My Work Sheet”).Index得到这个工作表在工作簿中的序号。

4.4.3 worksheet对象的方法

Copy方法，Copy方法可以复制粘贴整个工作表。例如，将整个工作表1复制一份，命令是 Sheet1.Copy。此时，Excel会在最后自动建立一个新工作表，然后工作表中的内容和表1中完全一致。

Activate方法，Activate方法可以使工作表处于激活状态，例如，在工作表1中进行工作时，想跳转到工作表2，用VBA代码的方法就是：Sheet2.Activate。

Delete方法，Delete方法用于删除一个工作表。例如，Sheet2.Delete，就删除了工作表2.

Move方法，Move方法可以改变工作表在工作簿中的位置，例如：Sheet2.Move After:=Sheet3 ,就将Sheet2工作表移动到了工作表Sheet3的后面，当然也可以利用例如：Sheet4.Move Before:= Sheet2 的方式把表Sheet4移动到Sheet2的前面。

第五节 Range对象

Range对象即单元格对象，是应用最为广泛，使用最为灵活的一个Excel对象，是本章节的重中之重。

4.5.1 Range对象的引用方法

（1）单一引用

直接用Range或者Cells或者[]方式引用，Excel VBA中，可以直接用代码引用Excel表格中的单元格或者区域。对于这些单元格或者区域，可以采用单一引用的方式，也可以采用嵌套引用的方式 。

Range(“A1”)方式引用单元格：Excel VBA中，可以用Range(“A1”)的方式引用Excel中的单元格，Range(“A1”)即表示Excel表格中的”A1”单元格，即第一行第一列的单元格。例如，可以用以下命令将A1单元格中的数字修改为2019.

Sub S()

Range(“A1”)=2019

End Sub

Cells(1,1)方式引用单元格：在Excel VBA中，还可以用Cells(1,1)的方式引用单元格，Cells(1,1)也表示Excel表格中第一行第一列的单元格，即“A1”单元格，例如，可以用下面命令给B1单元格中的数字修改为4

Sub S1()

Cells(1,2)=4

End Sub

[A1]方式引用单元格: 在Excel VBA中，还可以用[A1]方式引用单元格，[A1]方式引用单元格也是引用Excel中的A1单元格。可以用下面程序修改C1单元格中的数字。

Sub S2()

[C1]=25

End Sub

引用的引用：假如我们在Excel的单元格A1中写入了一个单元格的名称，例如”B2“，那么，用Range([A1])的方式同样可以引用到B2单元格。例如，下面程序中，利用A1单元格中的内容给相应的单元格赋值为15.

Sub S3()

Range([A1])=15

End Sub

(2) 嵌套引用 利用Range不仅可以引用单个单元格，而且可以引用一个单元格区域，Range应用单元格区域的方法和意义如下。

Range(“A1:B5”) 表示引用Excel表格中整个A1到B5区域。这种引用方法，引号中的部分可以用一个字符串变量代替。

Range(“A1”,”B5”)也是表示引用Excel表格中整个A1到B5区域，这种引用的方法中，逗号分隔的两个部分可以使用用两个字符串变量代替。

Range(“A:A”) 表示引用Excel表中整个A列所有单元格。

Range(“A:C”) 表示引用Excel表中A列到C列的所有单元格。

Range(“2:2”) 表示引用Excel表中整个第2行的所有单元格。

Range(“2:5”) 表示引用Excel表中第2行到第5行所有单元格。

整列的引用，除了可以用Range，也可以用Columns(“A”)的方式或者Columns(“A:B”)的方式。Columns(“A:B”)表示引用A列和B列。

整行的引用，可以采用Rows()的方式，与Columns方式引用整列的用法相似，Rows(2)表示引用整个第2行。

单元格对象的嵌套引用，可以用Range引用一个区域，再用Range引用其中的一个单元格或者区域，例如Range(“B2:D4”).Range(“B2”) 表示单元格区域B2到D4中的“B2”格，即第二行第二列的单元格，即C3单元格。

图4.6 单元格的嵌套引用

也可以用Range选择一个单元格区域，再用Cells引用单元格区域中的某一个单元格，例如Range(“C2:F9”).Cells(2,4) 表示单元格区域C2到F9中，第2行第4列的单元格。即F3单元格。

4.5.2 Range对象的属性和方法

Address属性：Address属性返回一个Range对象在工作表中的绝对位置，下面命令在A1单元格中返活动单元格的位置，当Range对象为一个单元格区域时，返回这个单元格区域的第一行第一列所在的位置。

Sub Address()

Range(“A1”)=ActiveCell.Address

End Sub

Column属性：Column属性返回单元格所在的列的列数，例如，将与活动单元格同一列的第4行的单元格的数修改为29.

Sub Col()

Cells(4, ActiveCell.Column) = 29

End Sub

Columns属性

Columns属性的Clear方法 用于清除单元格区域对象中的某一列或者所有列的数据。例如，想清除Excel 单元格区域A1：C3中第2列的数据，可以用以下程序。

Sub Col2()

Range(“A1:C3”).Columns(2).Clear

End Sub

如果想运用于单元格区域中的所有列，则Columns后面不加(2)。

Columns属性的Copy方法 用于复制单元格区域中某一列或者所有列的数据，例如，将Excel单元格区域A1：C3中第2列的数据复制到F列，可以使用下列程序：

Sub Col2()

Range("A1:C3").Columns(2).Copy Range("F:F")

End Sub

Copy方法前面是复制的“原始数据“的位置，后面是复制的”目标位置“。这里，直接用原始位置.Copy 目标位置，就可以完成这个复制操作。

Columns属性的ClearFormats方法，ClearFormats方法用于清除单元格区域中选定列的单元格格式。例如，加上单元格区域A1：C1格式设定为货币型，则这个单元格区域中所有的数字前多出一个货币符号”￥”,用Columns.ClearFormats就可以清除这种格式设置。

Columns 属性的Delete方法, Delete方法用于删除列，例如，想删除Excel中A列中的内容，并且让原来的B列自动成为A列则可以用下列程序。

Sub Del()

Range(“A:A”).Columns.Delete

End Sub

这里需要注意，删除列的操作可能会引起单元格中公式引用错误，例如，原先在D4单元格中输入公式“=A4+B4”，在删除A列后，这个公式就会发生引用错误的情况。这是因为，在进行删除列的操作后，公式所在的列也发生了变化（原来在D4格，删除一列后在C4格）

Columns 属性的Count方法，Columns属性的Count方法返回区域的列数，例如，Range(“A2:D5”).Columns.Count 等于4（这个区域有4列）。Columns属性的Count方法通常用于用户输入或者选择区域的地方，例如：

Sub ColumnCount()

Dim Rng As Range

Set Rng = Application.InputBox("选择区域", "计算区域列数", , , , , , 8)

MsgBox (Rng.Columns.Count)

End Sub

这一段程序中，需要解释的是Application.InputBox的用法，Application.InputBox是InputBox的”升级版本“其可以允许用户输入数字、文本（字符串）、公式、逻辑值、单元格或者单元格区域，其用法是Application.InputBox(Prompt,[Title],[Default],[Left],[Top],[HelpFile]

,[HelpIndex],[Type]),Prompt是对话框的内容，Title为对话框的标题，Default为默认输入值，Left和Top分别为对话框所在的屏幕位置（不设定情况下可以自由移动），HelpFile、HelpIndex为帮助文件，Type指定用户输入的类型，分别为0：公式，1：数字，2：文本，4：逻辑值，8：Range对象，16：错误值，64：数值数组。

Columns属性Active方法：用于激活Range对象的某一列。

Row 属性：Range对象的Raw属性，返回这个单元格所在的行数，如果是一个单元格区域，则返回该区域第一个单元格所在的行数。例如，将与活动单元格同行的第4列数字改为26.

Sub R()

Cells(ActiveCell.Row,4)=26

End Sub

Rows属性

Rows属性的Clear方法：与Columns属性的Clear方法相似，Rows属性的Clear方法用于清除一个Range对象中的某一行或者某几行数据。

Rows属性的Copy方法：用于复制一个Range对象的某一列数据。用法与Columns属性的Copy方法相似。

Rows属性同时也具有Columns属性的其他方法。

Offset属性：Range对象的Offset属性，返回Range对象移动若干个单元格后的单元格区域，Offset属性的用法是：Offset(行偏移量,列偏移量)，若行偏移量>0,则表示单元格区域向下移动，列偏移量>0表示区域向右移动，否则表示区域向上、向左移动。例如：Range(”A2:C6“).Offset(1,2) 表示单元格区域A2:C6向下移动1行，再向右移动2列的单元格区域，即C3:E7区域。

图4.7 单元格区域的Offset属性

利用Range对象的Offset属性，我们可以把一个Excel表特定的区域中的数字复制到其相邻的区域。例如：

Sub Rep（）

Dim Rng As Range

Set Rng=Range(“A1:C4”)

Rng.Copy Rng.Offset(0,Rng.Columns.Count)

End Sub

这一段的作用是，将A1:C4单元格区域中的内容复制到它右侧相邻的区域，即D1:F4.这里，先使用Columns的Count方法返回之前单元格区域Rng的列数Rng.Columns.Count，并使用Offset属性，让之前的区域向右移动这么多列，任意一个单元格区域，向右移动其列数个单元格后，总会得到与其相邻的右侧区域。最后，再用Copy方法将之前的内容复制过去。读者可以通过这个例子思考一下如何把单元格区域复制到其下侧相邻的区域。请读者记住这一个程序，我们会在下一节中对这个程序详加讨论，并把这个程序的思想“发扬光大”。

Interior属性

Interior属性的Color属性：用于修改单元格的背景颜色，可以采用vbRed(红色)、vbGreen(绿色)、vbBlue(蓝色)的方法修改单元格背景色，也可以用RGB()的方式修改单元格背景色。例如

Sub Col1()

Range(“A1”).Interior.Color=vbRed ‘将A1单元格的背景色改为红色

Range(“B2:D4”).Interior.Color=vbBlue ’将单元格区域B2:D4背景色改为绿色

Range(“E:E”).Interior.Color=RGB(0,0,255) ‘将E列单元格背景改为绿色

Range(“F2”,”G5”).Interior.Color=RGB(255,255,0) ‘将F2:G5区域背景色改为黄色。

End Sub

Interior属性的ColorIndex属性：也用于修改背景色的颜色，不同的是，ColorIndex通过一个数字修改背景色颜色，具体哪一个数字对应哪种种颜色，读者可以自己实现以下下列从程序。

Sub Col2()

Dim i As Integer, Rng1 As Range, Rng2 As Range

Set Rng1 = Range("A:A")

Set Rng2 = Range("B:B")

For i = 1 To 50

Rng1(i) = i

Rng2(i).Interior.ColorIndex = i

Next i

End Sub

程序输出了前50种颜色和其Index的对应关系。

Interior属性的Pattern属性

使用Pattern属性可以清除单元格或区域中的背景颜色，只需要将单元格的Interior.Pattern属性的值改为xlNone即可。

Font属性 Font属性用于修改单元格中的字体，可用属性有：Color，修改字体颜色；Bold，字体是否加粗；Italic，是否采用斜体，Size，设置字体大小，FontStyle，用于设置字体样式；Underline,是否使用下划线等。例如，将E2单元格字体设置为：红色、加粗、斜体、加下划线，字号为15的程序如下：

Sub Col2()

Range("E2").Font.Color = vbRed

Range("E2").Font.Bold = True

Range("E2").Font.Italic = True

Range("E2").Font.Size = 15

Range("E2").Font.Underline = True

End Sub

Formula属性: Range对象的的Formula属性的作用是在Range对象中运用Excel公式进行计算，相当于在Excel文本中的“插入公式”操作，例如，在单元格A6中，计算A1单元格到A4单元格中的和，程序为：

Sub F1()

Range("A6").Formula = "=Sum(A1:A5)"

End Sub

而要在A列到E列中，在第6行计算前5行单元格中的和可以用下列方式：

Sub F2()

Range(“A1:E6”).Rows(6).Formula=”=Sum(A1:A5)”

End Sub

通过Formula属性添加的公式，不会遇到之前提到的删除行或者列造成公式引用错误的情况。这里公式的书写方法和在Excel单元格中的书写方法是一致的，即加入$符号表示绝对引用。值得注意的是，这里在引号内外都有“=”号，引号外侧的“=”的用处是将这个公式作为一个“字符串”赋值给Range变量的Formula属性，而引号内部的“=”的作用是，告诉Excel这是一个公式，而不是一个字符串。

FormulaR1C1属性： FormulaRC1与Formula属性的作用是一致的，即向Excel单元格中添加公式，而不同之处在于，FormulaR1C1的方式是采用相对位置的方式引用单元格，还是以上述的程序为例子，如果采用FormulaR1C1形式，则书写方式为：

Sub F3()

Range(“A1:E6”).Rows(6).FormulaR1C1=”=Sum(R[-5]C[0]:R[-1]C[0])”

End Sub

解释一下这个R和C中括号中的数字的意思，R和C中括号里面的数字表示相对偏移量。在这个例子中，相对于A6来说，A1位于同一列前面的第5行，相对于A6来说，A1的行偏移量为-5，列偏移量为0，所以A1这里表示为R[-5]C[0]。同样的道理，A5表示为R[-1]C[0]。 在VBA中，如果偏移量为0也可以省略不写，上面两个可以直接写出R[-5]C和R[-1]C。中括号中的负数表示引用的单元格在当前单元格的上方或者左方,正数则表示引用的单元格位于目前的下侧或者右侧。

前面已经提到了Range对象的几种属性和方法，例如Copy、Clear、ClearFormats等，Range对象本身也有这些方法，例如前面的程序，直接使用了Range对象的Copy方法，而不是其Columns属性或者Rows属性的Copy方法，Range对象的Copy方法是复制整个Range对象，而Columns或者Rows属性的Copy方法只是复制Range对象中的某列或者某行。Range对象除了上述的属性和方法以外，还有一些属性和方法

Sort方法：Sort方法用于对Range对象按照列排序，Sort的用法如下：Sort key1:=Range对象1, order1:=方式1, key2:=Range对象2, order2=方式2……Header=……例如，对象区域A1:B5，按照A1列降序排序（没有标题）的程序为：

Sub S()

Range("A1:B5").Sort key1:=Range("A1"), order1:=xlDescending

End Sub

key表示以哪一列为排序对象，order为xlDescending是为降序排序，为xlAscending时为升序排序。

Merge方法：Merge方法可以合并Excel中的单元格，利用Range().Merge属性即可以将单元格区域中的单元格合并，合并后的单元格的地址，自动等于参与合并的单元格首个单元格的地址。例如，将A1:B5区域合并单元格，可以写成：

Sub M1()

Range(“A1:B5”).Merge

End Sub

MergeCells属性：如果单元格是合并以后的单元格，则单元格的Merge属性为Ture，否则为False，利用这个属性可以把已经合并的单元格进行拆分。例如，上面的命令将A1到B5单元格区域合并为一个单元格，现在需要拆分，只需要下列命令即可：

Sub M2()

Range(“A1”).MergeCells=False

End Sub

Resize属性：Resize属性返回一个改变大小的Range区域，Resize属性的用法是，Resize(行数,列数),表示以原来区域第一个单元格为标准，将原来Range区域改变为行数和列数的新区域，例如Range(“A1:B5”).Resize(3,5)，表示以原先单元格区域A1:B5第一个单元格A1为基准，将区域大小修改为3行和5列，修改后的区域为A1:E3。

图4.8 Range对象的Resize属性

利用Resize属性，求任意一个选定单元的每一行数字和。我们可以利用Range对象的Resize属性以及FormulaR1C1属性编写程序实现对选定单元每一行求和运算。

Sub AllSum()

Dim Rng As Range, Rng1 As Range

Dim N As Integer

Set Rng = Application.InputBox("选择区域", "进行求和", , , , , , 8)

N = Rng.Columns.Count

Set Rng1 = Rng.Resize(Rng.Rows.Count, Rng.Columns.Count + 1)

Rng1.Columns(N + 1).FormulaR1C1 = "=Sum(RC[-" & N & "]:RC[-1])"

End Sub

程序中，先使用Application.InputBox输入一个单元格区域，并把这个单元格区域赋值给Range变量Rng，然后再定义一个Range变量Rng1，让Rng1等于Rng列数增加1，行数不变的新区域。然后再利用FormulaRC1属性计算每一行的和，而对于FormulaRC1来说，因为用户输入的区域行数和列数都是不确定的，所以这里，第一个单元格的相对列偏移量应该等于之前Rng变量的列数的相反数，这里用一个变量N来对Rng变量的列数进制保存，然后利用字符串的& 符号将常量“RC[-“与列偏移的变量N相连。而最后一个单元格列偏移量一定等于-1。读者可以自己试着写一个求每一列和的程序。

Delete方法：Delete方法可以用于删除Excel表的某一行或者某一列，在运用这个方法的时候，有一个小小的陷阱，即在行删除之后，后面的行数会自动减去1，即删除前的第2行，在删除之后就变成了第1行。例如，下面程序不是删除Excel的1到20行，而是删除了第1、3、5、7、9……39行：

Sub Del()

Dim i As Integer

For i =1 to 20

Rows(i).Delete

Next i

End Sub

那么如何才能删除前20行呢？第一个方法，删除第一行，删除20遍。第二个方法，采用逆向删除，先删除1第20行，然后删除第19行……。

删除前20行方法1。

Sub Del20()

Dim i As Integer

For i =1 to 20

Rows(1).Delete

Next i

End Sub

删除前20行方法2

Sub Del20()

Dim i As Integer

For i =20 to 1 Step -1

Rows(i).Delete

Next i

End Sub

逆向删除时，由于删除操作不会对前面的行产生影响，所以可以用Rows(i).Delete

Seletion方法: Selection方法完成对Range对象的“选定”操作，这种操作可以允许使用一次性操作不同区域的Range对象。例如可以用Section方法计算选中单元格中所有数字的和。

Sub S()

Dim i , S1 As Double

For Each i In Selextion

S1=S1+i

Msgbox(S1)

最后讲解一下Range对象的Value属性，Range对象的Value属性即Range对象的值属性，即单元格或者单元格区域中的内容。Range对象的Value属性是Range对象的默认属性，以上对Range对象的赋值过程或者访问过程其实都是利用了Range对象的这个属性，只不过由于Value属性是默认属性，所以我们这里并没有见到例如Range(“A1”).Value=5这样的表达形式。

第六节 在Excel表中相隔的区域输入序号

这个问题出自于对电话号码的打印和保存，假设我们现在用一个Excel表格保存电话号码，当然你可以每一行只保存一个人的电话号码，这样做将非常浪费Excel表格的行，阅读起来也相对困难。现在我们在每一个电话号码的前面加上一个“序号“，把Excel表格按照列分成若干的区域，每一个区域只保存20个电话号码。即A列到C列保存前20个人的电话号码，A列编号从1到20，然后D列到F列保存21号到40号的电话号码，D列编号从21到40……如果我们现在有100个电话号码，呢么请问如何快速地在A列、D列、G列、J列、M列输入这100个数字呢。

4.6.1 思路分析

现在我们设一个Range变量，这个变量为3列，20行，然后让这个Range变量从A列开始向右侧移动5次，每移动一次，就向变量的第一列写入数字。

这个思路听上去很棒，实际上也确实是一个非常不错的思路，那么，我们如何来实现这个思路呢？实现这个思路，我们先来看一看需要哪些“材料“，

首先，我们得有一个Range变量，这个Range变量可以通过命令Dim Rng As Range来实现，这个不难。

其次，我们需要一个循环变量，来计数一下这个Rng共循环了多少次，我们用i这个变量对Rng循环的次数进行保存，并且当i的数值达到6时，不再循环。

随后，我们需要一个变量表示1到100这100个数字，因为对于每一个Rng以及Rng循环的每一次，都需要输入这个数字，并且保证每输入一个数，这个变量自动增加1。现在，还剩下一个问题：如何向每一个Rng中的第一列输入连续的数？

所以我们还需要一个变量，表示Rng变量第一列的每一行。可以用RowI这样的变量来表示。

最后让我们把这个思路给串联起来。

（1）定义Range变量，用于整个大循环，定义变量i用于保存循环进行了多少次。

（2）定义RowI变量，用于给每个大循环中的第一列进行循环。

（3）定义整数N，用于表示现在需要输入的数字。

将这些思想串联起来之后，可以写出以下程序。

4.6.2 动手写程序

Sub Insert()

Dim Rng As Range, i As Integer, RowI as Integer, N As Integer

Set Rng=Range(“A1:C20”)

i=1

N=1

For i=1 to 5

For RowI=1 to 20

Rng.Cells(RowI,1)=N

N=N+1

Next RowI

Set Rng=Rng.Offset(0,Rng.Columns.Count)

Next i

End Sub

这个程序看起来稍微比之前长了一些，但基本框架还是之前的那些，并且，其基础的命令也没有发生什么变化，这里，读者只需要知道这个For……Next是一种循环结构，循环结构将在第6章的部分详细讲解。这里看到，利用Offset属性将范围重新选定的方式确实发挥着超级高的效率。

4.6.3 另一个例子

在A列输入细胞培养的培养天数：在工作中，我们希望用一个Excel保存若干个摇瓶的细胞培养数据，由于整个Excel表格需要放不止一个摇瓶的细胞培养数据，所以我们想按照列的输入方式，在A列填入培养天数，剩下的按照摇瓶编号或者实验的先后顺序编号将数据输入Excel表格。这样A列的数据就需要反复输入，采用同上面思路一致的思路，现在假设我们培养天数需要连续重复输入20遍，每一遍的数字已经确认，为0到14，并且在每两遍之间空一行，那么，同样可以编写程序如下.

Sub Insert()

Dim i As Integer, j AS Integer,Rng As Range

Set Rng=Range(“A1:A15”)

For i =1 to 20

For j=1 to 15

Rng(j)=j-1

Next j

Set Rng=Rng.Offset(Rng.Rows.Count+1,0)

Next i

End Sub

这里可以少一个变量的原因是，我们每一次的输入都是从0重新开始，对于每一个Rng，第一个数字总等于0，第二个数字总等于1…… 所以，Rng的第j个数总等于j-1。

第七节 UsedRange与CurrentRegion对象

4.7.1 UsedRange对象

让我们来看一下这样一个问题，问题：将Excel工作表Sheet1中已有数据的列复制到与其相邻的区域。

聪明的读者已经认识到了，题目中并没有告诉我这个Excel表格里面那些列被写入了数据，我怎么定义这个变化的单元格区域对象呢？

在VBA中，UsedRange对象为当前表格使用的区域，这个区域的定义是这样的，不管区域中有多少空格，以左上和右下两个顶端有数据的单元格为界，都是UsedRange区域。

图4.9 UsedRange

也就是说，在上面这个图中，UsedRange起始的列为不是空列的第一列，即B列，结束的列为非空列的最后一列，即G列，开始的行和结束的行也是非空的第一行和最后一行。加入在J10的位置再写上数据，那么这个UsedRange就变成B2:J10。

有了UsedRange对象，我们就可以完成上述的任务了。

Sub U()

Dim Rng As Range

Set Rng=Sheet1.UsedRange.Offset(0,Sheet1.UsedRange.Columns.Count)

Sheet1.UsedRange.Copy Rng

End Sub

这里要注意的是，使用UsedRange时，一定要指明是在哪个工作表中，即使只有一个工作表也需要这样写！

4.7.2 CurrentRegion对象

与UsedRange对象不同的是，CurrentRegion对象返回Range对象中连续的列和连续的行组成的连续的“区域”。例如Range(“A1”).CurrentRegion的意思是，以A1单元格为基准的，具有数据的连续列和连续的行组成的区域。

图4.10 A1单元格的CurrentRegion

可以利用CurrentRegion选择有连续数据的单元格（即相邻的行或者列中单元格有数据），例如，求A1单元格有连续数据的单元格中的数字之和（在上图中是求方块中的数字，不计算方框外面的这个E4单元格。

Sub C()

Dim S As Integer, i

For Each i In Range(“A1”).CurrentRegion

S=S+i

Next i

MsgBox(S)

End Sub

第八节 Range对象的 Union、Intersect与End

4.8.1 Range对象的合集Union

Union的作用是将不连续的Range区域选择作为一个“整体”，将不连续的单元格区域作为整体操作的方式也可以用Range的方法来实现，例如：Range(“A1:B5”,”D4:E6”)，为A1:B5和D4:E6两个不连续的单元格区域，用Union方法表示就是Application.Union(Range(“A1:B5”),Range(“D4:E6”))。下面将不连续的两个区域背景色改为红色。

Sub U2()

Application.Union(Range(“A1:B5”),Range(“D4:E6”)).Interior.Color=vbRed

End Sub

4.8.2 Range对象的交集Intersect

Intersect的作用是求两个Range变量的交集。例如，Intersect(Range(“A1:F5”),Range(“E3:G7”))，结果是A1:F5区域与E3:G7区域的 “交集”即E3:F5区域。

图4.11 两个单元格区域的“交集”

利用Instersect求两个区域的交集，可以完成一些“看似不可能”完成的任务。例如，求用户选择的区域与Excel表中前两列交叉范围内的数字之和。由于不能确定用户只选取了前两列的数据，而计算中，两列后的数据对于计算是没有作用的。所以，这里要用Intersect。

Sub Calc()

Dim Rng1 As Range, Rng2 As Range, s As Double, i

Set Rng1 = Range("A:B")

Set Rng2 = Application.InputBox("选择区域", "", , , , , , 8)

Set Rng2 = Intersect(Rng1, Rng2)

For Each i In Rng2

s = s + i

Next i

MsgBox (s)

End Sub

4.8.3 End属性

Range对象的的End属性返回该对象所在行和列的“边缘”，其中可以有4个选项，即xlDown、xlUp、xlToLeft、xlToRight分别表示上、下、左、右边缘。读者可以任意用一组数据（记住B3单元格周围有数据实验一下效果）

Sub E()

Range("B3").End(xlDown).Interior.Color = vbRed

Range("B3").End(xlUp).Interior.Color = vbRed

Range("B3").End(xlToLeft).Interior.Color = vbRed

Range("B3").End(xlToRight).Interior.Color = vbRed

End Sub

将B3单元格不为空且上下左右“边缘”背景改为红色。这里可以看到，产生的效果是，以B3单元格所在的单元格为基准，向上、向下、向左、向右找到连续有数字的“最后一个”单元格。

还有一点，如果选择了一个空的单元格，那么寻找结果为第一个不为空的单元格。

第五章 数组和结构体

第一节 数组

5.1.1 什么是数组

在之前的章节中，我们已经学习到了如何去定义一个变量，那么假设现在我们需要100个整形变量，如何来声明这100个整形变量呢？这100个变量又如何给命名呢？

当然，可以通过写100个变量的名称来申明这100个变量，如果有时间的话，当然可以用例如a1、a2、a3……a100来一个一个命名。

但是，即使这样命名了，如何能够把这100个变量从第1个到第100个“重新阅读”一遍？或者如何让这100个整数分别等于1到100呢？是不是还需要写a1=1、a2=2……a100=100？

在许多计算机编程语言中，我们可以把像这样的一组变量类型相同的变量定义成一个“数组”所谓数组就是在计算机中连续储存的，相同类型的变量。在VBA中，当然也可以这样做了，即我们现在可以用一个“数组”来储存这100个变量，并且通过数组下标的方式来对数组的每一个元素实现一一遍历。

在VBA中，数组有两种形式，即定长数组和可变数组。

5.1.2 定长数组

(1) 声明数组的通常做法

所谓定长数组，就是数组元素个数确定的数组，在VBA中，声明一个定长数组最常用的命令是： Dim 数组名(N) As 类型。这里需要注意的是，在默认情况下，VBA中的数组是从0号元素开始的，所以数组的元素个数等于N+1。对于数组中的元素，可以用数组名(i)的形式访问，i表示数组的第i号元素，即数组的第i+1个元素。

例如： Dim Ary(6) As Integer，声明了一个整形数组，这个整形数组中有Ary(0),Ary(1)，Ary(2)、Ary(3)、Ary(4)、Ary(5) 、Ary(6)，共7个元素构成。例如，下面语句，声明了一个数组，并且把0到100共计100个数保存在是这个数组中。

Dim Ary(100) As Integer, i As Integer

For i =0 to 100

Ary(i)=i

Next i

(2)指定下标范围法：

在声明定长数组的时候，也可以指定数组的下标范围。例如，Dim B(2 to 6) As Integer。这个语句中的数组B的元素为 B(2)、B(3) 、B(4) 、B(5) 、B(6)，共计5个元素。

5.1.3 指定下标从1开始

VBA中也可以强制指定所有的数组下标的起始数字，使用Option Base 1语句即可以指定数组的下标从1开始。下面语句中定义了一个从A(1) 到A(100) 的100个元素的数组。

Optional Base 1

Dim A(100) As Integer

5.1.4 下标溢出

在VBA中，下表超过数组范围时，会引发”下标溢出”的错误，例如，Dim A(10) As Integer，定义了数组A，下标范围从0到10,如果现在语句中同时出现了A(11)这样的用法，则会发生数组下标溢出的错误。

但是有时候确实也不是我们自己想这样做的，可能完全忘记了自己之前的操作或者操作后发生了问题，例如，一个一个地判断这个数组A中的数字是否大于零，若大于0则放入另外一个数组B，最后输出B的最后一个元素。如果这个程序写成下面这样：

Sub GT0()

Dim A(10) As Integer，B(10) As Integer, i as Integer

for i =0 to 10

A(i)=Inputbox(“输入第” & i & “次”)

If A(i)>0 Then B(i)=A(i)

Next i

Msgbox(B(i))

End Sub

这一段看上去似乎没啥问题，确实“聪明”的我们也会说，是啊，我这个i不是变化的么？这个i应该始终是最后一个元素的序号啊。但是，事实情况并不是这样！这是因为这个循环语句的时候，是先把i的数字加上1，然后再判断i是否大于等于10的，所以，循环后的i不是最后一个元素的序号，而是最后最后一个元素的序号加上1！

5.1.5 可变数组

除了定长数组，VBA还可以支持变成数组，定义一个可变数组的方法是：Dim 数组名() As 类型。

可变数组并不可以直接拿过来就用了，在使用之前，需要重新声明一下可变宿主的长度，重新声明的方法为：

Redim [Preserve] 数组名 (N)

若使用Preserve，则表明保留之前的数组中的数，若不使用Preserve则原数组丢失。

例如：让用户随机输入若干数字，输入完毕后，计算用户输入的数字的平均数。

Sub Ave()

Dim Ary() As String, N As Integer, i As Integer, S As Double

N = 1

Do

ReDim Preserve Ary(N)

Ary(N) = InputBox("输入数字，#结束")

If Ary(N) = "#" Then Exit Do

N = N + 1

Loop

For i = 1 To N – 1

S = S + Ary(i)

Next i

MsgBox (S / (N - 1))

End Sub

这一段中，同样要注意数组长度的问题，在循环后，数组长度并不等于N，而是等于N-1.

通常用一个可变宿主接受一个字符串分割后的结果，（字符串分割后结果为一个字符串数组例）如下面一个问题，在Excel表格中的A列输入的内容为用逗号分隔开的省市，例如“安徽省,合肥市”，如何将这些省市分成两列表示。

Sub S()

Dim RngA As Range, RngB As Range, RngC As Range, i As Integer, Ch() As String

i=1

Set RngA=Range(“A:A”)

Set RngB=Range(“B:B”)

Set RngC=Range(“C:C”)

While RngA(i)<>””

Ch=Split(RngA(i),”,”)

RngB(i)=Ch(0)

RngC(i)=Ch(1)

i=i+1

Wend

End Sub

这里要注意的是，单元格里面的那个逗号务必与VBA中的Split函数中的逗号保持统一，即要么都是英语状态的逗号，要么都是汉语状态的逗号。

5.4 Range数组

其实Range变量也是一个数组，就像上面所看见的那样，一个Range变量可以用下标i直接访问这个Range区域中的单元格。Range数组下标的规定是：Range变量的左上角单元格（区域最左边最上面的单元格）为1号元素（Range数组没有0号元素，这里提醒注意！）然后向右，为2号元素……一行结束后，下一行继续编号。例如Range(‘B2:E4”)每个单元格的序号如下：

图5.1 Range(“B2:E4”)中单元格的编号

第二节 多维数组

5.2.1 什么是多维数组

多维数组是数组的延伸，即二维或者高维上的一个数组。例如一个班级学生的座位号，可以看成一个二维数组，即通过行数和列数两个数定义一个确定的单元。例如三维空间的任何一个点，都可以用（x,y,z）这样的三个数字表示。

5.2.2 多维数组的声明和访问

与一维数组相同，声明一个多维数组的方法也是Dim 数组名(N,M,L……) As 类型，数组总的元素个数等于每一个“维度”上的元素个数的乘积。例如：

Dim A(2,4) As Integer

声明了一个二维数组，数组中的数为整形，数组元素为A(0,0),A(0,1),A(0,2),A(0,3),A(0,4)

,A(1,0),A(1,1),A(1,2),A(1,3),A(1,4),A(2,0),A(2,1),A(2,2),A(2,3),A(2,4)，共计15个元素。

从这个例子也可以看到，对于二维数组中元素的访问，与一维宿主相似，二维数组需要用两个数组下标来对数组实现访问。

与一维数组不同，VBA中不可以直接定义可变多维数组。因VBA中自带了Range类型变量，可以借助Range变量来定义多维可变数组。

第三节 结构体

5.3.1 什么是结构体

通过上面的内容，我们讲述了如何通过数组定义类型一致的若干个变量，这里，我们讲述一下如何通过结构体定义有相互联系的变量。例如，我们平时用的最多的一种“结构体”就是通讯号码簿，对于每一个“联系人”均有一个“姓名“，一个”地址“，一个或者若干个联系电话等。然而这里我们可以定义一个特殊的“数据类型”：来储存这种特殊的结构，就把这种数据类型称为“结构体“。那么，我们首先要确定一下这种结构都有哪些具体的部分组成呢？

首先给这种”结构“取一个名字，叫做”通讯信息“通讯信息”包括下列组成部分

姓名 字符串型

性别 字符串型

年龄 整数型

职业 字符串型

地址 字符串型

电话号码 字符串型

明确了这些组成部分之后，我们就可以定义结构体了，这里，我们先说明一下两个术语，（1）结构体名，即这个结构体的名字，在上面的例子中，为“通讯信息”。（2）成员，结构体中的基本组成部分称为结构体的“成员”，上面例子中，“姓名”是“通讯信息”的一个成员。

在VBA中，结构体的定义是在Sub、Function以外的部分，整个模块开始的位置定义。定义结构体的命令是：

[Private | Public] Type Name

VarName1 As Type1

VarName2 As Type2

……

End Type

Private、Public，指明结构体是私有还是共有。Name：结构体的名字。VarName1：结构体中成员的名字。Type1：结构体中成员的类型。

例如上面的例子，用VBA声明结构体就是：

Type CommunInfo

Name As String

Gender As String

Age As Integer

Profession As String

Address As String

Tel As String

End Type

5.3.2 结构体赋值和成员访问

上面我们声明了一种“结构体”但是并没有声明这种结构体的一个变量，所谓声明结构体的一个变量就是声明一个变量，这个变量的类型为上述的这种“结构体”类型。声明一个结构体变量的方法同声明一个整形变量、字符串型变量一致。也就是说，一旦用户通过上面的语句定义了一种“结构体”类型后，这种由用户自己定义的类型就可以同系统中内置的类型一样使用了！

例如Dim A As CommunInfo 这样就定义了一个变量A，这个变量A为上面我们定义的CommunInfo类型。

对于这个结构体A的赋值，需要通过对其成员一一赋值来实现。

结构体的成员访问通过一个小点（即英文的句号）来实现，例如上面的例子中，A.Name实现对A结构体中Name这个成员的访问。

我们可以写成 A.Name=”LiLei” 这样就把A这个结构体变量的Name改为了“LiLei“。也可以另外定义一个这种”结构体”变量B，然后把A赋值给B，即B=A，这样就实现了把A中所有的成员赋值给B结构体了！

5.3.3 结构体数组

既然结构体也是一种类型，那么我们当然可以定义一个数组，这个数组的所有元素为“结构体”，定义这样一种“结构体数组”的方法就是Dim 数组名(N) As 结构体类型。可变数组时，不用N。

例如，我们可以通过一个“结构体数组”储存一下每天细胞培养的数据。

Type CellCult()

LCC As Single

Via As Single

Glu As Single

Lac As Single

End Type

Dim Cel1(14) As CellCult

这样就定义了一个数组Cel1，总共有15个元素，分别储存培养0到14天的数据，对于每一天的培养数据。用一个类型为“CellCult”的结构体来储存，这个结构体包括细胞密度LCC、细胞活率Via、葡萄糖浓度Glu以及乳酸浓度Lac四个成员。例如，对于培养第3天的葡萄糖浓度，表示为Cel1(3).Glu。

第四节 枚举类型

5.4.1 枚举

所谓的枚举就是一个一个地“举例”出来，例如“星期”可以枚举，不过从“星期日”到“星期六”。“月份”也可以枚举，即从“1月”到“12月”。

5.4.2 枚举类型

VBA中的枚举类型就是这样一种类型，即类型的数字范围已经确定的类型。枚举类型的好处：让用户只可以在枚举类型的范围内输入数据。在进行程序设计或者使用设计的程序的时候，往往不希望用户输入我们设想数值以外的数字，例如，在“星期”这样一个变量中，我们并不希望用户输入例如8、9这样的数字，也不希望用户输入例如2.3、5.4这样的数字。

VBA对于枚举类型的定义方法是：

Enum 枚举类型名

枚举名称1=常量1

枚举类型2=常量2

……

End Enum

这个定义方法与结构体的定义方法比较类似，声明一个枚举类型变量的方法也是Dim 变量名 As 枚举类型名。

例如，可以将一周的“周日”到“周六”列成枚举类型：

Enum Day

Sunday = 0

Monday = 1

Tuesday = 2

Wednesday = 3

Thursday = 4

Friday = 5

Saturday = 6

End Enum

这里可以观察到，在Sunday的后面有一个“=0“，Monday后面有一个”=1“……这里这个数字成为“枚举量“，即在一个枚举类型的变量a中，让a=Sunday和让a=0的效果是一样的。最后这个a的值均为0.

第六章 程序结构

第一节 程序的三大结构

6.1.1 顺序结构

所谓顺序结构，就是要求计算机按照一定顺序的做某一件事情。例如，先将A1单元格的背景颜色改为红色，再将A1单元格的字体改为斜体，再将A1单元格的字体加粗。程序可以这样写：

Sub S()

Range(“A1”).Interior.Color=vbRed

Range(“A1”).Font.Italic=True

Range(“A1”).Font.Bold=True

End Sub

当需要若干次用到Range(A1)的时候，可以用With-End With语句将这一段简写，简写后的程序为：

Sub S()

With Range(“A1”)

.Intetior.Color=vbRed

.Font.Italic=True

.Font.Bold=True

End With

End Sub

计算机在碰到With的时候，会自动将With后面的关键字加入到下面所有的第一个”.”符号左边。

6.1.2 分支结构

所谓分支结构，就是需要计算机根据具体的情况，执行具体的任务。例如，要求计算机根据A1单元格中的数的大小将A1单元格背景色进行修改，如果大于15则修改为红色，否则不修改。可以写程序如下：

Sub F()

If Range(“A1”)>15 then Range(“A1”).Interior.Color=vbRed

End if

当然分支结构还可以更加复杂一点，比如说，分支结构嵌套分支结构，让计算机先根据A1单元格里面的数决定是否修改背景色，如果修改了背景色，则根据A1单元格中的字体决定是否把A1单元格中的数字增加。

Sub F1()

If Range(“A1”)>15 then

Range(“A1”).Interior.Color=vbRed

if Range(“A1”).Font.Italic=True then Range(“A1”)=Range(“A1”)+1

Else

End If

End Sub

6.1.3 循环结构

所谓循环结构，就是让计算机循环反复做同样一件事情或者做类似的事情，直到循环结束。循环结构是计算机编程语言的灵魂所在，使用VBA对数据进行统计和整理，主要运用循环结构。例如，让计算机把A列中所有大于10的数字一一找出来，背景色涂成红色。

Sub C()

Dim i As Integer, Rng As Range

Set Rng=Range(“A:A”)

i=1

While Rng(i)<>””

If Rng(i)>10 then Rng(i).Interior.Color=vbRed

i=i+1

Wend

End Sub

6.1.4 流程图

所谓的流程图，就是用一些符号来表示计算机执行命令的过程。一般地，用一个方框表示顺序执行过程，用一个菱形表示分支执行过程，用箭头表达命令执行的方向。流程图是为了让初学者更好地理解计算机程序如何执行，VBA语句如何运行，在实际过程中并不存在，编程时也没有必要画出来。

以上面的这个程序为例子，说明一下这个程序是如何执行的。

第一步，声明i是整形变量，把Rng变量设定为A列所有的单元格，这样的话Rng(i)就表示A列中第i个单元格了。

第二步，将i的数值设定为1 （即i=1）

第三步，判断第i个单元格是否为空，如果不是空，则进入循环，否则跳出循环

第四步，判断第i个单元格的数是否大于10，大于10则将这个单元格背景涂成红色。

第五步，把i的数值加上1，然后返回第三步。

第六步，循环结束，程序结束。

这个程序流程图如下：

图6.1 流程图

第二节 分支结构

6.2.1 分支结构的用处

分支结构用于需要判断的地方，例如在一般顺序结构中的分支以及在循环结构中的跳出循环或者进入循环。

6.2.2 If…… Then…… Else ……语句

If …… Then …… Else……语句用于有两个选择分支的语句中，当If后面的条件为真（True）时，执行Then……后面的语句，否则执行Else后面的语句。

例如：输入一个数，判断这个数是否大于100，大于100则输出“大于100”，不大于100则输出“小于等于100”。

Sub N()

Dim A As Integer

A=InputBox(“输入”)

If A>100 Then MsgBox(“大于100”) Else MsgBox(“小于等于100”)

End Sub

6.2.3 If…… Then 结构

If……Then结构可以用于多个选择条件，选择条件结束后，需要写明 End If。这个结构的格式如下：

If 表达式1 Then

语句1

ElseIf 表达式2 Then

语句 2

……

Else

语句n

End If

这种结构的运行顺序，先判断表达式1，表达式1成立就进行Then后面的表达式，否则再判断表达式2……所有表达式均不成立，执行Else后的语句。

例如，根据学生成绩给学生写评语，90分以上为“优秀”，80分以上为“良好”，70分以上为“中等“，60分以上为”及格“，60分以下为”不及格“。

Sub M()

Dim Score As Integer, V As String

Score=InputBox(“输入”)

If Score>=90 then

V=”优秀”

ElseIf Score>=80 Then

V=”良好”

ElseIf Score>=70 Then

V=”中等”

ElseIf Score>=60 Then

V=”及格”

Else

V=”不及格”

End If

MsgBox(V)

End Sub

6.2.4 Select Case 结构

Select Case结构同样可以用于对多个分支的选择，这种结构的语句如下：

Select Case 表达式

Case 值1

语句1

Case 值2

语句 2

……

Case Else

语句 n

End Select

Select Case的作用是，根据表达式的数值，选择Case中相应数值后面的语句。当表达式不等于所列的所有的数值，执行Case Else的语句。

例如，某商场促销活动，根据顾客购买的某商品个数定单价，当购买1件商品时没有优惠政策，购买2~5件商品时，按照原价9折优惠，购买6~10件商品按照8.5折优惠,10件以上一律8折。假设某衬衫原价为30.5元/件，输入购买件数，输出总金额。

Sub S()

Dim P As Double, Account As Double, N As Double, T as Double

P=30.5

N=InputBox(“输入件数”)

Select Case N

Case 1

Account=1

Case 2,3,4,5

Account=0.9

Case 6,7,8,9,10

Account=0.85

Case Else

Account=0.8

End Select

T=P*N*Account

MsgBox(T)

End Sub

6.2.5 分支结构的嵌套

分支结构的嵌套确实是一件比较令人头疼的工作，各种一层套一层的If……Else语句时常让人摸不着头脑。遇到这种分支嵌套的时候，通常的解决办法是尽量简化嵌套的结构，可以利用其他的逻辑判断方法来完成多层的判断。

例如，判断一个年分是否为闰年。闰年的定义是：如果年份不能被100整除，则判断年份是否能被4整除，被4整数就是闰年，否则不是闰年；如果年份能够被100整除，则年分需要被400整数才是闰年。

如果按照这样的逻辑判断，那么写成两层判断结构就在所难免了。

Sub LeapYear()

Dim Leap As Boolean

Dim Y as Integer

Y=InputBox(“输入年份”)

If Y Mod 100 <>0 Then

If Y Mod 4=0 Then

Leap=True

Else

Leap=False

End If

Else

If Y Mod 400=0 Then

Leap=True

Else

Leap=False

End If

End If

If Leap=True Then

MsgBox(“闰年”)

Else

MsgBox(“不是闰年”)

End If

End Sub

当然计算机可以明白这么多的If…… End If是怎么回事，也知道该如何去做。但是，作为编写计算机程序的人来说，这么多的嵌套看上去就非常麻烦，而且很容易自己把自己给弄晕了。我们可以把上面的这么多的判断简化一下，关于闰年的判断方法，可以表述为：能够被4整除且不能被100整除的年份或者能够被400整除的年份为闰年，否则不是闰年。这样我们就避免了分支的嵌套。

Sub LeapYear()

Dim Leap As Boolean

Dim Y As Integer

Y=InputBox(“输入年份”)

If (Y Mod 4=0 And Y Mod 100<>0) Or Y Mod 400=0 Then

Leap=True

Else

Leap=False

End If

If Leap=True Then

MsgBox(“闰年”)

Else

MsgBox(“不是闰年“)

End If

End Sub

还有另一种方法避免分支结构的嵌套，即利用 If……Goto的方法，一般地，可以在程序段中加入一个行标，行标可以用英文字母加上数字构成，并在行标后加上冒号。但是，行标只是起到标识和引导的作用，并不会阻止程序继续向下执行，所以，如果需要退出则额外需要Exit Sub这样的命令。

例如，判断单元格A1中的数字大小和背景颜色。规则如下：数字小于10的时候，如果背景颜色为红色，则输出”安全运行“，否则输出”警告“；数字大于10的时候，背景色为红色则输出”危险“，否则输出”停止运行“。

Sub NC()

With Range(“A1”)

If .value<10 Then Goto L1 Else Goto L2

L1:

If .Interior.Color=vbRed Then MsgBox(“安全运行”) Else MsgBox(”警告“)

Exit Sub

L2：

If .Interior.Color=vbRed Then MsgBox(“危险”) Else MsgBox(”停止运行“)

End With

End Sub

这里 Goto的作用即让计算机直接跳到与Goto后面所列的行标相同的地方执行。

第三节 循环结构

6.3.1 循环结构和死循环

在计算机编程中，循环结构是使用范围最为广泛，使用频率作为频繁的结构。循环结构就是让计算机循环往复的做一件事情，直到达到某种目标。而往往由于循环结构设计的不好，导致计算机陷入某种循环，永远也循环不出来，这种情况叫做”死循环”。举一个日常生活中的例子。领导要求业务员打电话给某人，业务员拨通电话后，无人接听。这种情况很常见，我们一般会等一会再打，实在打不通还可以向上报告领导。但是，如果这件事情交给计算机做，由于计算机忠实的执行你所有的命令，所以它就会不知疲倦地打电话，一直没人接听就一直打，一直打到对方电话没电或者自己死机。这种一直打不通电话一直打的情况就是“死循环”。

由此可见，计算机一旦陷入“死循环”，往往文件损坏或者系统损伤等严重的后果。所以，在设计循环之前，需要认真阅读本章节的内容。

6.3.2 If……Goto循环

利用If……Goto语句可以做一个循环。因为在上一节已经说到，Goto语句的作用是让计算机跳转到某一个标识行，如果我们把标识行放在Goto语句的前面，那么计算机跳转到前面，然后再次顺序执行下来，又会跳转回去。为了能够使得计算机从这样循环往复的跳转中“解脱”出来，我们采用If语句进行判断，符合条件则跳转回去，否则不跳转，直接执行下面的代码。

例如，要求用户输入手机号码，首先一条，手机号码都是11位的，如果直接采用InputBox，用户可以任意输入若干个数字，所以我们这里可以采用一个循环结构，让用户输入11个数字，如果输入不是11个，则让用户重新输入。

Sub Tel()

Dim T As String

L1:

T=InputBox(“请出入号码，11位”)

If Len(T)<>11 Then Goto L1

MsgBox(“Tel:” & T)

End Sub

这个程序中，让用户输入一个电话号码，判断电话号码是否为11位，即If Len(T)<>11，如果不是11位，则跳转（Goto）到L1的位置，L1位于If…… Goto语句前面。所以完成了用户只能输入11位数才可以正常通过的设计。但是，这里有一个小问题，即用户还是可以输入11个字母，或者11个数字加字母，只要是11个“字符”就可以。那么，如何让用户只能输入11位数字呢？假设用户输入的都是手机号码，我们先不去验证这些手机号码是否正确，那么就需要在If语句后面添加一些条件。

Sub Tel()

Dim T As String

L1:

T=InputBox(“请输入号码，11位”)

If Len(T)<>11 Or CStr(Val(T))<>T Then Goto L1

MsgBox(“Tel:”& T)

End Sub

这个程序可以让用户输入开头不是0的连续11位数字。

6.3.3 For……Next循环

不得不说，If ……Goto循环是一个非常糟糕的循环，原因就在于Goto语句可以让计算机不加检查地跳转到程序的任何地方，这样往往造成思路混乱，产生程序编写者不希望的一些结果。

在循环次数已知的情况下，For……Next循环是一个不错的选择，在For……Next循环中，可以用Exit For来提前结束循环。

例如，计算1+2+4+8+……256。我们可以设计这样一个循环，让i分别从0一直循环到8，然后声明一个变量S，每一次循环就让S加上2的i次方（分别等于0,1,2，……256）

Sub TEST()

Dim S As Integer, i As Integer

For i =0 to 8

S=S+2^i

Next i

MsgBox(S)

End Sub

这一段程序需要解释的地方是这个For……Next里面的东西。For 这一行，首先i=0，表示变量i的初始值为0，to 8，表示循环结束的标准是i不大于8，For这一行后还可以加上Step表示每循环一次i的数值增加多少，默认Step为1，使用默认Step时可以省略这个Step。中间的部分，S=S+2^i，即每循环一次的时候需要执行的任务，这里是每循环一次，就给S加上2的i次方这个数。Next i 表示一个循环结束，i的数值按照Step进行增加，进行下一个循环。

可以利用For…… Next构建双重循环，双重循环即在一个For循环内部还有另一个For循环，双重循环运行的顺序是，先进性内部的循环，内部循环结束后，在进行外部循环。

例如，构建一个9*9乘法表。

Sub Multi()

Dim i As Integer, j As Integer, k As Integer, StrL As String

For i=1 to 9

For j=1 to i

k=i*j

StrL=StrL &j & “*” & i & “=” & k & Chr(9)

Next j

StrL=StrL & Chr(13)

Next i

MsgBox(StrL)

End Sub

这一段程序中，首先看到大循环i=1 to 9，然后是内部循环 j=1 to i，即对于每一个i，j的数值都从1循环到i。所以执行顺序是，首先i=1，执行循环j=1 to 1，然后 i=i+1=2，执行循环j=1 to 2……

Chr(9)和Chr(13)分别是制表符（Tab键）和回车符（Enter键），用于生成水平制表以及回车（换行）。

6.3.4 提前退出循环

有时候我们需要提前退出循环，例如，求A列前20个数的和，但当遇到空格的时候，提前结束求和，即空格下方的数据不参与求和。这时，我们可以用Exit For来提前结束循环。

Sub SIS()

Dim S As Double, i As Integer, Rng As Range

Set Rng=Range(“A:A”)

For i=1 to 20

If Rng(i)=”” Then Exit For

S=S+Rng(i)

Next i

MsgBox(S)

End Sub

6.3.5 For Each ……Next循环

For Each……Next循环用于循环个数不确定的情况，是对于一个集合中的所有元素进行循环。例如，在Range对象中，当我们不知道Range对象的大小时，可以用For Each……Next循环一一遍历Range对象中的单元格。例如，对于用户任意选定的区域，求选定区域中所有数字的绝对值之和。

Sub ASB()

Dim Rng As Range, R,S As Double

Set Rng=Application.InputBox(“选择区域", , , , , , , 8)

For Each R In Rng

S=S+Abs(R.Value)

Next R

MsgBox(S)

End Sub

6.3.6 While…… Wend循环

当知道循环结束的条件，而循环次数不确定时，可以用当前循环或者直到循环，While……Wend循环即为当前循环。While……Wend循环执行的顺序是，判断While后面的条件是否成立，如果成立则进入循环，否则跳出循环。

例如，输入一个数，现在需要将这个数转化为2进制形式表达出来。

在第3章求2进制的讲解中，我们知道，求一个数的2进制表达形式，就是不断地用这个数除以2，余数作为数的每一位，直到这个数等于0。

Sub Bin()

Dim N As Integer, Str As String

N=InputBox(“输入数字”)

While N<>0

Str=N Mod 2 & Str

N=N

Wend

MsgBox(Str)

End Sub

6.3.7 Do……Loop循环

有时候，确实需要构建一个“死循环”，即当循环的次数以及循环条件都不是非常确定时，我们采用这种循环。Do……Loop可以用于构建这种“死循环”构建这种死循环的时候需要注意，循环内部一定要提供跳出循环的条件语句，即Exit Do。

例如，做一个Excel工作簿保护程序，要求用户输入密码，当输入错误达到3次时，关闭Excel工作簿。（假设密码为123456）

Sub Pro()

Dim Str As String, T As Integer

T=1

Do

Str=InputBox(“请输入密码”)

If Str=”123456” Then

Exit Do

ElseIf T<3 Then

MsgBox(“密码错误！”)

T=T+1

Else

MsgBox(“输错3次了！”)

ThisWorkBook.Close

End If

Loop

End Sub

6.3.8 Do While …… Loop循环

Do While…… Loop循环也是当前循环，是当循环条件不达到时退出循环，While后可以添加循环条件。与Do……Loop循环不同的是，这里是先对循环条件进性判断，然后决定是否进入循环。

就上面让用户输入3次密码的程序，改写成Do While……Loop循环。

Sub Pro()

Dim Str As String, T As Integer

T=1

Do While T<=3

Str=InputBox(“输入密码”)

If Str=”123456” Then Exit Sub

MsgBox(“密码错误！”)

T=T+1

Loop

MsgBox(“输错3次了！”)

ThisWorkBook.Close

End Sub

6.3.9 Do Until……Loop循环

Do Until……Loop循环为直到型循环，即判断条件是否符合，符合条件退出循环，否则执行循环。

例如，让用户猜一个100以内的整数，让用户输入的数字大于目标数，输出“大了”，小于目标数，输出“小了”，直到用户出入正确。

Sub Game()

Dim T As Integer, A As Integer

Randomize

T=Fix(Rnd()*100)

A=-1

Do Until T=A

A=InputBox(“输入数字”)

If A>T Then

MsgBox(“大了”)

ElseIf A<T Then

MsgBox(“小了”)

End If

Loop

MsgBox(“对了！”)

End Sub

这里，Randomize的作用是，让每一次运行产生的随机数均不相同。首先给A赋值为-1的作用是随机数Rnd()可能产生的数为从0到1，乘以100以后，等于从0到100。若随机数刚好产生0，若A一开始没有赋值，其为0，用户不用输入则程序自动跳过循环体。

6.3.10 Do…… Loop While循环

Do……Loop While循环也是当前循环，与Do While ……Loop不同的是，Do……Loop While循环先进性循环，然后判断条件是否满足，满足条件则返回循环体。

例如，让计算机随机的写一些20以内的加法算式,当写到大于20的时候就结束。

Sub Cal()

Dim A As Integer, B As Integer, C As Integer

Randomize

Do

A=Fix(Rnd()*20)

B=Fix(Rnd()*20)

C=A+B

MsgBox(A & “+” & B & “=” & C)

Loop While C<=20

End Sub

运行一下，可以看到，这个程序每一次运行结果都不一样，每一次运行次数也不一样。

6.3.11 Do……Loop Until循环

Do……Loop Until循环为直到型循环，与Do……Loop While循环一样，这个循环也是先进性循环再判断条件。

例如上述程序，改成成Do……Loop Until结构：

Sub Cal()

Dim A As Integer, B As Integer, C As Integer

Randomize

Do

A=Fix(Rnd()*20)

B=Fix(Rnd()*20)

C=A+B

MsgBox(A & “+” & B & “=” & C)

Loop Until C>20

End Sub

第四节 VBA中的过程

6.4.1 Sub过程

早在第二章的时候，我们就已经认识到了VBA中Sub过程和Function过程两个过程。这里我们进一步对这两个过程进行解释。

（1） Sub过程

Sub是Subroutine（子程序）的简写，是由某个比较大的任务分解而来的小任务。在日常生活中，我们也可以遇到许多这种小任务，例如把“烧水泡茶”这项任务拆分，可以分为“接水”、“点火”、“准备茶具”、“抓茶叶”、“泡茶”等小任务。在VBA中，我们把一项任务看成一个“工程”，那么这项工程是由这些子程序，即Sub的过程组成。一项工程可以只有一个子程序，也可以有若干个子程序。

Sub过程只是简单地执行某项任务，并不能给我们某种“反馈”，即其并不可以返回一个具体的数值。要想得到某种“反馈”必须在Sub过程中运用某种方法把信息曾现出来。例如，显示一个对话框，让用户选择“是”或者“否”从而决定是否关闭Excel工作簿。

Sub Y()

Dim K

K=MsgBox(“是否关闭工作簿？”,vbYesNo)

If K=vbYes Then

ThisWorkBook.Close

Else

Msgbox(“没有选择关闭”)

End If

End Sub

(2) Sub 过程调用Sub过程

在Sub过程中，可以调用其他的Sub过程，这就好比我们平时可以一边接电话一遍记录电话内容一样。在一个Sub过程中，调用另一个Sub的方法是使用Call关键字，然后输入另一个Sub过程的名字。例如，我们先在A1单元格里面写入数字“2019”，然后再让这个单元格里面的数字加上1，显示出来。可以定义两个Sub，一个是向A1中写2019这个数，另一个，是让A1中的数加上1显示出来。然后用后面一个过程调用前面一个过程，这样你不需要调用单独调用前面一个过程，前面一个过程就已经自动执行了。

Sub W1()

Range(“A1”)=2019

End Sub

Sub W2()

Dim a As Integer

Call W1

a=Range(“A1”)+1

MsgBox(a)

End Sub

在点击“运行”的时候，直接选择运行W2过程，而不运行W1过程。可以看到，运行W2过程的同时，W1过程也得到运行。即同时在A1单元格输入数字2019以及显示一个对话框。

（3）有参数的Sub过程

参数就是在进行某项任务时，与任务有关的，但可以改变的一些数值，例如，“跑步100米”这项任务中，“跑步”是要做的任务，100米是这项任务的“参数”可以把100米改成1000米，“跑步”的任务任然没有发生变化。在Sub过程中，可以引入一些“参数”，这些参数提供给Sub过程某种“目标”。例如，用Sub过程对两个数进行“交换”需要交换的两个数就是“参数”。我们一起来看一下这个“交换”的过程。

首先，假设现在有两个数a和b，让a=1，b=2。任务是：交换以后，a的数值变成2，b的数值变成1. 读者可以先想一下怎么做？

也许你会觉得简单，直接让a=b，然后b=a就好了。仔细想一想，就会发现这样不对！因为，当a=b的时候，计算机把b的数值赋值给a（记住，=是赋值符号！），现在a和b就都等于2了！然后再让b=a，b的数等于没有变！这样看来计算机编程还真是需要一点逻辑的！

这里，我们需要运用一个临时的变量T来保存一下a的数值，然后把b的数值赋值给a，再回过头来把T的数值赋值给b。就像这样T=a a=b b=T。现在运用计算机的思维，第一步T=a，T的数等于1，第二步，a=b，a的数等于2，第三步，b=T，b的只等于1.这样就完成了交换，于是，这个Sub过程这样写。

Sub Swar( a As Integer, b As Integer)

Dim T as Integer

T=a

a=b

b=T

MsgBox ("a=" & a & ",b=" & b)

End Sub

Sub Test()

Dim a As Integer, b As Integer

a=1

b=2

Call Swar(a,b)

End Sub

这里，我们看到，调用一个有参数的Sub过程方法是，Call关键字加上名称再加上需要的参数。就像Call Swar(a,b)这样，这一句意思是，让Test这个过程中的a作为参数传递给Swar这个过程中的a，Test过程中的b作为参数传递给Swar过程中的b。

(4) 参数地址传递和值传递

如果我们在上述程序中添加一个MsgBox显示一下Test中的a和b的数值：

Sub Swar( a As Integer, b As Integer)

Dim T as Integer

T=a

a=b

b=T

MsgBox ("a=" & a & ",b=" & b)

End Sub

Sub Test()

Dim a As Integer, b As Integer

a=1

b=2

Call Swar(a,b)

MsgBox ("a=" & a & ",b=" & b)

End Sub

运行一下，可以看到，在Test过程中，a和b的值也发生了交换。这是因为这个Swar过程中的两个参数是传地址引用的。如果我们把这段程序简单修改一下：

Sub Swar( ByVal a As Integer,ByVal b As Integer)

Dim T as Integer

T=a

a=b

b=T

MsgBox ("a=" & a & ",b=" & b)

End Sub

Sub Test()

Dim a As Integer, b As Integer

a=1

b=2

Call Swar(a,b)

MsgBox ("a=" & a & ",b=" & b)

End Sub

只是简单地加上了两个词，ByVal，再次运行，读者会发现，两次的MsgBox中，只有第一次的时候a和b的数值发生了交换，第二次并没有发生交换！

细心的读者可能会问这是为啥。简单地来说，第一段Swar中参数a和b为传地址，第二段a和b为传值。何为传值何为传地址？

简单介绍一下，在计算机中，对于某一个数的存储，其用到一块内存，这一块内存在计算机的内存中具有一个编号，这个编号就是这块内存的“地址”。现在不妨把计算机内存当作“旅社”地址就是这个旅社中的房间号码。

举个例子，上面这个变量a，在计算机会开辟有一块内存空间，这一块内存空间的名字叫做a，a的数值等于1，也就是内存中所储存的东西为数字1，而这一块内存空间在计算机内存中的编号是不会发生变化的，假定为H10000001。

现在如果运用传地址的工作方式，则计算机在执行Swar这个过程的时候，看到a，就直接找到内存的H10000001位置，然后对其进行操作。而如果采用传值的工作方式，计算机在执行Swar这个过程的时候，并不是找到内存的H10000001位置直接进行操作，而是“复制”这个地址里面的数值，也就是1到另一块内存里面，然后再对另一块内存进行操作。

在上述的例子中，第一段程序里面没有ByVal关键字，系统将自动按照“传地址“方式进行工作，（默认关键字为ByRef，可以不写），而第二段程序中加入了关键字ByVal，系统将按照”传值“方式进行工作。(图6.2、6.3)

图6.2 传地址（红色为函数过程，黑色为传参数过程）

图6.3 传值

6.4.2 Function过程

与Sub过程不同的是，Function过程可以返回一个具体的“数值”，Function这个单词即英语中的“函数”，VBA的Function过程也就是数学里面的“函数”，像Excel工作表中的“函数”一样，Function过程也可以返回一个具体的数值。接下来我们看几个例子说明这个过程的用法。

（1）定义一个Function过程，求两个数中较大的一个数。

Function Max( A As Integer, B As Integer) As Integer

If A>B Then

Max=A

Else

Max=B

End If

End Function

这里我们可以看到已经熟悉的If条件语句，这个函数编写完之后，打开Excel工作簿，可以直接在任意一个单元格里面写上“=Max（”可以看到，我们自己写的这个Function函数可以像Excel内在函数一样使用。在Excel中使用自己定义和编写的函数与使用内置函数的过程是一样的）函数的返回值等于这个函数的名称，即这个函数名为Max，函数最后的数值等于在函数中的Max这个变量的数值（定义了Max为函数名的同时也就定义了Max这个变量了，没有必要在函数中再次定义Max变量）。

（2） 定义一个Function过程，求一个单元格区域中最大的数字

Function MaxM(Rng As Range) As Double

Dim R

MaxM=Rng.Cells(1,1)

For Each R In Rng

If R.Value>MaxM Then MaxM=R.Value

Next R

End Function

函数既然是一个“过程”那么其也可以运用例如For，For Each等循环或者分支结构。这里先对MaxM赋初值为单元格区域的第一个单元格的数字，然后一一比较MaxM与单元格中数值的大小，并把较大的数赋值给MaxM。

（3）定义一个Function过程，要求函数有一个可选参数，参数选择True时返回单元格区域的最大值，参数选择False时，返回最小值。

Function M(Rng As Range, Optional OP As Boolean=True) As Double

Dim R

M=Rng.Cells(1,1)

For Each R In Rng

If OP=True Then

If R.Value>M Then M=R.Value

Else

If R.Value<M Then M=R.Value

End If

Next R

End Function

这个函数在Excel中运用时，如果不加第二参数，则返回一个单元格区域的最大值，例如”=M(A1:A8)”将返回A1到A8区域中的最大值。如果加上第二参数，并且第二参数为False则返回该区域的最小值，例如“=M(A1:A8,False)”。

（4）定义一个函数，现在要求一次性返回两个数，第一个数表示单元格区域的大小（共有多少个单元格）第二个数表示这些单元格中的数字之和。

Function MySum(Rng As Range)

Dim S As Double, N As Integer,R

Dim A(1) As Double

For Each R In Rng

N=N+1

S=S+R.Value

Next R

A(0)=N

A(1)=S

MySum=A

End Function

在使用这种一次返回一个数组的函数时，需要根据返回数组的大小选定单元格（本例子中为选定两个单元格）然后输入“=MySum（”，再使用Shift+Ctrl+Enter方式就可以完成对这个公式的运用了。

（5）ParamArray，ParamArray可以让函数有一个不确定参数个数的参数形式。例如，我们随机选择许多个单元格区域，并且求所有区域中的所有单元格数字之和。

Function SumAny(ParamArray Group())

Dim S As Double,i,j

For Each i In Group

For Each j In i

S=S+j

Next j

Next i

SumAny=S

End Function

这个函数中，使用两个循环变量对单元格进行遍历，第一个变量i用于遍历所有的选定单元格区域，第二个变量j用于遍历每一个单元格区域中的每一个单元格。

（6）函数的调用

函数的调用和Sub的调用相似，函数的调用不需要用Call关键字，可以直接调用。例如，我们可以写一个拆分一个合数为两个素数的函数，这个函数又调用到判断一个数是否为素数的函数。

Function IsPrime(N As Integer) As Boolean

Dim i As Integer

IsPrime=True

For i =2 to N-1

If N Mod i =0 then IsPrime=False: Exit For

Next i

End Function

Function Gid(N As Integer)

Dim A(1) As Integer, i As Integer

For i =2 to N-2

If IsPrime(i) And IsPrime(N-i) Then A(0)=I : A(1)=N-I : Exit For

Next i

Gid=A

End Function

这里，Gid直接调用函数IsPrime。

（7）函数的递归

记得在大学信息技术考试的时候，其中一道名词解释题目就是“递归”。我的答案是这样写的：

递归：递归。

也许有人要问，你这不是没有解释么？能获得分数么？结果是，我这个名称解释被老师评为“最为恰当的名词解释”。即递归就是“递归”本身。相信读者看到这里会觉得一头雾水，那么我们就给出一个比较通俗一点的解释。

用通俗一点的解释来说，递归就是一个函数自己解释自己，自己调用自己的过程。在生活中可以举一个例子，小明有一个姐姐和一个哥哥，已知道小明去年12岁，小明的姐姐比小明大2岁，小明的哥哥比姐姐大3岁，问小明哥哥的年龄。

这个例子中，我们可以进行如下推导：

第一，要求哥哥的年龄，需要知道姐姐的年龄，哥哥的年龄=姐姐的年龄+3

第二，求姐姐的年龄，需要知道小明的年龄，姐姐的年龄=小明的年龄+2

第三，求小明的年龄，小明去年12岁，于是小明的年龄=12+1

再次推到哥哥的年龄，哥哥的年龄=姐姐的年龄+3=小明的年龄+2+3=12+1+2+3=18.

这种根据一个基础条件不断向后推导的过程就叫做“递归”，在计算机中，可以自动完成这种推导的过程。

举个例子，斐波那契数列中，第一个数和第二个数都是1，以后每一个数等于前面两个数的和。于是，我们可以得到这样的数列： 1,1,2,3,5,8,13,21,34……如果用VBA求这个数列的第N个数。则可以写成这样的函数

Function Fib(N As Integer) As Integer

Dim F As Integer

If N=1 Or N=2 Then

F=1

Else

F=Fib(N-1)+Fib(N-2)

End If

Fib=F

End Function

这个函数Fib中，运用了函数的递归调用。我们来看一下这个函数，如果输入Fib(4),VBA会干那些事情。

第一步，计算Fib(4)。判断N的数值是否等于1或者2，现在N=4，不等于1，也不等于2,所以执行F=Fib(N-1)+Fib(N-2)，即F=Fib(3)+Fib(2)

第二步，计算Fib(3)。现在N=3，执行F=Fib(2)+Fib(1)

第三步，计算Fib(2), 等于1，于是现在变成F=Fib(2)+Fib(1)+1

第四步，计算Fib(2), 等于1，现在变成F=1+Fib(1)+1

第五步，计算Fib(1), 等于1，现在变成F=1+1+1=3

第六步，执行Fib=F，所以最后函数Fib的数值等于3.

（8）利用函数完成高等数学中“无穷大”的思想

计算上面这个斐波那契数列后面一项与前面一项的比值，看一下当N趋近于无穷大的时候这个比值为多少。

计算机并不会知道无穷大这个要怎么计算，我们唯一的方法，是让计算机计算很多次，每两次的数值进行比较，如果两次数字相差小于一个非常小的数，就认为我们执行到了“无穷大”次。

在上述Function的基础上再添加一段代码

Function P() As Double

Dim N As Integer

N=1

While Abs(Fib(N+1)/Fib(N)-Fib(N+2)/Fib(N+1))>1E-5

N=N+1

Wend

P=Fib(N+1)/Fib(N)

End Function

与上述的函数不同的是，这个函数并没有“参数”，然而却不妨碍这个函数的运行，在Excel的单元格里面输入“=P()”，按下确定。

可以看到，这个比值为1.618. 说明斐波那契数列后面一项与前面一项的比值，随着项数的增加，无限趋近这个数字。可能对于某些读者来说，1.618是一个非常熟悉的数字，没错这个数字就是黄金比例0.618的倒数，也就是说，斐波那契数列的前后两项比值无限趋近于黄金比例。

利用相同的原理，我们也可以自定义一个计算自然数对数底e的函数。

Function SumE(N As Integer) As Double

Dim I As Integer

For I=1 to N

SumE=SumE+1/ Application.WorksheetFunction.Fact(N)

Next I

End Function

Function E() As Double

Dim N As Integer

N=0

While Abs(SumE(N+1)-SumE(N))>1E-5

N=N+1

Wend

E=SumE(N+1)

End Function

但是这个函数并不像之前那个P()表现的这么好，数值等于3.0了都。这是因为我们这个算法不够精确导致的。因为计算机中对于一个小数的存储和运算时，总会丢失小数后若干位。所以这样的算法并不能得到希望的解。现在我们把这个函数改写如下：

Function EN(N As Long) As Double

EN = (1 + 1 / N) ^ N

End Function

Function E()

Dim N As Long

N = 1

While Abs(EN(N + 1) - EN(N)) > 0.00001

N = N + 1

Wend

E = EN(N + 1)

End Function

再次运行，这一次的数值等于2.7146，比之前精确许多。读者可以自行想一下如何写一个计算圆周率的函数。

第七章 代码实战

第一节 算法

7.1.1 什么是算法

算法是计算机程序的灵魂，是实现某种特定工作的“方法”。假设以日常做菜为例，我们总是按照一定的步骤（例如按照菜谱）将油盐酱醋等材料进行“加工”。其中，这种步骤（菜谱）就是做这道菜的“算法”。可见，尽管油盐酱醋等材料是一样的，但是由于做法（算法）的不同而可以呈现不同的风味。

7.1.2 计算机中对程序的描述

数据+算法=程序。在计算机中，数据和算法是不可分割的两部分，根据数据的结构不同，算法也曾现千差万别。一个算法往往只能适应于一种数据结构，或者几种数据结构，当数据结构发生变化的时候，算法需要发生相应的变化。例如，可以用3个介于0~255之间的数字来表示一种RGB颜色。现在如果数据增加到了4个，用4个数字表示颜色，就需要用到CMYK系统了。在Excel中，定义了某列或者某行作为数据的一个索引，现在如果需要改变索引的位置，往往需要重新构建VBA代码。

第二节 数理统计基础知识

7.2.1 名词解释

数理统计：数理统计是以概率论为基础，研究社会和自然界中大量随机现象数量变化基本规律的一种方法。

事件：就是发生的某一件事情。例如，“火车以300公里的时速前进”，“水烧到100℃沸腾”等都是事件。事件分为：绝对事件和概率事件，绝对事件是指，在某种情况下绝对会发生的事件，或者在某种情况下绝对不可能发生的事情，例如“在一个标准大气压下，纯水在100℃时达到沸腾”就是一个绝对事件。概率事件是指，在一定情况下，某种事件可能发生或者可能不发生，例如，“抛一枚硬币，正面向上”。

概率：概率事件发生的可能性，叫做概率事件的“概率”。例如上述例子中，抛硬币的结果，可能是正面向上，也有可能是背面向上，”正面向上“这件事情发生的概率为1/2。

试验：为了观察事件而进行的动作，例如，掷一个骰子。每掷一下就是一个“试验”。

观察：某一次试验的具体结果。

总体：所研究的对象所有观察结果。

样本：从总体中抽取出来的，用于了解总体情况的一部分观察。

样本空间：即样本中的观察的次数。

统计量：样本中的观察值经过一定的计算得到的，可以反映总体在某些方面具体情况的一个数值。

估计：样本的一个统计量，用于对总体的某方面特征进行评估。

无偏估计：该统计量在样本空间无限大时与总体的特征相互一致。

描述性统计：用于描述样本所在总体的某方面特征的统计过程。

统计推断：用于对不同样品所在总体方面的某种特征进行推断的过程。

描述性统计

描述性统计用于对样品所在总体的某一方面特性进行描述，描述性统计一般牵涉到对总体的分布、平均数、方差、峰度、偏度等方面的描述。

7.2.2 描述性统计

描述性统计是描述总体的某一种特征的统计过程。在描述性统计中，非常重要的一个概念是“分布”。

观察值与其取值时的概率有着一定的联系，这种联系在数理统计中以分布的形式表达出来。下面举一些日常生活中常见的例子说明。

（1）离散变量的概率分布

掷一个骰子，正面向上为1到6的概率均为1/6，那么这个出现的点数和概率之间的关系可以用下表表示：

点数123456

概率1/61/61/61/61/61/6

两个骰子，正面向上的点数之和与概率之间也有一定的关系，这种关系如下：

点数23456789101112

概率1/362/363/364/365/366/365/364/363/362/361/36

以上举的例子，事件A和其发生的概率之间的这种一一对应的关系就叫做分布列（分布率）

以上的事件A可以成为离散型变量事件，即这个观察值可以一个一个地“列举“出来，下面我们再看两个例子，看一看连续型的观察值事件。

（2）连续变量的概率分布

电灯泡的使用寿命t（小时）与其发生的概率之间的关系。

第一，这种关系，正确表述应为”电灯泡至少工作t小时与概率间的关系“。由于电灯泡的工作时间是一个连续的变量，即在一定分范围内，可以取任意的数值。所以电灯炮恰好工作t小时，这样的事件在日常生活中是不会遇到的，在理论上，一个连续变量在某一点的概率也为0.

第二，我们将这种电灯泡至少工作T小时发生的概率表述为下面这样的表达式：

即电灯泡至少工作T小时的概率等于一个函数f(x)在负无穷大到T上的积分。把函数f(x)成为分布函数，简称分布。事实证明，这个例子中，f(x)具有下列表达式的形式：

第二个例子是数理统计的一个最为基础的公式，即平常实验中我们所得到的实验数据，在一般情况下都是服从正态分布的，正态分布的分布率为：

若一个变量服从正太分布，正态分布函数公式中，μ为总体的平均数，σ为总体的方差。可以简记为N(μ, σ )

（2）分布函数的特征

分布函数具有以下特征：

分布函数总是大于或等于零。

分布函数在负无穷大到正无穷大上的积分等于1

（3）平均数

用于描述总体中观察值的“均值”。在数理统计中，“平均数”一词包含我们平时所说的“算数平均数”，但不仅仅指代“算数平均数”。平均数还有如下的形式：

算术平均数：即我们平时所说的平均数，等于一组数的和除以个数。算数平均数用M或者M1表示。

平方平均数：一组数中每一个数的平方和除以个数再开方。平方平均数用M2表示。

调和平均数：个数除以一组数中每一个数的倒数之和，调和平均数用H表示。

众数：一组数中出现次数最多或者频率最高的数，众数一般用Mod表示。

中位数：一组数按顺序排列，位于中间的数。当这组数个数为奇数时，即为中间位置的数，当个数为偶数时，等于位于中间位置的两个数的算数平均数。

需要指出的是，当变量服从正态分布时，其算术平均数=众数=中位数。

（4）方差和标准差

标准差用于描述一组数的离散程度。其定义是，一组数与其平均数差的平方平均数。方差定义是：标准差的平方。

在总体中，总体的标准差等于

通常，用样本的方差来估计总体的方差，样本的方程公式为：

（5）偏度、峰度

偏度用于描述一个分部与正态分布相偏的程度，峰度用于描述一个分部中心聚集的程度。这两个统计量的公式这里不再给出。

（6）求一组数的众数VBA程序

现在给出一组数字，求出这一组数字的众数。根据上面的定义，众数就是出现次数最多的数字。那么这个程序要怎么写？

最简单的想法，先让众数等于第一个数，把数组中全部等于这个数的数字给“数”一遍；然后如果后面的数字的出现个数大于前面的数，就把众数置换为第二个数字。

Function M(Rng As Range)

Dim N As Integer, i As Integer, j As Integer, Tmp As Double, T1 As Integer, T2 As Integer, mt As Double

N = Rng.Columns.Count * Rng.Rows.Count

T2 = 0

mt = Rng(1)

For i = 1 To N

Tmp = Rng(i)

T1 = 0

For j = 1 To N

If Tmp = Rng(j) Then T1 = T1 + 1

Next j

If T1 > T2 Then T2 = T1: mt = Rng(i)

Next i

M = mt

End Function

但是，这个程序有着明显的一个漏洞，即如果后面的数字的个数等于前面的数字，那么众数并不会改为后面的数字，而是始终保持前面的数字。

读者可以想一想如果遇到有相同个数的数字时如何处理？

（7）求一个数组的中位数的VBA程序

求一个数组的中位数，首先需要对这个数组进行排序。下面我们讲解排序的一个经典的算法：冒泡算法。

冒泡算法：根据“轻者向上，重者向下”的原理，完成对数据的排序。现在不妨假设一个简单的序列3、5、1、9、6、7。共6个数字。冒泡算法可以有两种算法，一种是固定比较，一种是移动比较。我们先说一下固定比较的算法：

第一轮，第一个数字与后面所有的数字进行比较，如果第一个数字大于后面的某一个数字，就把第一个数字与后面该位置的数字做交换。于是，第一个数字为3，第3个数字为1,3大于1，将3和1做交换，变成了一个新的序列：

1、5、3、9、6、7

第二轮，用第二个数字与后面的数字进行比较，如果大于则交换位置，于是5和3进行交换，序列变成：1、3、5、9、6、7

……

如此进行五轮交换，最后得到排序的结果1、3、5、6、7、9.

移动比较算法：

第一轮，用第一个数字与第二个数字进行比较，第二个数字与第三个数字进行比较，……若大于则交换，于是变成了：

3、1、5、6、7、9

第二轮，再用第一个数字与第二个数字进行比较，第二个数字与第三个数字进行比较……

……

如此进行5轮比较，得到排序结果。

排序以后，如果数组个数为奇数，则直接取（数组个数+1）/2位置上的数即可。如果数组个数为偶数，则取 （数组个数/2位置上的数+数组个数/2+1位置上的数）/2.

Function Med(Rng As Range)

Dim A() As Double, N As Integer, i As Integer, j As Integer, Temp As Double, R

N = 1

For Each R In Rng

ReDim Preserve A(N)

A(N) = R.Value

N = N + 1

Next R

N = N – 1

For i = 1 To N – 1

For j = i + 1 To N

If A(i) > A(j) Then Temp = A(i): A(i) = A(j): A(j) = Temp

Next j

Next i

If N Mod 2 = 1 Then

Med = A((N + 1) / 2)

Else

Med = (A(N / 2) + A(N / 2 + 1)) / 2

End If

End Function

7.2.3 假设检验

假设检验是用于统计分析中分析某一个命题是否成立的方法。

假设检验中有也有一些专业术语：

原假设：即原命题，即需要统计分析的命题。记作H0。

备择假设：即当原命题被推翻的时候选择的假设，通常为原假设的否定形式。

概率p：即通过统计分析得到了原假设成立的概率。

显著水平Alpha：统计分析并不能完全证实或者证伪原假设，通常情况下，我们按照“小概率事件在一次实验中出现的可能性为0”的假说对原假设进行接受或者否定。例如，天气预报中，如果明天下雨的概率小于0.0001，就会预报说不会下雨。同样的道理，我们设定一个比较小的概率值（通常为0.05或者0.01），当原假设的概率小于这个概率时，就推翻原假设，接受备择假设，若大于这个概率值，则接受原假设。这个人为设定的概率值就是“显著水平”。

显著、不显著：当原假设的概率值小于显著水平时，就说达到“显著”，否则为“不显著”。

第一类错误：也叫“拒真”错误，当统计结果根据p值拒绝了实际上成立的原假设时，为“第一类错误”，第一类错误也可以叫做“假阳性”错误。

第二类错误：也叫“纳伪”错误，即当统计结果根据p值接受了实际上不成立的原假设时，为“第二类错误”，第二类错误也可以叫做“假阴性”错误。

假设检验就是假设某命题成立，然后根据假设计算一个统计量，进而计算出这种假设成立的概率值，如果这个概率值小于某一个具体的数值（一般情况下为5%或者1%）就推翻原假设，接受备择假设。

（1）单样本总体均数的T检验

已知总体的均数，现抽样得到一个样本，判断样本是否来源于总体。这一类问题为单样本总体均数的T检验。下面举例说明。

在实际工作中，已知某机床正常工作时，加工出的零件直径平均数为10.5cm。现用该机床加工了10个零件，测得直径的数值分别为10.2、10.6、10.6、10.4、10.6、10.7、10.7、10.7、10.4、10.7。试问该机床的工作状态是否正常。

在单样本总体均数T检验中，我们需要构建一个统计量T，这个统计量T的公式如下：

根据公式可以编辑VBA代码如下：

Function TSingle(Rng As Range, u As Double) As Double

Dim A() As Double, Mean As Double, S As Double, SS As Double, df As Integer, N As Integer, i As Integer

N = 1

Dim R

For Each R In Rng

ReDim Preserve A(N)

A(N) = R.Value

N = N + 1

Next R

N = N – 1

df = N – 1

For i = 1 To N

S = S + A(i)

SS = SS + A(i) * A(i)

Next i

SS = SS - S * S / N

Mean = S / N

TSingle = (Mean - u) / (Sqr(SS / (df * N)))

End Function

统计过程，先做原假设为，该机器运转正常，即现在加工的零件直径与平均数无显著性差异。

根据上面的叙述和公式，以及VBA程序，可以计算出这个统计量T的数值。

计算结果，T值为1.108，然后再计算这个数值在T分布上的积分，即从无穷大积分到-1.108，以及1.108积分到正无穷。等于1减去从-1.108到1.108上T分布的积分（自由度为10-1=9），算出概率为0.2967,又0.2967大于0.05，所以接受原假设，即该机器运转正常。

（2）方差其次的两样本总体均数T检验

当一个样本扩展到两个样本的时候，统计量变得相当复杂。这时候应当做两种假设：两个样本所在的总体方差相等（其次）以及两样本所在总体的方程不等。

当两样本所在总体方差其次时，两样本总体均数T检验用来比较两样本所在总体均数是否相等，下面举例说明。

在技能大赛中，甲乙两名操作工进行零件加工的比赛，比赛分5轮进行。在规定时间内，甲完成的符合规范要求的件数为3件、4件、3件、5件、4件；乙完成的符合规范要求的零件数为2件、4件、4件、5件、2件。问甲乙两人在操作技术上时候存在一定的优劣差别。

在两样本总体均数T检验时，方差其次情况下，也是构建一个统计量T，T的公式为：

根据公式可以编写VBA代码如下：

Function TDEqual(rng1 As Range, rng2 As Range) As Double

Dim R

Dim S1 As Double, N1 As Integer, M1 As Double, SS1 As Double

Dim S2 As Double, N2 As Integer,M2 as Double,SS2 As Double

N1 = 1

For Each R In rng1

S1 = S1 + R

SS1 = SS1 + R * R

N1 = N1 + 1

Next R

N1 = N1 – 1

M1 = S1 / N1

SS1 = SS1 - S1 * S1 / N1

N2 = 1

For Each R In rng2

S2 = S2 + R

SS2 = SS2 + R * R

N2 = N2 + 1

Next R

N2 = N2 – 1

M2 = S2 / N2

SS2 = SS2 - S2 * S2 / N2

Dim SS As Double

SS =(SS1 + SS2) * (1 / N1 + 1 / N2) / (N1 + N2 - 2)

TDEqual = (M1 - M2) / Sqr(SS)

End Function

统计过程，原假设，甲乙两人技能无明显差别，即单位时间内甲完成的零件个数和乙完成的零件个数无显著差异。

根据我们编写的VBA代码进行计算，T值为0.566，计算出p值等于0.2，大于0.05，所以，甲乙两人完成零件的个数无显著差异。

（3）方差不齐次的两样本T检验

当两个样本所在总体方差不等的时候，需要用到方差不其次的样本T检验。方差不齐次时，统计量T的公式为：

根据公式可以编写VBA代码如下

Function TDDif(Rng1 As Range, Rng2 As Range)

Dim S1 As Double, N1 As Integer, SS1 As Double, M1 As Double

Dim S2 As Double, N2 As Integer, SS2 As Double, M2 As Double

Dim R

N1 = 1

For Each R In Rng1

S1 = S1 + R

SS1 = SS1 + R * R

N1 = N1 + 1

Next R

N1 = N1 – 1

N2 = 1

For Each R In Rng2

S2 = S2 + R

SS2 = SS2 + R * R

N2 = N2 + 1

Next R

N2 = N2 – 1

M1 = S1 / N1

M2 = S2 / N2

SS1 = SS1 - S1 * S1.N1

SS2 = SS2 - S2 * S2 / N2

TDDif = (M1 - M2) / Sqr(SS1 / (N1 * N1 - N1) + SS2 / (N2 * N2 - N2))

End Function

（4）方差其次性检验

方差其次性检验用来检验两个样本方差是否其次，用两个样本的均方差中较大的一个除以较小的一个，即可以得到统计量F，根据统计量F的数值在F分布中求得积分，就得到两组方差相等的概率值。

方差其次性检验的公式如下：

根据公式编写VBA代码

Function FValue(Rng1 as Range,Rng2 as Range)

Dim S1 As Double,SS1 As Double,N1 As Integer

Dim S2 As Double,SS2 As Double,N2 As Integer

Dim R

For Each R In Rng1

S1=S1+R

SS1=SS1+R*R

N1=N1+1

Next R

For Each R In Rng2

S2=S2+R

SS2=SS2+R*R

N2=N2+1

Next R

SS1=SS1-S1*S1/N1

SS2=SS2-S2*S2/N2

Fvalue=(SS1/(N1-1))/(SS2/(N2-1))

If FValue<1 Then FValue=1/Fvalue

End Function

（5）配对样本T检验

配对样本T检验用于统计配对样本之间的总体均数是否相等。在实验设计时，设计配对样本可以尽量的减少实验误差，消除个体差异。下距离说明。

为了验证某种饲料对小鼠的血红蛋白含量的作用，现将小鼠用普通饲料饲喂一周，同一只小鼠再用饲料A饲喂一周，采集得到不同饲料饲喂时，每只小鼠在不同饲料饲喂后血红蛋白含量（g/L）见下表，问饲料是否会增加小鼠血红蛋白含量？

表一

小鼠编号

1

2

3

4

5

普通饲料

102

127

110

105

120

A饲料

110

132

121

112

129

配对样本的T检验过程，是划归为单一样本的总体均数T检验问题来解决的。这个问题中，先将小鼠不同饲料饲喂情况下的血红蛋白的差值计算出来，然后再将问题简化为：一个样本的总体均数是否为0，统计量T的公式为：

根据公式可以编写VBA代码如下：

Function TParied(Rng1 As Range, Rng2 As Range)

Dim D() As Double

Dim S As Double, SS As Double, N As Integer, i As Integer

Dim R1, R2

N = 1

For Each R1 In Rng1

ReDim Preserve D(N)

D(N) = R1

N = N + 1

Next R1

N = N – 1

i = 1

For Each R2 In Rng2

D(i) = D(i) - R2

i = i + 1

Next R2

For i = 1 To N

S = S + D(i)

SS = SS + D(i) * D(i)

Next i

SS = SS - S * S / N

TParied = (S / N) / Sqr(SS / (N * N - N))

End Function

统计过程，假设饲料A对小鼠血红蛋白含量无显著影响，根据配对T检验公式，构造统计量，并计算出T值等于-8.0，然后计算自由度为4的T分布积分，得到概率值为p=0.002，小于0.05。所以推翻原来饲料对小鼠血红蛋白无显著影响的假设，饲料对小鼠的血红蛋白含量有明显的增加作用。

（6）单因素方差分析

单因素方差分析，用于3组或者3组以上的比较，当实验的组数达到3组或者3组以上时，就不再适合用T检验来做统计分析了，此时的统计量为F。统计量F的定义是，组间数据的均方除以组内数据的均方。

平方和和自由度的剖分：在不同的实验组之间，得到一个数据的变异程度。数据的变异程度我们用平方和SS来表示，SS的定义是，一组数的平方和等于这一组数与其平均数的差值的平方之和。而一组数的均方MS的定义是，均方等于平方和除以自由度。在不同的组之间，我们可以得到组间的平方和SSA。此时，我们把总的平方和剖分为组间的平方和和组内的平方和，即SST=SSA+SSE，将总的自由度剖分为组间自由度和组内自由度。关于总平方和、组间平方和、组内平方和、总自由度、组间自由度，组内自由度的公式如下：

假设现在进行N组实验，每组实验有M个重复，则：

对于总平方和和总自由度

对于组间平方和和组间自由度：

对于组内平方和和自由度

对于F的计算：

这里我们给出一个Sub过程，读者可以仿照这个过程写一个计算F的函数

Sub Anova()

Dim Rng As Range, i As Integer, j As Integer, M As Integer, N As Integer, SST As Double, SSA As Double, SSE As Double

Dim dfT As Integer, dfA As Integer, dfE As Integer, CS As Double, SA As Double

Set Rng = Application.InputBox("选择连续的区域", "以行分组", , , , , , 8)

M = Rng.Columns.Count

N = Rng.Rows.Count

dfT = N * M – 1

dfA = N – 1

dfE = dfT – dfA

For i = 1 To N

SA = 0

For j = 1 To M

SST = SST + Rng.Cells(i, j) * Rng.Cells(i, j)

CS = CS + Rng.Cells(i, j)

SA = SA + Rng.Cells(i, j)

Next j

SSA = SSA + SA * SA

Next i

CS = CS * CS / (N * M)

SST = SST – CS

SSA = SSA / M – CS

SSE = SST – SSA

Sheet2.Cells(2, 2) = SSA

Sheet2.Cells(2, 3) = dfA

Sheet2.Cells(2, 4) = SSA / dfA

Sheet2.Cells(2, 5) = (SSA / dfA) / (SSE / dfE)

Sheet2.Cells(3, 2) = SSE

Sheet2.Cells(3, 3) = dfE

Sheet2.Cells(3, 4) = SSE / dfE

Sheet2.Cells(4, 2) = SST

Sheet2.Cells(4, 3) = dfT

Sheet2.Cells(4, 4) = SST / dfT

Sheet2.Range("F2").Formula = "=F.DiST.RT(E2,C2,C3)"

Sheet2.Activate

End Sub

程序作用，在Sheet2中显示单因素方差分析（Anova）的结果。

（7）相关分析和回归分析

作为统计中一对非常重要的概念，相关和回归在统计工作中占有相当大的比例。相关分析揭示两个实验变量直接的“相关性”，回归分析则是通过某种代数表达式表示二者直接的“关系”。相关和回归用于对某种事物的推测，即预测(Forecast)某个实验的结果。

在实际的科学实验中，我们也可以通过某些实验结果来观察两个变量之间的关系，在一定范围内，某种溶液对光的吸收量总是随着溶液浓度的增加而增加。这种一个变量总随着另一个变量变化而变化的特性就叫做“相关性”。

相关系数：相关系数是一个介于-1~1之间的数，表示两个变量之间变化的关联程度，相关系数的绝对值越高，表面二者之间的“相关性”越大；反之，相关系数的绝对值越小，二者之间的“相关性”也就越低。科学工作中，经常用相关系数的平方来判断两个变量之间的相关性。

当相关系数为一个正数时，两个变量之间的作用为“正相关”，即一个变量的增加将导致另一个变量的增加；当相关系数为负数时，两个变量之间的相关性为“负相关”，即一个变量的增加将导致另一个变量的减少。

相关系数的计算：两个变量x，y之间的相关系数r的公式为：

这里必须要说的是，在Excel中，已经含有计算相关系数的公式了，但是不妨碍我们自己写一个计算公式。

Function Correlate(Rng1 As Range, Rng2 As Range)

Dim A() As Double, B() As Double

Dim S1 As Double, SS1 As Double, S2 As Double, SS2 As Double, SP As Double

Dim R, N As Integer

N = 1

For Each R In Rng1

ReDim Preserve A(N)

A(N) = R

N = N + 1

Next R

N = 1

For Each R In Rng2

ReDim Preserve B(N)

B(N) = R

N = N + 1

Next R

N = N – 1

For i = 1 To N

S1 = S1 + A(i)

SS1 = SS1 + A(i) * A(i)

S2 = S2 + B(i)

SS2 = SS2 + B(i) * B(i)

SP = SP + A(i) * B(i)

Next i

SS1 = SS1 - S1 * S1 / N

SS2 = SS2 - S2 * S2 / N

SP = SP - S1 * S2 / N

Correlate = SP / Sqr(SS1 * SS2)

End Function

“相关性”表示两个变量之间的关乎关系，如果两个变量之间具有一定的相关性，则其中一个变量可以用另一个变量的函数进行表示，这种一个变量用另一个变量或者另外变量表示的过程就叫做“回归”。

在回归问题中，需要表示的变量被称为“因变量”，而另外的变量成为“自变量”，因变量总是随着自变量的变化而变化。我们把只有一个自变量的回归成为“一元回归”，把自变量和因变量之间的线性关系的回归叫做“线性回归”。

回归的最简单模型便是一元线性回归问题，在一元线性回归中，假设因变量y随着自变量x的变化而曾现线性的变化。一元线性回归问题，无非是找到两个合适的系数，使得Y=bX+a这个算式计算出来的Y与实际观察值y之间的“差异性”最小。

对于一元线性回归，具有下列公式：

可以仿照这个公式写一个VBA程序，同时输出回归方程和相关系数的平方R的值。

Function Reg(Rng1 As Range, Rng2 As Range) As String

Dim X() As Double, Y() As Double

Dim S1 As Double, SS1 As Double, S2 As Double, SS2 As Double, SP As Double

Dim R, N As Integer

Dim b As Double, a As Double

N = 1

For Each R In Rng1

ReDim Preserve X(N)

X(N) = R

N = N + 1

Next R

N = 1

For Each R In Rng2

ReDim Preserve Y(N)

Y(N) = R

N = N + 1

Next R

N = N – 1

For i = 1 To N

S1 = S1 + X(i)

SS1 = SS1 + X(i) * X(i)

S2 = S2 + Y(i)

SS2 = SS2 + Y(i) * Y(i)

SP = SP + X(i) * Y(i)

Next i

SS1 = SS1 - S1 * S1 / N

SS2 = SS2 - S2 * S2 / N

SP = SP - S1 * S2 / N

R = SP * SP / (SS1 * SS2)

b = SP / SS1

a = (S2 - S1 * b) / N

Reg = "y=" & b & "x+" & a & ",R=" & R

End Function

第三节 微积分算法的VBA代码实现

7.3.1 数值估算法

在计算机发展历程中，微积分思想解决了估算一个数值方法。在未发明“无穷大”和“无穷小”时，人们对于一些数值的计算显得无能为力。例如，求10.022这样的数值，按照一般的算法，就需要计算一个4位数的乘法。

在微积分的数值估算法中，有一个著名的泰勒公式：

、 这个公式就像从天而降一样，对于复杂的计算来说简直就是福音。它告诉我们，当我们对于结果的要求不是那么精确时，可以省略掉后面的计算部分而只保留前面的结果。

以10.022这个计算为例，说一下在微积分估算数值中的算法。

（1）将问题抽象成一个函数，现在这个函数就是f(x)=x2

（2）取合适的X0值，X0这里可以取10，ΔX=0.02

（3）对于y=f(X0+ΔX),在ΔX非常小的时候，可以约等于

f(X0)+f’(X0)ΔX

（4）又f(x)=x2,f’(x)=2x所以，f(X0+ΔX)=f(X0)+2X0ΔX

（5）带入，可以得到10.022=f(10.02)=f(10)+2×10×0.02=102+0.4=100.4

用计算器计算一下，10.022=100.4004，两者相差仅有0.0004.

再举一个例子，现在要求计算1000.1的开3次方，即1000.11/3.在没有计算器的时候，这个开立方的计算将无比复杂。但是任然可以利用上面的思路，这里，f(x)=x1/3.f’(x)=1/3x-2/3.那么，取X0=1000，ΔX=0.1.

f(X0+ΔX)=f(X0)+f’(X0) ΔX=10001/3+1/3×1000-2/3×0.1

10001/3=10,1000-2/3=0.01,所以，1000.11/3=10+0.00033=10.00033。跟计算器计算出的数值几乎一样。

类似的，我们可以写一个VBA程序来计算例如像4.02的开平方这样的计算。

Function MySqr(A As Double) As Double

Dim X0 As Integer

Dim DX As Double

X0=Fix(A)

DX=A-X0

MySqr=Sqr(X0)+0.5*DX/Sqr(X0)

End Function

读者可能会问，这里还是利用了系统的Sqr函数，那么可不可以不利用系统的Sqr函数来计算一个数的开平方呢？答案是肯定的，这里我们就需要用到上一章所将到的函数的递归调用了。因为我们已经知道，0的开平方是0, 1的开平方是1，因为现在已经知道了初始的数值，不妨让函数递归时，每一次减去0.1，一直减少到整数部分为0或者为1。这个程序就留给读者完成了！

7.3.2 曲边梯形面积的计算

函数y=f(x)与x轴，以及x=a，x=b构成的图形的面积（曲边梯形）被定义为f(x)在a到b上的定积分。定积分的计算方法是，将x轴上a到b之间的距离N等分，每一个小份就可以近似看出一个矩形，然后分别求取每一小份的面积，再对每个小份面积求和。当N趋近于无穷大时，求和的面积就等于曲边梯形面积。在高等数学中，我们通过计算一个函数原函数的方法来求定积分的精确值，但是，计算机中完全没有这个必要，因为计算机并不知道函数的原函数是什么，更不会计算例如负无穷大到正无穷大这样的反常积分。计算机只会做的事情，就是像上面那样的“分割”和“求和”，至于说取极限，也只要利用上一章所将的过程，即判断两次计算结果的差的绝对值是否小于一个确切的数值。

例如：利用定积分求sin(x)在0到π/2上的定积分。

Function IntG(N As Integer)

Dim a As Double

Dim b As Double

Dim StepI As Double

Dim i As Integer

Dim y As Double

a = 0

b = Application.WorksheetFunction.Pi() / 2

StepI = (b - a) / N

For i = 0 To N

y = Sin(a + StepI * i) * StepI

IntG = IntG + y

Next i

End Function

Function ResT()

Dim N As Integer

N = 100

While Abs(IntG(N) - IntG(N + 10)) > 0.00001

N = N + 10

Wend

ResT = IntG(N)

End Function

这里的两个函数，一个用于“分割求和”，一个用于“求极限”。直接调用ResT就可以得到结果1.000008.

7.3.3 计算概率值

根据上述“分割求和”与“取极限”的思想，我们可以求出一个概率的数值。在之前已经谈到，一个时间发生的概率等于这个事件的某个统计量在其分布函数上的一个定积分。现在我们暂且不谈诸如t检验中用到的t分布这样的太过于复杂的表达式，先看一看标准正态分布情况下的概率值的计算。在之前的讨论中，我们当时给出的概率值均是“两尾概率”，所谓的两尾概率指的是，当两个样本（或者更多的样本）的总体均数“不相等”时的概率，“不相等”的概率应该等于第一个变量大于第二个变量的概率加上第一个变量小于第二个变量的概率。因此，“两尾”概率等于“一侧”概率的两倍。

现在我们来看一下标准正太分布的两尾概率求解。

标准正态分布的分布函数为:

Function InTG(X As Double, N As Integer) As Double

Dim a As Double

Dim b As Double

Dim StepI As Double

Dim i As Integer

Dim y As Double

a = -X

b = X

StepI = (b - a) / N

For i = 0 To N

y = Exp(-(a + StepI * i) ^ 2 / 2) * StepI / Sqr(2 * 3.14)

InTG = InTG + y

Next i

End Function

Function ResT(X As Double) As Double

Dim N As Integer

N = 100

While Abs(InTG(X, N) - InTG(X, N + 10)) > 0.00001

N = N + 10

Wend

ResT = InTG(X, N)

ResT=1-ResT

End Function

最后这个ResT=1-ResT，因为整个积分等于1，两尾的概率等于1减去中间的概率。

大家可以用这个来计算一下，当x取值为2.95时，两尾的概率小于0.5%，达到显著水平，这就是工厂上以平均数±3倍标准差作为判断正常运行标准的原因。

第四节 按照条件搜索相应的记录

7.4.1 按精确条件搜索记录

平时工作中，总会遇到按条件搜索记录的要求，例如，现在我们想在工作表1（Sheet1）中的第N列（编号为A~Z的列）中找到与一个“目标”完全相同的行并将其数据拷贝到同一个工作簿的工作表2（Sheet2）中。

Sub MyFind()

Dim Rng As Range,N As Integer

Dim RngColumn As Range,Aim As String,Colu As String

Dim i As Integer, j As Integer

Set Rng=Sheet1.UsedRange

N=Rng.Rows.Count

L1:

Colu=Inputbox(“输入需要查找的列A~Z”)

If Asc(Colu)<65 or Asc(Colu)>90 Then Goto L1

Colu=Colu & “:” & Colu

Set RngColumn=Sheet1.Range(Colu)

Aim=Inputbox(“输入查找项”)

j=1

For i=1 to N

If RngColumn(i)=Aim Then Sheet1.Rows(i).Copy Sheet2.Rows(j): j=j+1

Next i

End Sub

7.4.2 按照范围条件搜索

与精确条件不同，范围条件往往是一个范围，对于这些范围的条件，我们现在的搜索内容不再是某一个具体的值，而是需要拓展到一个比如大于、小于、不等于等的条件。

例如，如果我们想找出表1（Sheet1）中某一列的数大于或小于一个数字的所有的行并拷贝到工作表2（Sheet2）中。这里，我们需要进行两个操作，第一个操作，确定需要提取的目标是“大于”或者“小于”，第二，确定目标是什么。对于第一个操作，我们可以通过一个字符串来解决，即向计算机输入大于号“＞”或者小于号“＜”，对于第二个操作，仍然可以用Aim表示。

Sub MyFind()

Dim Rng As Range, N As Integer

Dim RngC As Range, Aim As Double, Colu As String

Dim Op As String, Cmd As String

Dim i As Integer, j As Integer

Set Rng=Sheet1.UsedRange

N=Rng.Rows.Count

L1:

Colu=Inputbox(“输入需要查找的列A~Z”)

If Asc(Colu)<65 or Asc(Colu)>90 Then Goto L1

Colu=Colu & “:” & Colu

Set RngC=Sheet1.Range(Colu)

Cmd=Inputbox(“输入条件，符号和数字之间留一个空格”)

Op=Split(Cmd,” “)(0)

Aim=Val(Split(Cmd,” “)(1))

j=1

If Op=”>” Then

For i=1 To N

If RngC(i)>Aim Then Sheet1.Rows(i).Copy Sheet2.Rows(j) : j=j+1

Next I

Else

For i=1 to N

If RngC(i)<Aim Then Sheet1.Rows(i).Copy Sheet2.Rows(j) : j=j+1

Next i

End If

End Sub

请大家先用笔或者什么的把这个程序的代码记录一下，在下一章代码的健壮性中我们将对这个程序进行讨论。

7.4.3 多个条件的搜索

在多条件搜索的情况下，搜索条件不再是一个，而是可以拓展到多个条件，条件与条件之间可以是“或者”、“并且”等关系。现在先看一下两个条件的搜索。例如，想搜索表1（Sheet1）中符合B列大于10并且C列小于6的所有的行，并复制到表2（Sheet2）。注意到，这里考虑到通用性，不一定是B列和C列，中间的连接符也不一定是“并且”也可以是“或者”。

Sub MyFind()

Dim Cond As String

Dim Cond1 As String, Op As String,Cond2 As String, i As Integer, j As Integer

j=1

Cond=Inputbox(“输入条件”)

Cond1=Split(Cond,”.”)(0)

Op=Split(Cond,”.”)(1)

Cond2=Split(Cond,”.”)(2)

Select Case Op

Case “And”

For i=1 to Sheet1.UsedRange.Rows.Count

If MyComp(i,Cond1) And MyComp(I,Cond2) Then Sheet1.Rows(i).Copy Sheet2.Rows(j) : j=j+1

Next i

Case “Or”

For i=1 to Sheet1.UsedRange.Rows.Count

If MyComp(i,Cond1) OR MyComp(I,Cond2) Then Sheet1.Rows(i).Copy Sheet2.Rows(j) : j=j+1

Next i

Case Else

End Select

End Sub

Function MyComp(RowI As Integer,Cond As String)As Boolean

Dim C1 As String,Col As String

Dim Op As String

Dim C2 As String,V As Double

Dim Rng As Range

C1=Split(Cond,” “)(0)

C2=Split(Cond,” “)(1)

C3=Split(Cond,” “)(2)

V=Val(C3)

Col=C1 & “:” & C1

Set Rng=Sheet1.Range(Col)

Select Case C2

Case”>”

If Rng(RowI)>V Then MyComp=True Else MyComp=False

Case “<”

If Rng(RowI)<V Then MyComp=True Else MyComp=False

Case “=”

If Rng(RowI)=V Then MyComp=True Else MyComp=False

Case “!=”

If Rng(RowI)<>V Then MyComp=True Else MyComp=False

Case “>=”

If Rng(RowI)>=V Then MyComp=True Else MyComp=False

Case “<=”

If Rng(RowI)<=V Then MyComp=True Else MyComp=False

Case Else

MyComp=False

End Select

End Function

请读者同样也将这个程序“打包”复制一下，我们在第8章将讨论这个程序中可能出现的问题。

7.4.4 模糊搜索

模糊搜索是一个非常大的难题，计算机程序的编写法则是：不怕繁琐，就怕模糊。当一个条件模糊不清的时候，我们不清除需要计算机做些什么，这样的问题是最为头疼的。遇到此类问题时，先把问题尽量地量化和精确化，然后再给出具体解决方案。

例如：找出B列中数字较大的行。这里的问题就是，如何定义“较大？”假如我们将B列的最大数和最小数之间的差定义为B列数据的范围，那么，这个数据范围的前10%算不算“较大？”还是前20%算作“较大？”当然，这里需要根据B列共有多少个数来定。假设我们现在让用户自己输入这个百分比。

Sub MyFind()

Dim Rng As Range, Max As Double, Min As Double, i As Integer, j as Integer

Dim Rt As Double, Psent As Double

Set Rng=Sheet1.Range(“B:B”)

Max=Rng(1)

Min=Rng(1)

j=1

For i=1 To Sheet1.UsedRange.Rows.Count

If Rng(i)> Max Then Max=Rng(i)

If Rng(i)< Min Then Min=Rng(i)

Next i

Rt=Max-Min

Psent=Inputbox(“输入百分比%”)

Psent=Psent/100

For i=1 to Sheet1.Usedrange.Rows.Count

If Rng(i)>Max-Psent*Rt Then Sheet1.Rows(i).Copy Sheet2.Rows(j) : j=j+1

Next i

End Sub

当然这个问题也可以稍微改一下，即我们搜索全部排名的前5%或者10%，请读者思考一下这个搜索排名前5%的程序应该如何实现。

第五节 几个简单统计过程的VBA实现

7.5.1 统计过程

计算出每个统计量，并列表，然后计算p值的过程成为统计过程。在统计过程中涉及到的内容有：

（1）对该统计过程的每一个统计量进行计算。

（2）计算p值。

我们可以看到，这里不再是只求出一个具体的数值，而是需要显示整个统计的过程，所以这里用Sub过程比较好，我们利用Excel现有的单元格来显示我们统计的结果。这一节中，将不再详细讨论过程是如何实现的，而是只给出过程的VBA程序，对于VBA程序的语句，大家可以自行理解。

7.5.2 两样本总体均数T检验（方差其次）的Sub过程

Sub TTestFor()

Dim Rng1 As Range, Rng2 As Range, S1 As Double, S2 As Double, SS1 As Double, SS2 As Double, df As Integer

Dim N1 As Integer, N2 As Integer, i, SS As Double, T As Double

Set Rng1 = Application.InputBox("选择第一个区域", "第一个区域", , , , , , 8)

Set Rng2 = Application.InputBox("选择第二个区域", "第二个区域", , , , , , 8)

For Each i In Rng1

S1 = S1 + i

SS1 = SS1 + i * i

N1 = N1 + 1

Next i

SS1 = SS1 - S1 * S1 / N1

For Each i In Rng2

S2 = S2 + i

SS2 = SS2 + i * i

N2 = N2 + 1

Next i

SS2 = SS2 - S2 * S2 / N2

SS = SS1 + SS2

SS = SS / (N1 + N2 - 2)

SS = SS * (1 / N1 + 1 / N2)

SS = Sqr(SS)

T = (S1 / N1 - S2 / N2) / SS

df = N1 + N2 – 2

Sheet1.Range("B2") = S1 / N1

Sheet1.Range("B3") = S2 / N2

Sheet1.Range("B4") = S1 / N1 - S2 / N2

Sheet1.Range("C2") = Sqr(SS1 / (N1 - 1))

Sheet1.Range("C3") = Sqr(SS2 / (N2 - 2))

Sheet1.Range("C4") = SS

Sheet1.Range("D2") = N1 – 1

Sheet1.Range("D3") = N2 – 1

Sheet1.Range("D4") = N1 + N2 – 2

Sheet1.Range("E4") = T

Sheet1.Range("F4").Formula = "=T.DIST.2T(ABS(E4),D4)"

Sheet1.Activate

End Sub

作用：将一个Sheet中的两个单元格区域的数字做T检验。检验结果放在Sheet1中A1到F4单元格区域。

7.5.3 相关系数矩阵的Sub过程

Sub Cor()

Dim Rng As Range

Set Rng = Application.InputBox("选择区域", "连续区域", , , , , , 8)

Dim i, j As Integer

For i = 1 To Rng.Columns.Count

For j = 1 To Rng.Columns.Count

Sheet2.Cells(i, j) = WorksheetFunction.Correl(Rng.Columns(i), Rng.Columns(j))

Next j

Next i

End Sub

7.5.4求矩阵的逆矩阵的Sub过程

Sub Solve()

Dim RngO As Range, Rng1 As Range, i As Integer, j As Integer, k As Integer, N As Integer, M As Integer

Set RngO = Application.InputBox("请选择区域", "选择方形区域", , , , , , 8)

N = RngO.Rows.Count

M = RngO.Columns.Count

If N <> M Then

MsgBox ("区域选择有误！")

Else

For k = 1 To N

Set Rng1 = RngO.Offset(RngO.Rows.Count + 1, 0)

Rng1.Cells(k, k) = 1 / RngO.Cells(k, k)

For j = 1 To N

If j <> k Then Rng1.Cells(k, j) = RngO.Cells(k, j) / RngO.Cells(k, k): Rng1.Cells(j, k) = -RngO.Cells(j, k) / RngO.Cells(k, k)

Next j

For i = 1 To N

For j = 1 To N

If i <> k And j <> k Then Rng1.Cells(i, j) = RngO.Cells(i, j) - RngO.Cells(i, k) * Rng1.Cells(k, j)

Next j

Next i

Set RngO = Rng1

Next k

End If

End Sub