VBA学习.docx

VBA学习.docx

《VBA学习.docx》由会员分享，可在线阅读，更多相关《VBA学习.docx（17页珍藏版）》请在冰豆网上搜索。

VBA学习

一、认识VBA

VBA的全称是VisualBasicforApplications，Visual的意是可视化，Basic是一种计算机程序语言，类似的程序语言还有JAVA、C++等，VisualBasic是可视化Basic语言，Applications的意思是应用程序，比如WORD、EXCEL等，VisualBasicforApplications的意思是开发环境被整合到了某个应用程序的VisualBasic语言，我们将要学习的是开发环境被整合到了MSOffice中的VisualBasic语言。

我们在使用office办公软件的时候经常可以听到“宏这个概念”，宏是office软件自动录制的一段VBA代码，VBA能完成的工作如下：

避免重复劳动，多个文档协作，可以操作任何WindowsAPI（代表你的Windows能做的任意事情）和任意用COM封装的组件。

比如像Acrobat，EndNote等都是这么做的。

VBA直接提供OPEN关键字来访问文本文件。

VBA直接提供SendKeys函数来模拟键盘输入。

VBA可以使不同的Office组件可以互操作，还可以操作比如MediaPlayer，IE等程序。

VBA可以写图形界面等。

二、基本编程元素：

Sub（过程）与Function（函数），Sub与Function都是能完成某一功能且能重复利用的代码块，不同之处在于Sub无返回值，而Function有返回值；Sub可以指定给某个图形或按钮，也可以直接运行（带参数的过程只能指定给某个图形或按钮来执行），Function则不可以，它只能被某个Sub调用，Sub与Function可以相互调用，也就是说Sub可以调用Function，Function也可以调用Sub，从这个角度来说，Sub一般用于完成某个动作，Function一般用于自定义函数。

它们的语法格式如下：

过程的书写语法：

Sub过程名（参数名1As类型1，参数名2As类型2，﹒﹒﹒）

这里是要执行任务的代码块

EndSub

如：

Subtest1（aAsInteger,bAsInteger）

DimcAsInteger

c=a+b

MsgBoxc

EndSub

但这个过程不可以直接调用，因为它有参数，如想直接调用，可以借助于另一个不带参数的过程调用它，如：

在前台放个按钮，并为其找定下列代码：

PrivateSubCommandButton1_Click（）

Test112,14

EndSub

在前台点击按钮，会输出：

26

也可以借助以下过程调用：

Subtest2（）

Test112,14

EndSub

测试效果，同样输出：

26

这说明过程可以调用过程。

函数的书写语法：

Function函数名（参数名1As类型1，参数名2As类型2，﹒﹒﹒）As类型

这里是要执行任务的代码块

函数名=表达式或值

EndFunction

以下示例是求两数和的函数：

Functionadd（aAsInteger,bAsInteger）AsInteger

add=a+b

EndFunction

由于函数不能被直接执行，所以还要借助于以下过程来执行：

Subshichu（）

DimcAsInteger

c=add（2,3）

MsgBoxc

EndSub

执行此过程，输出结果为：

5

此例同时说明过程可以调用函数。

下面再举例说明函数调用函数：

Functionadd（aAsInteger,bAsInteger）AsInteger

add=a+b+By（4,5）

EndFunction

Subshuchu（）

DimcAsInteger

c=add（2,3）

MsgBoxc

EndSub

FunctionBy（dAsInteger,eAsInteger）AsInteger

By=d*e

EndFunction

上面例子中有两个函数一个过程，其中函数加法add调用乘法函数By，调用By函数时完成了4和5的乘法运算，并返回运算结果，过程shuchu调用函数add，调用add函数时完成2与3与By函数的返回值20这三个数的加法运算，所以最终输出结果为：

25。

变量与类型

和其它任何一门程序语言一样，VBA编程必须要有变量用以存放数据或对象，VBA中声明变量的语法格式是：

Dim变量名As类型

比如：

DimaAsInteger

