VBA的Excel基本原理和应用

VBA的Excel基本原理和应用

 

 

摘要：直到90年代早期，使应用程序自动化还是充满挑战性的领域。对每个需要自动化的应用程序，人们不得不学习一种不同的自动化语言。例如:可以用EXCEL的宏语言来使EXCEL自动化，使用WORD BASIC使WORD自动化，等等。微软决定让它开发出来的应用程序共享一种通用的自动化语言——Visual Basic For Application(VBA)来一统大局。

 

关键词：VBA、Excel、VBA/Excel

 

 

㈠ 一些基本概念

 

一、什么是VBA

 

Excel是什么这里就不多说了。Visual Basic for Applications（简称VBA）是新一代标准宏语言，是基于Visual Basic for Windows 发展而来的。它与传统的宏语言不同，传统的宏语言不具有高级语言的特征，没有面向对象的程序设计概念和方法。而VBA 提供了面向对象的程序设计方法，提供了相当完整的程序设计语言。VBA 易于学习掌握，可以使用宏记录器记录用户的各种操作并将其转换为VBA 程序代码。这样用户可以容易地将日常工作转换为VBA 程序代码，使工作自动化。因此，对于在工作中需要经常使用Office 套装软件的用户，学用VBA 有助于使工作自动化，提高工作效率。另外，由于VBA 可以直接应用Office 套装软件的各项强大功能，所以对于程序设计人员的程序设计和开发更加方便快捷。

可以认为VBA是非常流行的应用程序开发语言VISUAL BASIC 的子集。实际上VBA是寄生于VB应用程序的版本。VBA和VB的区别包括如下几个方面:

1. VB是设计用于创建标准的应用程序，而VBA是使已有的应用程序(EXCEL等)自动化；

　　2. VB具有自己的开发环境，而VBA必须寄生于已有的应用程序；

　　3. 要运行VB开发的应用程序，用户不必安装VB，因为VB开发出的应用程序是可执行文件(*.EXE)，而VBA开发的程序必须依赖于它的父应用程序，例如EXCEL。

尽管存在这些不同，VBA和VB在结构上仍然十分相似。事实上，如果已经了解了VB，会发现学习VBA非常快。相应的，学完VBA会给学习VB打下坚实的基础。而且，当学会在EXCEL中用VBA创建解决方案后，即已具备在WORD、ACCESS、OUTLOOK、FOXPRO、PROWERPOINT中用VBA创建解决方案的大部分知识。

 

 

二、什么是Excel应用程序

 

Excel知道是什么了，那么Excel应用程序是什么呢？其实说起来很简单：利用Excel和VBA为平台，开发出来的电子数据表即称之为Excel应用程序。

 

 

三、什么是宏

 

宏是一系列的命令与函数，存储于 Visual Basic 的模块中，并在需要执行该项任务时可随时运行。如果经常在重复某项任务，那么可以用宏自动执行该任务。Office中，宏可以手动录制，也可以自己输入代码制作。

 

 

 

以上是VBA的三个概念，那么为什么要用VBA？为什么要以Excel为平台来开发程序？这里说几个简单的理由：

1. 当使用Excel为平台时，你的程序就可以利用Excel现有的功能，相当于站在一个小巨人的肩膀上，这就可以大大减少开发的周期；

2. 几乎所有的电脑中都有Excel，也有大量的人正在是用Excel，但并不是每个人都会使用VBA，当你了解VBA后，以前的很多问题就可以迎刃而解了；

3. Excel开发程序很容易，只要电脑中有Excel，基本不需要其他的文件，简简单单的复制与粘贴，就完成了程序的开发；

4. VBA的语言是相对容易学的语言，很容易上手，如果你熟悉VB，那你发现它们在语言方面是相通的，而如果你对Excel比较了解，那你也就很容易理解Excel的各种对象了。

 

 

 

 

㈡ VBA的Excel基本应用

 

Application对象是Excel对象模型的顶层，表示整个Excel应用程序。在VB应用程序中调用Excel，就是使用Application对象的属性、方法和事件。为此，首先要声明对象变量：

 

Dim VBExcel As Object ^①

 

或直接声明为Excel对象：

 

Dim VBExcel As Excel.Application

 

在声明对象变量之后，可用CreateObject函数或GetObject函数给变量赋值新的或已存在的Application对象引用。

1.       用CreateObject函数生成新的对象引用：

 

Set VBExcel=CreateObject ("Excel.Application")

 

字符串"Excel.Application"是提供Excel应用程序的编程ID，这个变量引用Excel应用程序本身。

2. 用GetObject函数打开已存在的对象引用：

 

Set AppExcel=GetObject("SAMP.XLS")

 

上面语句打开文件SAMP.XLS。

 

 

一、             Application对象常用的属性、方法

 

