计算机Excel应用案例,Excel VBA在Office中的应用案例

摘要：VBA是一种内嵌于某种应用程序的编程语言，如Excel。编程人员可以通过VBA语言，操纵Excel对象模型实现自动处理数据的功能。该文介绍了三个VBA在日常办公与教学中的Excel应用案例：Excel操作题自动评分、数据快捷输入、合并数据表记录。这三个应用案例很有代表性，通过简单地修改就可以应用到其它方面。

关键词：Excel；VBA；办公自动化

中图分类号：TP311 文献标识码：A 文章编号：1009-3044(2013)12-2819-03

VBA是一种内嵌于某种应用程序的编程语言，如Excel。通过VBA的编程，可以使Excel自动完成一些工作，如大批量的数据复制与粘贴、工作表的合并。VBA是VB语言的子集，只要掌握了VB语言就能自然地应用VBA。VBA是针对应用程序编程，确切的说是针对应用程序的对象模型编程。Excel作为一款优秀的数据处理软件，通过可视化操作，就能实现强大的数据处理功能。同时，它还对外公布了它的对象模型，编程人员可以通过VBA语言，操纵Excel相关对象而实现数据处理功能。VBA语言给Excel插上了飞翔的翅膀。

以下通过三个日常办公与教学中的常用案例，介绍VBA在Excel中的高级应用。

1 Excel操作题自动评分

在Excel教学中，我们经常会布置一些Excel操作题给学生做，但这些操作题如何不是某个考试软件中的题目，往往要由教师手动给学生评分，一方面工作量很大，另一方面，往往带有主观意见，对学生不公平。其实我们可以利用VBA编程，实现操作题自动评分。

例如有这么一个操作题：

1)选择工作表sheet1，用求平均值函数求出每人的平均成绩，依次放在E3到E6的单元格中。

2)把标题行A1-E1合并单元格，把标题“学生成绩表”设为20号宋体，居中，并把字体设置为红色。

3)把sheet1工作表名改为：学生成绩表

我们可以编写如下程序，实现该操作题自动评分。

Sub 自动评分()

Dim Grade As Integer ‘Grade-分数

For i = 3 To 6

If Range("E" & i).Formula = "=AVERAGE(B" & i & "：D" & i & ")" Then Grade = Grade + 2

Next

If Range("A1：E1").MergeCells = True Then Grade = Grade + 2

If Range("A1：E1").HorizontalAlignment = xlCenter Then Grade = Grade + 2

If Range("A1：E1").Font.Size = 20 Then Grade = Grade + 2

If Range("A1：E1").Font.ColorIndex = 3 Then Grade = Grade + 3

If ActiveSheet.Name = "学生成绩表" Then Grade = Grade + 3

MsgBox Grade

End Sub

编程思想其实很简单，就是用IF语句对操作题的各个对象属性进行检测，如符合要求，则加分，否则不加。如对对象属性不熟悉，可以先录制宏，得到相应的属性代码。

2 数据快速输入

在日常办公中，经常会遇到这种情况，Excel的 某一列就是常用的几个数据，如某一工作表第三列是职称，教师中常见的职称就是教授、副教授、讲师和助教。对于这些数据，我们可以分别以代号来表示，如1为教授、2为副教授、3为讲师、4为助教。编写如下代码，那只要输入1，应自动转变为教授，其它类似。

Private Sub Worksheet_Change(ByVal Target As Range)

‘ worksheet_change()，是工作表数据发生改变的事件

‘Target是当前操作的单元格

If Target.Column = 3 Then

‘第三列职称，如要在其它列中实现这个功能，则改为其它列

If Target.Value = 1 Then Target.Value = "教授"

If Target.Value = 2 Then Target.Value = "副教授"

If Target.Value = 3 Then Target.Value = "讲师"

If Target.Value = 4 Then Target.Value = "助教"

‘编号与职称相对应，如性别，1-男，2-女，则相应改变其代码。

End If

End Sub

3 合并数据表记录

我们在办公中经常会遇到这种情况，组织部门需要收集单位员工在某一方面的信息，这时工作人员首先会向员工发一份Excel文件，其中包含有要求员工填写的相关信息，有的还会给出了一个样例。员工按要求填写好信息后发回给工作人员，那工作人员需要将每一位员工的工作表打开，复制相关信息粘贴到一个总表中。如果这样的工作表很多，如几百上千份，这将是一个非常繁琐而且容易出错的工作。那么我们能不能用VBA编程实现自动合并这些数据表的记录呢？

如某高校图书馆面向全校各部门征集图书采购信息，工作人员制作了图表1的Excel工作表，并将工作表分发给各部门。

图1

Excel文件只有一个工作表(sheet1)，工作表的第一行是表头信息，第二行是收集图书的信息，第三行给出了一个填写样例，并要求员工不要删除。员工按要求填写好数据发回给工作人员，工作人员将这些文件放在一个文件目录下，并新建一个新的Excel文件，命名为图书信息总表.xls。

这些Excel工作表具有相同的字段信息，除第一条记录外，我们需要将其它工作表的记录合并到图书信息总表中。在总表中编写宏代码，就可以实现。代码及相应解释如下。

Sub HB()

'变量说明：MyPath -文件路径；m-分表的行数；n-总表的当前行数

'XLSName-分表的文件名；WB-分表的工作簿变量

Dim MyPath As String， XlsName As String

Dim m As Integer， n As Integer

Dim WB As Workbook

Application.ScreenUpdating = True

MyPath = ThisWorkbook.Path '获取当前文件的路径

If Right(MyPath， 1) "＼" Then MyPath = MyPath & "＼"

'视情况，将路径补充完整

XlsName = Dir(MyPath & "*.xls") '得到当前路径下的第一个EXCEL文件

Do While XlsName ""

If XlsName ThisWorkbook.Name Then

'除总表之外的分表进行合并记录到总表

Set WB = Application.Workbooks.Open(MyPath & XlsName) '打开分表

m = WB.Sheets(1).[a65536].End(xlUp).Row '得到分表的行数

WB.Sheets(1).Range("a3：h" & m).Copy

'复制分表a列到h列，第四行到最后一行区域的记录

n = ThisWorkbook.Sheets(1).[a65536].End(xlUp).Row '得到总表当前的行数

ThisWorkbook.Sheets(1).Paste Destination：=ThisWorkbook.Sheets(1).Range("a" & n + 1)

'从总表当前最后一行的下一行开始粘贴内容，即追加记录

Application.CutCopyMode = False '清空剪贴板内容

WB.Close '关闭分表

End If

XlsName = Dir '取得下一个分表文件名

Loop

End Sub

上述程序代码具有很大的通用性，对于类似的办公操作，只要按上述要求，并将代码复制到总表，根据情况修改WB.Sheets(1).Range("a3：h" & m).Copy中的参数即可，如果是从第10行开始复制到最后一行，工作表的列数为B到P列，则该语句改为WB.Sheets(1).Range("B10：P" & m).Copy。

4 结束语

通过以上三个案例，足以说明VBA在Excel中的地位。通过VBA编程，可以使Excel的功能更为强大，可以极大提高我们的工作效率。

参考文献：

[1] 博彦科技.Office VBA编程高手[M].北京：北京大学出版社，2001.

[2] 韩小良.Excel VBA工资管理应用案例详解[M].北京：中国铁道出版社，2006.

[3] 陈永强.例学VBA Excel/Access/PowerPoint中的VBA高效应用[M].北京：中国铁道出版社，2010.