这句代码声明了一个整数型变量a，它在内存中占有2个字节空间，不同类型的变量在内存中有空间大小不同，所以我们要合理选择使用变量类型，以节省机器内存资源，提高机器工作效率。

变量也可以不经定义直接使用，也就是使用默认类型（Variant类型），它可以存放任意类型数据，但它使用的内存空间太大，且性能差，而且不容易查错，所以不建议不经定义直接使用变量。

一般我们会强制变量先声音后使用，强制声音变量的方法是，在开头第一句写上语句：

OptionExplicit

常用数据类型有：

Integer：

整数型

Long：

长整数型

Single：

单精度浮点型

Double：

双精度浮点型

Date：

日期型

String：

字符型

Varint：

任意类型

变量作用域问题：

每一个变量都有一个作用域。

如果在一个Sub/Function内定义变量，那么这个变量的作用域就仅仅限于这个Sub/Function，称为局部变量。

也就是说，在其他Sub/Function中是访问不到这个变量的；或者说，如果在其他Sub/Function中定义了同名的变量，二者完全是两码事，互相不会有任何影响。

在Sub/Function中的参数也是一种局部变量，其定义的方式就是比常规方法省略了Dim。

其形式正如上文所写的那样。

当然，你也可以在参数列表里不写类型，这就意味着参数类型是Varaint。

同样的，Function第AsType

表明Function返回值的类型。

不写这个返回值就意味返回Variant类型。

如果将Dim语句写在任意Sub/Function之外，就定义了全局变量。

此时变量对于当前模块文件的所有Sub/Function都有效。

这时，不同的Sub/Function之间就能通过全局变量交流（尽管这不是个好习惯，因为这样就破坏了Sub/Function的独立性，使得复用难以进行）。

当然，还有可以在不同模块之间都有效的变量的定义方法，即将全局变量定义中的“Dim”改为“Public”。

不过这种变量在小型程序中非常少见。

也许你会问，如果某个局部变量恰好和全局变量同名会怎么样？

这时局部变量会“遮蔽”全局变量，使你只能访问到局部变量。

三、对象

对象的概念比较抽象：

对象是一个实体物件，如EXCEL的工作环境、EXCEL中的工作薄、工作表、单元格、区域、图形、图表、透视表、名称、批注、条件格式等都是对象，当然还不止这此，我们可以在以后的学习慢慢的认识这些对象。

对象的定义虽然晦涩难懂，但它非常重要，因为VBA中几乎所有的操作都与对象有关。

对象和变量一样，都有相应的类型。

比如一个单元格（区域）的类型是Range.

对象的引用：

所以对象的引用是指定义一个变量用来指向某个对象（可以理解为存储为某个对象），这所以称为引用，是因为变量只是存储了这个对象在物理内存中的地址，这个地址可以同进分配给多个变量，比如：

DimaAsRange,bAsRange

Seta=Range（"A10"）

Setb=Range（"A10"）

代码中的变量a和b同时指向一个对象Range（"A10"）。

同时值得注意的是把一个变量赋值给某变量时要用Set关键字，如：

Seta=Range（"A10"），如果不用关键字Set，就等价于给对象的默认属性赋值，如：

DimaAsRange,bAsRange

a="abc"

等价于

a.Value="abc"

因为Value是Range对象的默认属性。

但是如果你写成：

a=Range（"A2"）

就会出错。

所以不要忘记Set关键字。

而对于数值型变量赋值则不需如此，如以下代码：

DimaAsInteger

a=3

四、学习编写完整程序

下面就一个具体的问题来练习编写完整程序：

如何将一组单元格内红色的数字求和.

Excel自己提供的函数Sum不能对待求和数据进行条件判断，而Sumif只允许对待求和数

值进行数值比较上的判断，也无法处理“字体是红色”这种格式条件。

这时正是VBA发挥作用的时候，它可以将一些基本的功能组织到一起，然后完成自定义的任务。

开始编程之前，应该先想清楚几个关键的问题，也就是一个设计的过程。

对于本问题，