方法 / 属性	描述
Visible属性	取True或False，表明Excel应用程序是否可见
Left,Top属性	Excel窗口的位置
Height,Width属性	Excel窗口的大小
WindowState属性	指定窗口的状态，取：XIMaximized(最大化)X1 Minimized(最小化)x1 Normal(缺省)
Quit方法	退出Microsoft Excel
Calculate方法	重新计算所有打开的工作簿、工作表或单元格
Evaluate方法	求值数学表达式并返回结果

 

示例1：

求值数学表达式：

 

Dim VBExcel As Object

Set VBExcel=CreateObject ("Excel.Application")

X=VBExcel. Evaluate ("3+5*(cos (1/log (99. 9)))")

 

 

二、使用Excel应用程序

 

如前所述，在VB应用程序中调用Excel应用程序，就是使用Application对象的属性、方法和事件。下面分类给出其中常用的属性和方法。

 

1. 使用工作薄

 

Workbook对象代表Excel应用程序中当前打开的一个工作簿，包含在Workbooks集合中。可以通过Workbooks集合或表示当前活动工作簿的Active Workbook对象访问Workbook对象。

 

方法	描述
Add方法	创建新的空白工作簿，并将其添加到集合中
Open方法	打开工作簿
Activate方法	激活工作簿，使指定工作簿变为活动工作簿，以便作为Active Workbook对象使用
Save方法	按当前路径和名称保存现有工作簿(如是首次保存，则将其保存到缺省名称中，如BOOK1.XLS)
SaveAs方法	首次保存工作簿或用另一名称保存工作簿
Close方法	关闭工作簿
PrintOut方法	打印工作簿，语法为：PrintOut (from, To, Copies, Preview, Printer, ToFile, Collate)
	可选参数	描述
	From	打印的起始页号。如省略将从起始位置开始打印
	To	打印的终止页号。如省略将打印至最后一页
	Copies	要打印的份数。如省略将只打印一份
	Preview	如果为True则Excel打印指定对象之前进行打印预览。如果为False，或省略则立即打印该对象
	Printer	设置活动打印机的名称
	ToFile	如果为True则打印输出到文件
	Collate	如果为True则逐份打印每份副本

 

下面语句可将活动工作簿的2到5页打印3份：

 

ActiveWorkbook.PrintOut From:=2 To 5 Copies:=3

 

示例2：

生成、保存、关闭工作簿：

 

Dim VBExcel As Excel.Application

Set VBExcel= CreateObject("Excel.Application")

With VBExcel

.Workbooks.Add

With ActiveWorkbook

.Save As"C:\Temp\OUTPUT.XLS"

.Close

End With

.Quit

End With

 

本例实际操作过程详解：

1. 新建一个Excel表格，笔者保存在桌面，文件名为“Book1.xls”，打开后选择“视图→工具栏→Visual Basic”调出Visual Basic工具栏：

 

2. 点击红圈圈住的按钮进入Visual Basic编辑器：

 

3. 右键单击VBAProject，按图示插入一个模块：

 

4. 构建auto_open()函数,让代码在Book1.xls启动时就自动运行，在代码框中输入“Sub auto_open()”，回车后自动生成“End Sub”，将以上代码复制粘贴到两者中间，如图：

 

5. 保存退出VBA编辑器及Excel，建立C:\Temp目录：

 

6. 打开Book1.xls，会跳出一个提示框，点击“启用宏”，代码中的“auto_open()”事件在Book1.xls打开时自动运行：

 

 

 

7. 现在可以去看C:\Temp目录的情况了：

 

以下代码不再进行详细解说，感兴趣的话可以自行尝试。

 

2. 使用工作表

Sheets集合表示工作簿中所有的工作表。可以通过Sheets集合来访问、激活、增加、更名和删除工作表。一个Worksheet对象代表一个工作表。

常用的属性、方法有：

 

属性	描述
Worksheets属性	返回Sheets集合
Name属性	工作表更名
Add方法	创建新工作表并将其添加到工作簿中
Select方法	选择工作表
Copy方法	复制工作表
Move方法	将指定工作表移到工作簿的另一位置
Delete方法	删除指定工作表
PrintOut方法	打印工作表

 

示例3：将C盘工作簿中的工作表复制到F盘工作簿中：

 

Dim VBExcel As Excel.Application

Set VBExcel=CreateObject("Excel.Application")

With VBExcel

.Workbooks.Open "C:\Temp\OUTPUT.XLS"

.Workbooks.Open "F:\OUTPUT1.XLS"

.Workbooks("OUTPUT.XLS").Sheets ("Sales").Copy