首先应明白如何选定并表示一个单元格范围，并以此作为问题的输入？

对于一个单元格如何判断一个单元格的文字是红色的？

如何遍历某个区域内的每一个的单元格？

如何获取一个单元格的值？

如何求和？

如何输出？

问题的解决：

假如待求各数据在A1单元格与I13单元区域内，如图所示：

我们可以用Range（"a1:

i13"）.Select来选中这个区域，以后可以用Selection这个属性表示前面选中的区域。

判断红色字体可以用如下代码实现：

r.Font.Color=vbRed或r.Font.Color=RGB（255,0,0），这里的r代表一个单元格。

遍历区域内的单元格用ForEach循环实现，其语法格式如下：

ForEachIn

'DoSomethingwith

Next

获取单元格的值用单元格属性Value实现。

输出就用MsgBox吧。

完整代码如下：

SubsumIfRed（）

Range（"a1:

i13"）.Select

DimiAsRange

DimsumAsDouble

sum=0

ForEachiInSelection

Ifi.Font.Color=vbRedThen

sum=sum+i.Value

EndIf

Next

MsgBoxsum

EndSub

测试效果，输出和为：

180，完全正确。

上述代码我们是先把数据区域选中再进行循环、判断，求和，我们也可以不选中数据区域，直接指定数据区域并进行循环、判断，求和，如下列代码：

Subhh（）

DimaAsRange

Seta=Range（"a1:

i13"）

DimiAsRange

DimsumAsDouble

sum=0

ForEachiIna

Ifi.Font.Color=vbRedThen

sum=sum+i.Value

EndIf

Next

MsgBoxsum

EndSub

测试效果，输出结果依然正确。

我们还可以用函数实现这个效果，代码如下：

FunctionsumIfRed（aAsRange）AsDouble

DimiAsRange

DimsumAsDouble

sum=0

ForEachiIna

Ifi.Font.Color=vbRedThen

sum=sum+i.Value

EndIf

Next

sumIfRed=sum

EndFunction

Subkk（）

DimbAsRange

Setb=Range（"a1:

i13"）

DimsumAsDouble

sum=sumIfRed（b）

MsgBoxsum

EndSub

其中过kk也可以优化为：

Subkk（）

DimbAsRange

Setb=Range（"a1:

i13"）

MsgBoxsumIfRed（b）

EndSub

使用自定义函数的还有一个好处，就是可以在前台直接调用该函数，如本例中我们可以在任何一个单元格内输入：

=sumIfRed（A1:

I13）后回车，照样可以得出结果。

四、操作文件

我们以具体的安全来说明这个问题：

一家超市有几个销售部门，如：

烟酒、服装、日用品、电器四个部门，每个部门都有一销售电脑，这台电脑里以文本文件的形式存放着本部门的销售日志，如图所示：

下图是电器部门日志（sever1）。

此文本文件中存储了本部门当日销售的所有商品名称、价格和交易时间，中间用一个空格隔开。

每个部门每天的销售日志传送到财务部门，专门存入在以日期命名的一个文件夹内，如图所示：

财务部门每周（七天）进行一次汇总处理，此时账务部门数据目录结构如下图所示：

假设这些数据的根路径在E:

\xiaoshou下。

我们希望将所有的数据汇总到一个Workbook中，为每一个销售部门建立一个Worksheet，sheet名称就是销售部门名称，里面记录了这个销售部门的所有交易日期，交易时间和成交价格（不管交易的什么东西）。

然后新建一个Worksheet，求出所有销售部门的成交总额，并为此建立一个柱状图。

最后再清理掉所有原始日志。

和以前一样，编程前应该先弄清楚有哪些问题要解决：

1.如何新建一个Workbook，并在里面添加Worksheet？

2.如何打开文本文件？

3.如何遍历一个目录下的所有文件夹？

4.如何一行一行的读取文本文件的内容？

5.如何拆分字符串？

6.如何将数据放到数据表恰当的位置？

7.如何在工作表里插入公式，计算总额？

8.如何在工作表里插入柱状图？

9.如何删除原始的文本文件

下面逐一解决上述问题:

1.如何新建一个Workbook，并在里面添加Worksheet？

在Excel中，Workbooks这个集合有一个方法叫做Add，即Workbooks.Add意为添加一个Workbook。

可以看到，这非常的直观。

例如，编辑运行如下代码就可以创建一个新的工作簿：

Subff（）

Workbooks.Add

EndSub

类似的你可以Workbooks.Open来打开一个现成的Excel文件；Workbooks.OpenDatabase打开一个数据库（比如Access的mdb文件）；Workbooks.OpenText可以打开一个文本文件，自动做分列处理；Workbooks.OpenXML可以打开XML文件等等。

2.如何打开文本文件？

对于问题2，需要使用Open这个VBA的关键字来打开一个文本文件：

OpenForAs#

这条语句用指定的打开模式打开指定路径上的文件，并连接到指定的一个号码。

比如代码：

Open"C:

\log\2008-4-1\server1.txt"ForInputAs#1

可以以只读的方式打开指定的server1.txt文件，并将文件号设为1。

输入以下代码并测试效果：

Subff（）

Open"E:

\xiaoshou\2011-10-1\sever1.txt"ForInputAs#1

EndSub

测试时并不在前台显示已打开的sever1.txt文件，但确实已经打开，如果你再次测试会报如下错误：

指定的文件号在后续的读写操作中代表打开的文件内容（详情见下文）。

文件打开的模式常用的有：

Input只读；Output写入；Append追加写入；Binary二进制方式打开；Random随机读写方式打开这五种。

在本文的例子中用到了Input模式。

对于打开的文件要时刻记住一定要在不用文件的时候关闭它。

不然就会出现别的程序无法打开，或者无法删除的情况（因为你在占着嘛）。

利用关键字Close就可以关闭文件，如：

Close#1

就可以关闭上边打开的文件。

输入以下代码并两次测试效果：

Subff（）

Open"E:

\xiaoshou\2011-10-1\sever1.txt"ForInputAs#1

Close#1

EndSub

并不会出现上面所提到的错误提示，是因为sever1.txt被打开之后又被关闭了，虽然只是一开一关，中间并没有做什么事，所以第二次测试时照样可以打开那个文本文件。

3.如何遍历一个目录下的所有文件夹？

如果是在命令行下，就可以用dir命令。

VBA也提供这样一个具有同样功能的Function——Dir。

其格式为：

fileName=Dir（[path],[attribute]）

其中path指定要Dir的目录，就像在命令行一样，path中可以使用像*和?

这样的通配符。

attribute可以用一些常量来指定要遍历的文件类型。

这样就能够只遍历目录，或者只读文件等等。

不过这个Dir奇怪的很，一次只能返回一个文件名称（目录也算是文件的一种）。

VBA规定第一次使用Dir时需要指定一个路径。

这时将返回指定路径的第一个文件名，之后不写任何参数调用Dir，就可以依次遍历出所有的文件名。

当最后一个文件名得到之后，Dir会返回一个空字符串”"。

这样我们就知道了遍历结束了。

所以以下代码可以遍历C:

\log下的所有目录，并依次用对话框输出。

DimStrAsString

Str=Dir（"C:

\log\",vbDirectory）

WhileStr<>""

IfStr<>"."AndStr<>".."ThenMsgBoxStr

Str=Dir

Wend

4.如何一行一行的读取文本文件内容并将其按规则拆分？

对于问题4，VBA提供了了关键字Input，Write，Print等关键字来读写文本文件，还提供了Put和Get来读写二进制文件（以后再说）。

不过对于每次读一行这样的需要还是使用LineInput关键字最合适（注意LineInput是一个整体）。

比如代码：

LineInput#1,str

就可以从文件号为1的文件中读出一行，并放到str中。

那行字符串末尾的回车会被自动删掉。

这样就可以利用循环将文件中所有的行都读出来。

下面的代码读出了文件的所有行，并用对话框依次输出：