.Workbooks("OUTPUT1.XLS)

.Workbooks("OUTPUT1.XLS").Save

.Workbooks("OUTPUT.XLS").Close

.Workbooks("OUTPUTI.XLS").Close

.Quit

End With

 

3. 使用单元范围

 

Range对象代表工作表的某一单元格、某一行、某一列、某一选定区域或者某一三维区域。

常用的属性、方法有：

 

方法 / 属性	描述
Range属性	Range (arg)其中arg为A1--样式符号，表示单个单元格或单元格区域
Cells属性	Cells (row , col )(其中row为行号，col为列号)表示单个单元格
ColumnWidth属性	指定区域中所有列的列宽
Rowl3eight属性	指定区域中所有行的行宽
Value属性	指定区域中所有单元格的值(缺省属性)
Formula属性	指定单元格的公式，由A1--样式引用
Select方法	选择范围
Copy方法	将范围的内容复制到剪贴板
C1earContents方法	清除范围的内容
Delete方法	删除指定单元范围

 

4. 使用图表

 

Chart对象代表工作簿中的图表。该图表既可为嵌人式图表(包含于ChartObject对象中)也可为分立的图表工作表。

常用方法有：

 

方法 / 属性	描述
Add方法	新建图表工作表。返回Chart对象。
PrineOut方法	打印图表。
ChartWizard方法	修改给定图表的属性，其语法为：ChartWizard(Source, Gallery, Format, P1otBy, CategoryLabels ,SeriesLabels, HasLegend, Title, CategoryTitle, ValueTitle, ExtraTitle)
	可选参数	描述
	Source	包含新图表的源数据的区域。如省略，将修改活动图表工作表或活动工作表中处于选定状态的嵌人式图表
	Gallery	图表类型。其值可为下列常量之一：xlArea
	Format	内置自动套用格式的编号。如省略，将选择默认值。
	P1otBy	指定系列中的数据是来自行(xlRows)还是列(xlColumns)
	CategoryLabels	表示包含分类标志的源区域内行数或列数的整数
	SeriesLabels	表示包含系列标志的源区域内行数或列数的整数
	HasLegend	若指定True，则图表将具有图例
	Title	图表标题文字
	CategoryTitle	分类轴标题文字
	ValueTitle	数值轴标题文字
	ExtraTitle	三维图表的系列轴标题，或二维图表的第二数值轴标题

 

可组合使用Add方法和ChartWizard方法，以创建包含工作表中数据的图表工作表。下例基于工作表"Sheetl"中单元格区域"A1:A20"中的数据生成新的折线图并打印。

 

With Charts.Add

.ChartWizard source:=Worksheets ("sheet1").Range ("a1:a20"),gallery:=xlLine,title:="折线图表"

.Printout

End With

 

5. 使用Excel工作表函数

在VB语句中可使用大部分的Excel工作表函数，可通过WorksheetFunction对象调用Excel工作表函数。下面的Sub过程用Min工作表函数求出指定区域中单元格的最小值，并通过消息框显示结果值。

 

Sub UseFunction()

Dim myRange As Range

Set myRange=Worksheets ("Sheet1").Range("B2:F10")

answer=Application.WorksheetFunction.Min(myRange)

MsgBox answer

End Sub

 

如果使用以区域引用为参数的工作表函数，必须指定一个Range对象。如可用Match工作表函数对A1：A10区域的所有单元格进行搜索。

 

Sub FindFirst()

my Var=Application.WorksheetFunction.Match (9,Worksheets( 1).Range("A1:A10"),0)

MsgBox myVar

End Sub

 

要在单元格中插人工作表函数，可将该函数指定为对应于Range对象的Formula属性值。在以下示例中，将当前工作簿Sheetl内A1：B3区域的Formula属性指定为RAND工作表函数(此函数产生二个随机数)。

 

Sub InsertFormula()

Worksheets ("Sheet1").Range("A1:B3").Formula="RAND()"

End Sub

 

 

 

 

 

最后，任何东西都不是万能的，Excel与VBA也是一样如，Excel是一个电子表格程序，如果你把它强加成数据库软件是不公平的，在处理较少的数据，比如几千行的，用Excel是比较理想的，而大量的数据时，你就应该考虑用数据库了，比如Microsoft Office中的Access等，或者将两个相结合。充分了解Excel的基本知识与VBA的编程技巧，你就会从中获得乐趣，让我们一起来吧，走进VBA的世界中。

 

 

 

 

注解：

①     代码部分用灰色底纹标识

 

参考文献

 

http://baike.baidu.com/view/88461.htm

http://hi.baidu.com/ztoxet/blog/item/f18149dfe2d29615495403f7.html

http://tech./2006-10/11609006399673.html

 

 

【P.S：本文是我08年上学期《网络化办公应用教程》的期末考试论文，大部分文字来源于网络（绝无侵权之意），自己排版、画表格，其中实例操作部分图片、文字原创为（因为第一次交上去老师要让加图片后重新提交，网上又找不到图片，就只好自己弄了- -|||）。应该不会有太多人去研究吧，只希望对一些想要研究的朋友能有帮助咯~~】