Subkk（）

Open"E:

\xiaoshou\2011-10-1\sever1.txt"ForInputAs#1

DimstrAsString

DimiAsInteger

WhileNotEOF

（1）

LineInput#1,str

MsgBoxstr

i=i+1

Wend

MsgBox"共有"&i&"条记录"

EndSub

注意这里用了FunctionEOF（filenumber）来判断是不是到了文件尾。

有人也需要问什么时候需要#，什么时候不需要？

答案是#是VBA关键字的需要，也就是说在用关键字的时候需要带#，比如Open，Close，Input等；而对于VBAFunction，则不需要#。

5.如何拆分字符串？

对于字符串拆分这个问题，VBA提供了很便利的函数Split。

你需要指定要拆分的字符串和分隔符，split就能将结果以数组的形式返回（要是Dir也返回数组就好了）。

比如：

strs=Split（"abc,def,ghi",","）

就可以获得一个三个成员的数组，其中strs（0）内容为abc，strs

（1）内容def，strs

（2）内容为ghi。

在这里引入了数组的概念。

简单来讲，一个数组就是可以用“变量名（下标）”访问的变量。

数组的下标具有下界（LowerBound，简称LBound）和上界（UpperBound，简称UBound）。

默认情况下数组下界从0开始，但也可以采用一些方法来改变这个，例如在文件头写OptionBase1，可以让默认下界变为1。

或者明确的用Dim声明一个数组，如Dimarr（3to9）AsString，就定义了一个上界为9，下界为3的字符串数组。

不过就我的经验，修改上下界没有太大的意义，只会引起混乱。

只有一个下标的数组被称为“一维数组”，同理就会有“二维”、“三维”、……等数组。

但多维数组一般不常用，数组必须在定义时就指定上下标范围才能使用，即其大小需要在使用前确定。

与数组对应的就是Collection，读者可以将其理解为一个可变长的数组，不需要提前制定大小。

通过Add方法来添加元素，通过Count来取得其包含元素的个数。

比如：

DimcolAsNewCollection

col.Add"abc"

col.Add"def"

MsgBoxcol.Count

注意因为Collection是对象类型，所以声明时要用New来创建对象。

Collection使得开发方便了很多，比如在下文的代码中就有用到。

6.如何将数据放到数据表恰当的位置？

其实这已经不是个问题了。

看过前面教程的都知道用Range（”XX”）.Value=XXX的形式来做到这一点。

7.如何在工作表里插入公式，计算总额？

对于问题6，可以使用Range.Formula属性。

比如可以用Range（"A1"）.Formula="=Sum（Sheet2!

B1:

B10）"来将一个公式赋给单元格A1。

对于上面的问题，其实更关键的是如何确定待求和的单元格范围。

在下文的完整代码再说明这个问题。

8.如何在工作表里插入柱状图？

与Workbooks这种集合对象类似，Excel中也有Charts集合，表示当前Workbook中所有的图表。

所以可以利用Charts.Add来添加一个图表，比如：

Range（"A1:

A10"）.Select

Charts.Add

和直接使用Excel一样，在添加图表之前应该先选择一些数据，然后Excel就会自动建立一个最合适类型的图表。

如果不满意，可以利用产生的Chart对象来调节。

比如Chart.ChartTitle属性调节名称，Chart.Location属性调节位置，Chart.ChartType属性来调节图表类型，Chart.SetSourceData方法来改变数据源等等。

9.如何删除原始的文件？

VBA提供了Kill关键字和RmDir关键字，前者可以删掉一个文件，而后者可以删除一个目录。

Kill的特点是可以使用通配符，这样一次性可以删掉一批文件。

RmDir的特点是只能删除空目录。

所以Kill和RmDir需要配合使用。

OK，所有的关键问题到目前为止都已经解决。

是时候将他们组合起来了。

不过由于问题比较多，还是先画个流程图，进一步明确要做的事情。

除非你能一次想明所有问题，写代码之前最好先规划一下。

对于我们的程序，大致应该是这样的：