查看: 11318|回复: 22
请问如何实现将一个总表拆分成N个分表？
阅读权限20
在线时间 小时
本帖最后由 大卫124578 于
17:58 编辑
想求老师给帮忙用vba实现这样的功能提高我的工作效率，设置2各功能按钮，一个可以创建拆分多个独立excel，另一个可以创建一个excel工作簿自动生成多个sheet表。
介绍一下本人的工作，工作中面临大量的数据筛选，一个excel数据源里有3个或多个sheet，将近万条或更多条的数据，格式相同，每条数据又有20几项信息，我的工作就是要将这3个或多个sheet表中依据机构这个字段内容进行拆分，同一个机构的数据拆分复制到一个独立的excel里，excel名为机构名或机构列的信息为准，该表再存放到一个文件夹名叫‘分表’里。即：将总表拆分成：北京.xls，上海.xls，天津.xls，等等。
还没完，还是要将这个数据源的数据再创建一个独立的excel工作簿，工作簿的名称为《分表》。里面自动生成多个sheet表，同样的方式将内容复制到sheet里面，sheet的表名为机构名或机构列的信息为准。
这个自动生成的excel的工具但要注意几点：
1、机构名或机构列的信息有可能是字母、数字、汉字等不同类型。
2、之前借用别的老师做的程序，不太理想有时出现拆分出来的独立的excel容量夸大问题，如：北京.xls，里面就2条也就是占用几十KB空间,但该excel的实际大小为8MB.
3、解决数据源中的格式例如有表格、有颜色等，拆分后不保留的问题。
4、程序能自动判断第1行里，找到‘机构'在哪列以哪列为准，而不是死板的设定B列必须为‘机构'。
09:09 上传
点击文件名下载附件
11.12 KB, 下载次数: 203
17:58 上传
点击文件名下载附件
413.4 KB, 下载次数: 233
阅读权限20
在线时间 小时
以下的代码是一位尊敬的老师的作品，但在我应用过程中用着不太好使，数据量很大时程序就不能用了，请各位老师出手给改改。
[code=vb]Sub 导出台账()
Dim x As String, y%
& & x = Workbooks(1).Path&&'母表存放地址
& & y = Range(&C65536&).End(xlUp).Row '声明最后一行的行号
& && &&&Application.ScreenUpdating = False&&'冻结屏幕，以防屏幕抖动
For h = 2 To y&&'从第2行到最后一行
& & m = Cells(h, 3)
If Application.CountIf(Range(Cells(2, 3), Cells(h, 3)), m) = 1 Then&&'限制为不重复值
&&Range(&B1&).AutoFilter Field:=3, Criteria1:=m& &'以m值作为字段自动筛选
& & Columns(&A:AZ&).Copy& &'复制 A:Z 数据
& &&&Workbooks.Add&&'新建工作薄
& && &Selection.PasteSpecial Paste:=xlPasteAll& &'粘贴
& && &ActiveWorkbook.SaveAs Filename:=x & &\& & m & &.xls&
& && &ActiveWindow.Close&&'关闭新工作簿
& &&&End If
& & Next h
& &Selection.AutoFilter '关闭自动筛选
MsgBox &在& & x & &，生成了新文件，请查看！&, vbOKOnly, &o(∩_∩)o&
Sub Sortdata()&&'按工作单位排序
& & Dim i As Integer, row_d As Integer
& & row_d = Sheets(&总表&).Range(&A2&).End(xlDown).Row
& & right_end = Sheets(&总表&).Range(&A1&).End(xlToRight).Column
& & Range(Cells(2, 1), Cells(row_d, right_end)).Select
& & 'Range(&A2:L& & row_d).Select
& & Selection.Sort Key1:=Range(&C2&), Order1:=xlAscending, Header:=xlNo, _
& && &&&OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _
& && &&&:=xlPinYin
Sub sht_Name()& &'筛选出总表中的单位个数和名称
& & Dim i As Integer, row_d As Integer
& & Dim mycell As Range
& & Dim Nodupes As New Collection
& & On Error Resume Next
& & row_d = Sheets(&总表&).Range(&C2&).End(xlDown).Row
& & For Each mycell In Sheets(&总表&).Range(&C2:C& & row_d)
& && &&&Nodupes.Add mycell.Value, CStr(mycell.Value)
& & Next mycell
& & On Error GoTo 0
& & For Each Item In Nodupes
& && &&&Sheets(&总表&).Cells(i, 26).Value = Item
& && &&&i = i + 1
& & Next Item
Sub Add_sht()&&'根据筛选出的单位增加相应的工作表
& & Dim i As Integer, Row_cnt As Integer
& & Row_cnt = Application.WorksheetFunction.CountA(Sheets(&总表&).Range(&Z:Z&))
& & For i = 1 To Row_cnt
& && &&&Sheets.Add After:=ActiveSheet
& && &&&ActiveSheet.Name = Sheets(&总表&).Cells(i, 26).Value
& & Next i
& & Sheets(&总表&).Activate
Sub Copy_head()&&'复制表头
& & Dim i As Integer, row_d As Integer, right_end As Integer
& & row_d = Sheets(&总表&).Range(&Z1&).End(xlDown).Row
& & right_end = Sheets(&总表&).Range(&A1&).End(xlToRight).Column
& & Sheets(&总表&).Select
& & Range(Cells(1, 1), Cells(1, right_end)).Copy
& & For i = 1 To row_d
& && &&&Sheets(Sheets(&总表&).Cells(i, 26).Value).Select
& && &&&Range(Cells(1, 1), Cells(1, right_end)).Select
& && &&&ActiveSheet.Paste
& & Next i
Sub Add_data(sht_Name)& &'找出要取资料的区域
& & Dim i As Integer, j As Integer, row_d As Integer
& & Dim First_row As Integer, Last_row As Integer
& & On Error Resume Next
& & right_end = Sheets(&总表&).Range(&A1&).End(xlToRight).Column
& & With Sheets(&总表&)
& && &&&i = 1
& && &&&Do Until .Cells(i, 3).Value = sht_Name
& && && && &i = i + 1
& && &&&Loop
& && &&&First_row = i
& && &&&j = First_row
& && &&&Do Until .Cells(j, 3) && sht_Name
& && && && &j = j + 1
& && &&&Loop
& && &&&Last_row = j - 1
& & End With
& & Sheets(&总表&).Range(Cells(First_row, 1), Cells(Last_row, right_end)).Select
& & Selection.Copy
& & Sheets(sht_Name).Select
& & Range(&A2&).Select
& & ActiveSheet.Paste
& & With ActiveSheet
& && &&&row_d = .Range(&A2&).End(xlDown).Row + 1
& && &&&Range(&B& & row_d).Value = &合计&
& && &&&For i = 5 To right_end - 1
& && && && &Cells(row_d, i).Value = Application.WorksheetFunction.Sum(Range(Cells(2, i), Cells(row_d - 1, i)))
& && &&&Next i
& & End With
& & Sheets(&总表&).Activate
& & Range(&A2&).Select
Sub Paste_date()& &'将资料复制到相应的工作表
& & Dim i As Integer, row_d As Integer
& & On Error Resume Next
& & Sheets(&总表&).Activate
& & row_d = Sheets(&总表&).Range(&Z1&).End(xlDown).Row
& & For i = 1 To row_d
& && &&&Add_data (Sheets(&总表&).Cells(i, 26).Text)
& & Next i
& & Application.CutCopyMode = False
& & Sheets(&总表&).Range(&Z:Z&).Delete
Sub Finish()& &'整个过程
& & Sortdata
& & sht_Name
& & Add_sht
& & Copy_head
& & Paste_date
阅读权限20
在线时间 小时
Sub Finish()& &'整个过程
& & Sortdata
& & sht_Name
& & Add_sht
& & Copy_head
& & Paste_date
End Sub
复制代码
阅读权限50
在线时间 小时
这种你上个附件，少弄几个数据模拟下效果，要不然没法写
阅读权限95
在线时间 小时
一、简要说明要求，写的太多反而看不懂
二、上传模拟效果
阅读权限95
在线时间 小时
Private Sub CommandButton1_Click()
& & Dim cnn As Object, rs As Object, SQL$, p$, i&, s$, t$, f$, sh As Worksheet
& & Application.ScreenUpdating = False
& & For Each sh In Sheets
& && &&&If Len(t) Then t = t & & union all &
& && &&&t = t & &select * from [& & sh.Name & &$]&
& & Next
& & p = ThisWorkbook.Path & &\分表&
& & Set cnn = CreateObject(&ADODB.Connection&)
& & cnn.Open &provider=Microsoft.jet.OLEDB.4.0;extended properties=excel 8.0;data source=& & ThisWorkbook.FullName
& & SQL = &select distinct 部门 from (& & t & &)&
& & Set rs = CreateObject(&ADODB.Recordset&)
& & rs.Open SQL, cnn, 1, 3
& & For i = 1 To rs.RecordCount
& && &&&s = rs.Fields(0).Value
& && &&&f = p & s & &.xls&
& && &&&If Dir(f) && && Then Kill f
& && &&&SQL = &select * into [Excel 8.0;database=& & f & &].& & s & & from (& & t & &) where 部门='& & s & &'&
& && &&&cnn.Execute SQL
& && &&&rs.MoveNext
& & Next
& & rs.Close
& & cnn.Close
& & Set rs = Nothing
& & Set cnn = Nothing
& & Application.ScreenUpdating = True
& & MsgBox &ok&
End Sub
复制代码
阅读权限95
在线时间 小时
(16.34 KB, 下载次数: 442)
21:33 上传
点击文件名下载附件
阅读权限95
在线时间 小时
本帖最后由 zhaogang1960 于
22:20 编辑
上面是ADO法，下面是数组+字典法：Private Sub CommandButton1_Click()
& & Dim ary(), arr, brr, sh As Worksheet, d As Object, k, t, a, i&, j&, m&, l&
& & Application.ScreenUpdating = False
& & Application.DisplayAlerts = False
& & Set d = CreateObject(&scripting.dictionary&)
& & For l = 1 To Sheets.Count
& && &&&arr = Sheets(l).[a1].CurrentRegion
& && &&&If l = 1 Then ReDim ary(1 To
To UBound(arr, 2))
& && &&&For i = 2 To UBound(arr)
& && && && &m = m + 1
& && && && &d(arr(i, 2)) = d(arr(i, 2)) & &,& & m
& && && && &For j = 1 To UBound(arr, 2)
& && && && && & ary(m, j) = arr(i, j)
& && && && &Next
& && &&&Next
& & Next
& & k = d.Keys
& & t = d.Items
& & brr = [a1].Resize(65536, UBound(arr, 2))
& & For i = 0 To d.Count - 1
& && &&&m = 1
& && &&&a = Split(t(i), &,&)
& && &&&For j = 1 To UBound(a)
& && && && &m = m + 1
& && && && &For l = 1 To UBound(arr, 2)
& && && && && & brr(m, l) = ary(a(j), l)
& && && && &Next
& && &&&Next
& && &&&With Workbooks.Add(xlWBATWorksheet)
& && && && &With .Sheets(1).[a1].Resize(m, UBound(brr, 2))
& && && && && & .Value = brr
& && && && && & .Borders.LineStyle = xlContinuous
& && && && && & .EntireColumn.AutoFit
& && && && &End With
& && && && &.SaveAs Filename:=ThisWorkbook.Path & &\分表& & Replace(k(i), Chr(9), &&) & &.xls&
& && && && &.Close
& && &&&End With
& & Next
& & Application.DisplayAlerts = True
& & Application.ScreenUpdating = True
& & MsgBox &完毕&
End Sub
复制代码
阅读权限95
在线时间 小时
& & & & & & & &
本帖最后由 zhaogang1960 于
22:21 编辑
(15.03 KB, 下载次数: 332)
22:21 上传
点击文件名下载附件
阅读权限20
在线时间 小时
老师上传的ADO法，3个sheet中数据是一样的，系统报错。
另外，老师在2楼也是一个拆分的程序，程序有问题买就是虽然定义为65536行，但是实际调数大于32768条，就废了，请老师帮们看看。
(10.33 KB, 下载次数: 36)
22:00 上传
数组法已更新请测试&
最新热点 /1
ExcelHome每周都有线上直播公开课，
国内一流讲师真身分享，高手贴身答疑，
赶不上直播还能看录像，
关键居然是免费的！
厚木哥们都已经这么努力了，
你还好意思说学不好Office。
玩命加载中，请稍候
玩命加载中，请稍候
Powered by
本论坛言论纯属发表者个人意见，任何违反国家相关法律的言论，本站将协助国家相关部门追究发言者责任！ & & 本站特聘法律顾问：徐怀玉律师 李志群律师当前位置： >>
Excel与VBA程序设计
EX C E L 与 V B A 程序设计标题： 版本： 作者： Email： Blog： 时间：Excel 与 VBA 程序设计 0.10 马维峰
http://maweifeng.cnblogs.com 开始时间： 版本 0.10关于本书的说明I关于本书的说明笔者大概从 98 年开始学习 VB，从喜欢到失望，从失望到欣喜，从欣喜到平淡，大概 是很多自己一样的程序员的学习心路。而对于 Office 的强大功能，对于 VBA，领会的却很 晚。例如 Excel，虽然也一直知道其功能很强大，但到底如何强大，有什么有别于其他同 类软件的特色，却不甚清楚。大概在 2003 年，应工作需要，仔细查阅了一些 Excel 的资料， 开始学习 Excel 数据处理和 VBA 开发。因为当时已是一个熟练的 VB 程序员，所以 VBA 语法并不是难点和重点，而在很多书中没有很清楚讲解的问题，例如一个工作表内的某些 数据如何获得，某个或某几个单元格的值怎么高效的获取和赋值，如何打开关闭 Excel 文 件，如何正确部署最后的程序，如何绘制复杂的图表，等等诸如此类的问题，反而经常会 困惑自己很久。因此，本书将以笔者的学习经验为依据，以一个程序员的角度，讲解 Excel VBA 开发的种种问题， 并对一些笔者在实际中遇到的大多数 Excel VBA 开发的书中较少涉 及的内容作深入探讨，对于一些设计问题、效率问题、程序风格，书中都会给出笔者的建 议。 书中关于运行效率的说明，都经过笔者亲自测试，具体测试方法在运行效率一节有详 细说明。书中包括的代码风格之类的部分观点只是个人喜好问题，特此说明，读者可以根 据自己的判断取舍。写作过程中，有时会觉得有太多的问题需要说明，却限于篇幅，不能 一一展开；有时又不知如何下笔，不知如何才可以清楚简洁的讲清楚一个问题。对于很多 自己的经验或者教训， 只能在合适的时候插入只言片语， 古人言 “中有苦心而不能显” ， “中 有调剂而人不知” ，大概如是。 书中的代码、例子和文字是紧密配合的，没有了这些内容，也就失去了全书的灵魂所 在。很多的说明文字必须通过代码来体会，这是笔者很多年来自己的体会。 本书不求对 Excel 和 VBA 面面俱到的介绍，而且这也是不可能的。从内容选择和取舍 来说，本书更注重实用，从笔者的经验出发，从应用的角度来介绍 Excel VBA 的内容，而 不是相反。 目前写一本关于 VBA 的书好像有些不合时宜， 毕竟 VBA 和 VB 一样， 是属于 “落后” 和“过时”的技术，VB 6 的使用者，如笔者，大多已经转移到.net 平台之下，那么，VBA 的命运如何，我们还不得而知？但至少，在很长的一段时间内，作为 Office 开发的方式，Excel 与 VBA 程序设计Copyright ? 2005 马维峰关于本书的说明IIVBA 和 VSTO 应该会共存，而对于非专业程序员，首选应该还是 VBA，此为其一。其二， VBA 开发的核心在于 Office 的对象模型的掌握，而这也是本书的重点之所在。本书的读者应该可以较熟练的使用 Excel，例如可以使用公式，可以自定义公式；熟 悉基本的 Excel 的概念和名词，例如宏、加载宏。对于基本没有程序设计经验的读者，第 二章比较系统的介绍 VBA 语法和集成开发环境（IDE） ，对于熟悉 VBA 或 VB 的读者，可 以略过这一章。其实对于 Office 系列的开发，语法只是很小的一部分，主要的难点和问题 在于相应的对象模型及其应用，所以书中的大多数内容其实只是围绕 Excel 对象模型的解 释和讲解。详细来说，本书的读者可以细分为： 1. 2. 3. 应用 Excel 作为基础平台，提供相应解决方案的程序员； 各类科研工作者，应用 Excel 进行数据处理，这其实是本书最初的写作动机； 在各类企事业部门需要进行大量机械性和重复性的信息、数据处理工作，希望可 以利用 Excel 自动化这些工作的人员； 4. 其他对 Excel 自动化和 VBA 编程感兴趣的读者。本书使用的 Excel 版本是 2003， 但书中绝大多数内容并未涉及 Excel 2000 之后的内容； 除了少数内容，书中所介绍的内容也与 Excel 97 内容兼容。对于较新版本的内容，在介绍 时都尽可能的做了说明。 最后祝学习愉快！****** *** ********* *** ***关于书中的符号、提示、代码等的说明 z 所有关于菜单工具栏的操作以以下形式表示：“文件 － 打开”z 对于一些技巧，需要提醒说明的问题，文中都已以下形式做了说明：Excel 与 VBA 程序设计Copyright ? 2005 马维峰关于本书的说明III打开 IDE 环境的方法 z z 通过“工具 D 宏 D VISUAL BASIC 编辑器” 通过快捷键“ALT + F11” 右键单击工具栏，选择“VISUAL BASIC” ，此工具栏有录制宏，打开 VBA IDE 等的快捷按钮zz程序代码以以下方式显示（黄底、字体为 Courier New、5 号） ：#001 Function MyAdd(varA, varB) As Variant #002 MyAdd = varA + varB #003 End Function z 对于一些需要说明的问题，一般以脚注方式列出。Excel 与 VBA 程序设计Copyright ? 2005 马维峰目录i目录关于本书的说明 .............................................................................................................................. I 目 1. 录 .............................................................................................................................................. I 前言 ..........................................................................................................................................11.1. 1.2. 1.3. 1.4. 关于 EXCEL 和 VBA ...............................................................................................................1 EXCEL 作为开发平台 ..............................................................................................................2 宏、加载宏和 VBA ................................................................................................................3 本书的组织 .............................................................................................................................52.VBA 简介 .................................................................................................................................72.1. VBA 及其 IDE 初步 ...............................................................................................................7 VBA 集成开发环境（IDE）的组成 .............................................................................7 在 VBA IDE 下进行开发 ............................................................................................. 11 善用工具及其他 ...........................................................................................................13 模块 ...............................................................................................................................14 过程 ...............................................................................................................................16 函数 ...............................................................................................................................18 调用过程和函数 ...........................................................................................................19 常量和变量 ...................................................................................................................20 数据类型 .......................................................................................................................21 运算符 ...........................................................................................................................23 数组 ...............................................................................................................................24 自定义数据类型 ...........................................................................................................25 枚举类型 .......................................................................................................................26 变量的作用域（生存周期） .......................................................................................27 字符串 ...........................................................................................................................28 日期和时间 ...................................................................................................................30 处理简单的用户输入输出 ...........................................................................................31 控制程序流程 ...............................................................................................................32 条件语句 .......................................................................................................................32 循环语句 .......................................................................................................................35 With 语句 ......................................................................................................................38 Exit 语句 .......................................................................................................................39 设计用户窗体 ...............................................................................................................40 2.1.1. 2.1.2. 2.1.3. 2.2. 2.2.1. 2.2.2. 2.2.3. 2.2.4. 2.3. 2.3.1. 2.3.2. 2.3.3. 2.3.4. 2.3.5. 2.3.6. 2.3.7. 2.3.8. 2.3.9. 2.4. 2.4.1. 2.4.2. 2.4.3. 2.4.4. 2.4.5. 2.4.6. 2.5. 2.5.1.模块、函数和过程 ...............................................................................................................14数据类型与变量 ...................................................................................................................20VBA 语言基础 ......................................................................................................................31用户窗体 ...............................................................................................................................40Excel 与 VBA 程序设计Copyright ? 2005 马维峰目录ii2.5.2. 2.5.3. 2.6. 2.6.1. 2.6.2. 2.6.3. 2.7. 2.7.1. 2.7.2. 2.7.3. 2.7.4. 2.8. 2.8.1. 2.8.2. 2.8.3. 2.9. 2.10.事件驱动 .......................................................................................................................43 使用控件 .......................................................................................................................43 错误的类型 ...................................................................................................................45 使用 Debug 对象 ..........................................................................................................46 VBA 的调试工具..........................................................................................................46 设置错误捕获 ...............................................................................................................48 编写错误处理实用程序 ...............................................................................................48 提供从错误处理程序跳出的出口 ...............................................................................48 错误处理的简单示例 ...................................................................................................49 面向对象开发 ...............................................................................................................50 对象变量和对象 ...........................................................................................................51 创建类模块 ...................................................................................................................52调试 VBA 代码 .....................................................................................................................45错误处理 ...............................................................................................................................47类模块和面向对象 ...............................................................................................................50COM 对象的使用 .................................................................................................................55 集合对象 ...............................................................................................................................563.EXCEL 的对象模型 ..............................................................................................................583.1. 3.2. EXCEL 对象模型简介 ............................................................................................................58 APPLICATION 对象 .................................................................................................................60 控制 Excel 状态和显示的属性 ....................................................................................61 返回对象的属性 ...........................................................................................................62 执行操作 .......................................................................................................................64 Window 对象和 Windows 集合 ...................................................................................68 Application 事件 ..........................................................................................................68 Workbooks 集合 ............................................................................................................72 Workbook 的属性 .........................................................................................................73 Sheets 集合....................................................................................................................75 Workbook 的方法 ........................................................................................................76 Workbook 的事件 .........................................................................................................783.2.1. 3.2.2. 3.2.3. 3.2.4. 3.2.5. 3.3. 3.3.1. 3.3.2. 3.3.3. 3.3.4. 3.3.5. 3.4. 3.5.WORKBOOK 对象 ..................................................................................................................72WORKSHEET 对象 ..................................................................................................................79 RANGE 对象 ...........................................................................................................................81 返回或获得 Range 对象 ...............................................................................................82 Range 对象的常用属性和方法 ....................................................................................843.5.1. 3.5.2.4.数据处理 ................................................................................................................................884.1. 4.2. 概述 .......................................................................................................................................88 EXCEL 数据处理的方式和流程 ............................................................................................89 方式和流程 ...................................................................................................................89 “表格驱动”的数据处理 ...........................................................................................91 基于“过程”代码的数据处理 ...................................................................................93 基于“面向对象”代码的数据处理 ................................................................................944.2.1. 4.2.2. 4.2.3. 4.2.4. 4.3.操作数据文件 .......................................................................................................................96Copyright ? 2005 马维峰Excel 与 VBA 程序设计目录iii4.3.1. 4.3.2. 4.3.3. 4.4. 4.4.1. 4.4.2. 4.4.3. 4.4.4. 4.5. 4.5.1. 4.5.2. 4.5.3. 4.5.4.使用 Excel 对象操作数据文件 ....................................................................................97 使用 VBA 语句操作文件 ...........................................................................................104 FileSystemObject 对象模型....................................................................................... 115 工作表数据引用 .........................................................................................................124 操作文本 .....................................................................................................................127 操作数值 .....................................................................................................................131 Excel 数据表函数 .......................................................................................................135 实例 1：在 Excel 中应用 VBA 批量导入数据 .........................................................141 实例 2：在 Excel 中使用 VBA 来筛选数据 .............................................................144 实例 3：如何在多个文件中查找需要的信息 ..........................................................148 实例 4：批量重命名文件 ..........................................................................................154操作数据 .............................................................................................................................124应用实例 .............................................................................................................................1405.绘图 ......................................................................................................................................1575.1. 5.2. 5.3. 5.4. EXCEL 图表及其类型 ..........................................................................................................157 应用 VBA 代码绘图 ...........................................................................................................157 数据透视表和数据透视图 .................................................................................................157 应用实例 .............................................................................................................................157 实例 1：Excel 下三角图解的绘制 ............................................................................1575.4.1.6.界面设计 ..............................................................................................................................1606.1. 6.2. 6.3. 6.4. 6.5. 6.6. 界面的类型和选择 .............................................................................................................160 应用电子表格作为界面 .....................................................................................................162 自定义菜单和工具栏 .........................................................................................................162 EXCEL 内置对话框的使用 ..................................................................................................162 用户窗体 .............................................................................................................................162 应用实例 .............................................................................................................................1627.其他话题 ..............................................................................................................................1637.1. EXCEL VBA 程序的类型和部署 .........................................................................................163 Excel VBA 程序的类型..............................................................................................163 加载宏和一般电子表格程序的优缺点 .....................................................................163 部署 .............................................................................................................................164 7.1.1. 7.1.2. 7.1.3. 7.2. 7.3.VBA 程序的安全性和保护 ................................................................................................164 自动化其他 OFFICE 组件 ....................................................................................................165 启动其他 Office 组件 .................................................................................................166 与其他 Office 组件交互 .............................................................................................169 创建 Excel 对象 ..........................................................................................................171 Excel 自动化中的事件 ...............................................................................................172 使用 Excel 完成业务逻辑 ..........................................................................................173 使用自动化传输数据 .................................................................................................174 使用 ADO 操作 Excel 数据 .......................................................................................1787.3.1. 7.3.2. 7.4. 7.4.1. 7.4.2. 7.4.3. 7.5. 7.5.1. 7.5.2.通过其他程序自动化 EXCEL ..............................................................................................171EXCEL 数据导入导出的几种方式 ......................................................................................174Excel 与 VBA 程序设计Copyright ? 2005 马维峰目录iv7.5.3. 7.6. 7.7.使用第三方类库 .........................................................................................................179关于 EXCEL 工程的引用 .....................................................................................................180 提高效率的一些建议 .........................................................................................................181 尽量使用 Excel 的内置函数 ......................................................................................181 尽量减少使用对象引用 .............................................................................................182 高效使用 Range 对象 .................................................................................................183 减少对象的激活和选择 .............................................................................................184 关闭屏幕更新 .............................................................................................................184 提高关键代码的效率 .................................................................................................185 代码执行时间的测算 .................................................................................................1857.7.1. 7.7.2. 7.7.3. 7.7.4. 7.7.5. 7.7.6. 7.7.7.8.附录 ......................................................................................................................................1878.1. VBA 命名规则 ....................................................................................................................187 变量、常量、自定义类型和枚举 .............................................................................187 过程和函数 .................................................................................................................188 模块、类模块和用户窗体 .........................................................................................188 VBA 工程 ...................................................................................................................189 代码的排版 .................................................................................................................189 注释 .............................................................................................................................190 程序版本 .....................................................................................................................191 一些基本原则 .............................................................................................................191 8.1.1. 8.1.2. 8.1.3. 8.1.4. 8.2. 8.2.1. 8.2.2. 8.2.3. 8.2.4. 8.3.VBA 代码规范 ....................................................................................................................189可用于 VBA 代码的工作表函数列表 ...............................................................................192Excel 与 VBA 程序设计Copyright ? 2005 马维峰1. 前言11. 前言1.1. 关于 Excel 和 VBAMicrosoft Excel 不仅仅是一个被广泛应用的电子表格软件， Excel 除了具有一般电子表 格软件的数据处理、统计分析、图表功能外，Excel 最大的特点是集成了 VBA 环境。从 Office 97 开始，微软为所有的 Office 组件引入了统一的应用程序自动化语言DDVisual Basic For Application（VBA） ，并提供了 VBA 的 IDE 环境1。作为非常流行的应用程序开发 语言 Visual Basic 的子集，VBA 具有 VB 语言的大多数特征和易用性，它最大特点就是将 Excel 作为开发平台来开发应用程序，可以应用 Excel 的所有现有功能，例如其数据处理、 图表绘制、数据库连接、内置函数等等。 VBA 作为 Visual Basic 的应用程序的版本，与 Visual Basic 的区别包括如下几个方面： 1. Visual Basic 用于创建 Windows 应用程序，其代码最终被编译为可执行程序；而 VBA 是用于使已有的应用程序自动化，始终为解释执行； 2. Visual Basic 具有自己的开发环境，而 VBA 必须“寄生于”已有的应用程序，例 如 Office，或者其他应用程序； 3. Visual Basic 开发出的应用程序编译后可脱离 VB 环境执行，但执行 VBA 应用程 序要求用户访问相应的被“寄生的”应用程序，例如 Excel 下开发的 VBA 程序， 不仅要安装 Excel，而且安装时必须安装 VBA 环境才可以执行； 4. 使用 VBA 开发，可以使用相应“寄生”应用程序的已有功能，大大简化开发， 但同时，对于已有应用程序不擅长的任务，则较难实现。 尽管存在这些不同，Visual Basic 和 VBA 在结构上仍然非常相似。如果你已经了解了 Visual Basic，会发现学习 VBA 非常快；相应的掌握了 VBA 会给 Visual Basic 的学习打下 坚实的基础。当学会在 Excel 中用 VBA 创建解决方案后，你就已经具备了在其他 Office 应用程序，例如 Word、Access 等中用 VBA 创建解决方案的基本知识。另外，VBA 不仅仅 是应用在微软自己的应用程序中，从 VBA 5.0 起，微软开始为其他软件开发商提供 VBA1Office97 使用的 VBA 版本为 5.0， 在此之前并非所有 Office 组件都提供 VBA， 而且 VBA 并不提供 IDE， 类似于现在的 VBScipt。 Copyright ? 2005 马维峰Excel 与 VBA 程序设计1. 前言2的许可证2，允许在其他应用程序中集成 VBA，例如 CorelDraw、AutoCAD、ArcGIS 等软 件目前都集成了 VBA。VB, VBA, VBScipt 微软是制造概念和混乱的大师，例如 VB 家族，就有很多成员，不算退役的 QBASIC、 WORD BASIC 之类的语言，目前被广泛应用的基于 COM 技术的 VB 成员就有 VB、 VBA、VBSCRIPT。 从功能和概念上讲， VB & VBA & VBSCRIPT， 后者是前者的子集。 但实际上在 VB 中， VBA 起着基础的作用，提供了大多数语言级别的支持，打开 VB 和 VBA 工程的引用， 你都可以看到 VBA 的引用。虽然在 VB 开发环境和 VBA 环境下二者具体的 DLL 模 块不同，但说明了微软内部是共享 VB 语言的基础实现的。1.2. Excel 作为开发平台应用 Excel 作为开发工具，在目前主要有 2 方面的用途。 第一是作为一种日常事务和工作处理的脚本语言， 主要应用于类似办公自动化等领域。 例如办公室人员的重复性事务处理，科研人员的数据处理或模拟，公司或企业的简单的数 据处理汇总等等，在这种情况下，这也是过去很多年来 Excel 的主要应用方面，在此方面， 可以应用 Excel 实现以下功能： 1. 2. 3. 4. 5. 6. 使重复性的任务自动化； 自定义 Excel 中工具栏、菜单和窗体的界面； 简化模板的使用； 为 Excel 环境添加额外的功能； 对数据执行复杂的操作和分析； 自动绘制各类图表并进行自定义。2参见 http://www.msdn.microsoft.com/vba/companies/company.asp。 Copyright ? 2005 马维峰Excel 与 VBA 程序设计1. 前言3Excel 在科学研究中的应用 由于 EXCEL 友好的用户体验、强大的功能及其普及性，再加上 VBA 的帮助，使得 EXCEL 在科学研究中的应用越来越多，很多研究人员使用 EXCEL 记录实验数据，通 过公式、自定义公式、各类 EXCEL 加载宏处理数据，应用 EXCEL 编写数学模型的实 现。 在地学研究中，很多著名的软件，如 ISOPLOT，都是使用 EXCEL VBA 编写的；每年 的 COMPUTER AND GEOSCIENCES 上有大量基于 EXCEL 和 VBA 的程序发表。第二是作为企业应用的一个组件来使用，主要应用于企业应用程序的前端（表现层） 或领域层。在表现层，其实就是应用 Excel 开发用户界面，通过 COM 组件、Web Service、 ADO 或其他方式连接后端应用。另一种应用方式是通过其他程序，应用 COM 自动化技术 来调用 Excel，完成一些在 Excel 中很容易完成，但在其他程序设计语言或环境下比较困难 的任务，例如很多公司使用 Excel 作为报表工具。 随着 Office 2003 的发布，微软对智能客户端技术的推广，Office 和 VBA 在企业应用， 智能客户端方面的应用会越来越多， 针对 VBA 本身的不足， 微软推出的 VSTO （Visual Studio Tools for Office） ，使得开发人员可以应用.net 开发 Office 应用。 本书并没有专门涉及 Excel 在企业开发中的应用，所以内容和例子也基本上是围绕第 一类应用而展开的。但实际上，不管是应用 Excel 开发一般的数据处理程序还是应用 Excel 开发企业应用的前端，其技术和思路都是类似的，如何正确使用 Excel 的对象模型，如何 正确设计自己的程序是解决所有实际问题的基础。1.3. 宏、加载宏和 VBA本书并不打算涉及宏的录制和使用， 但进行 VBA 开发确实应该熟悉宏的录制和操作3。 Excel（包括其他 Office 程序）允许用户录制一段宏，并将其记录为 VBA 代码。对于开发 者，使用这一功能，一方面可以节省时间，将录制的宏代码作为开发的基础，另一方面， 对于不熟悉的操作，例如如何绘制图表，如何删除一行之类操作，可以录制一个宏并，通 过查看其 VBA 代码进行学习。录制的宏可以在 VBA 集成开发环境（IDE）中修改编辑， 可以为宏指定按钮、快捷键；而实际编写的代码也可以象宏一样在运行。如何录制宏可以3参见 Excel 帮助，基本上宏的录制非常简单，在需要录制的操作之前按下录制按钮，操作完成后停止录 制即可。录制完成后即可在 VBA IDE 下查看其代码。 Copyright ? 2005 马维峰Excel 与 VBA 程序设计1. 前言4参考 Excel 帮助或有关书籍。 加载宏程序是一类程序， 它们为 Microsoft Excel 添加可选的命令和功能。 例如， Excel 的“分析工具库”加载宏程序提供了一套数据统计分析工具，在进行复杂统计或工程分析 时，可用它来节省操作步骤。 Excel 有三种类型的加载宏程序：Excel 加载宏、自定义的组件对象模型（COM）加 载宏和自动化加载宏。本书所说的加载宏特指 Excel 加载宏（后缀为 xla 的文件） 。Excel 加载宏可以通过单击“工具 － 加载宏”菜单来调用，在加载宏对话框中，可以安装、卸 载加载宏，对于没有在对话框中的加载宏，可以通过浏览按钮定位相应的文件（图 1-1） 。图 1-1 Excel 加载宏对话框包含 VBA 代码的 Excel 文件， 可以通过选择 “文件 － 另存为” 对话框保存为加载宏。 VBA 是一种脚本语言，它将 Microsoft Office 中的每一个应用程序都看成一个对象。 Office 中，每个应用程序都由各自的 Application 对象代表。例如在 Word 中，Application 对象中包容了 Word 的菜单栏、工具栏、Word 命令以及文档对象等等。文档对象中则包容 了所有的文字、表格、图像等文档组成部分的相应对象。在 Excel 中，Application 对象中 包容了 Excel 的菜单栏、工具栏、工作薄和工作表对象、图表对象等等。其中，工作表对 象和图表对象是 Excel 中的主要对象。VBA 程序设计的主要任务就是通过编写代码操作这 些对象来完成一些任务。Excel 与 VBA 程序设计Copyright ? 2005 马维峰1. 前言5VBA 原理的隐喻 VBA 的基本原理可通过下图做示意性解释。VBA 作为应用 VBA 编写的代码和 OFFICE 对象之间的一个桥梁，为 2 者之间的调用 提供支持，这种调用是通过 COM 自动化实现的。例如我们的代码中一句代码，调用 OFFICE 中一个对象的一个属性，那么这个过程大概是类似这样的：VBA 环境解释执 行这句代码，如果发现对 OFFICE 对象的调用，就通过 COM 的方式调用这个对象， 获取其属性，这样 VBA 代码就可以和 OFFICE 对象进行交互。1.4. 本书的组织本书划分为以下几个部分：VBA 简介 介绍了 VBA 的 IDE 环境， 如何在 IDE 环境下进行开发， VBA 的数据类型， 基本语法， 模块和过程的概念，调试 VBA 程序，错误处理，如何设计用户窗体，面向对象编程的基 本概念及其类模块设计等内容。Excel 对象模型 VBA 对象模型介绍了 Excel 对象模型的架构和组成，Application 对象，Workbook 对 象，Worksheet 对象，Range 对象以及与这些对象相关的一些其他对象，分别介绍了各对象 的基本属性、方法和事件。数据处理 数据处理部分介绍了数据处理的概念、方式、流程、技术，重点介绍了数据处理的不 同方式： “表格驱动”的数据处理方式和基于代码的数据处理方式，介绍了其方式、优缺点 和差别；介绍了文件操作、数据操作等具体技术；最后给出了不同的应用实例。Excel 与 VBA 程序设计Copyright ? 2005 马维峰1. 前言6绘图界面设计其他话题 本部分作为全书的补充，介绍了在其他部分没有介绍和没有展开介绍的内容，包括 Excel VBA 程序的类型及其部署， VBA 程序的保护， 在 Excel 中使用 VBA 自动化其他 Office 组件，在 VB 等编程语言中使用 COM 自动化使用 Excel，Excel 数据的导入导出，提高效 率的方法和建议等内容。附录 主要介绍了 VBA 的命名规范和代码规范Excel 与 VBA 程序设计Copyright ? 2005 马维峰2. VBA 简介72. VBA 简介要使用 VBA 进行数据处理，第一要熟悉 VBA 的 IDE 环境，知道如何进行代码书写， 如何编写代码，设计窗体，创建类模块（对象） ，第二要熟悉 VBA 的基本语法和。二者都 是 VBA 程序设计的基础，需要认真学习。 VBA 语法不是一章就可以全部介绍完全的， 本章介绍的内容是最基本和应该熟练掌握 的内容，对于不熟悉或者不理解的内容可以在学习了后面的内容后再反过头来学习。有些 内容需要反复练习和熟悉。对于 VBA 语法和用法的很多内容可以随时通过查看帮助来获 得相关信息。 本章和下一章（Excel 对象模型）的部分内容，特别是表格内的一些内容，没有必要 完全记住，可以作为参考手册来使用。2.1. VBA 及其 IDE 初步本部分将对 VBA 及其开发环境 IDE（集成开发环境）作一概略的介绍。VBA IDE 是 进行程序设计和代码编写的地方，同一版本的 Office 共享同一 IDE。文中会涉及到一些诸 如对象、事件等部分读者可能不熟悉或不清楚的概念，对于此类问题可直接忽略之，因为 在后面会有详细介绍。本部分也不是一个 VBA 的参考文档，只是其语法、特征的快速浏 览和介绍。2.1.1. VBA 集成开发环境（IDE）的组成VBA 代码和 Excel 文件是保存在一起的， 可以通过点击 “工具 D 宏 D Visual Basic ，进行程序设计和代码编写。 编辑器”打开 VBA 的 IDE 环境（图 2-1）打开 IDE 环境的方法 z z 通过“工具 D 宏 D VISUAL BASIC 编辑器” 通过快捷键“ALT + F11” 右键单击工具栏，选择“Visual Basic” ，此工具栏有录制宏，打开 VBA IDE 等的 快捷按钮：zExcel 与 VBA 程序设计Copyright ? 2005 马维峰2. VBA 简介8图 2-1 Visual Basic IDE 环境图 2-1 为 Excel VBA 的 IDE 环境，对于所有使用同一版本 VBA 的应用程序，都共享 相同的 IDE 环境。 对于同一程序， 例如 Excel， 不管你打开几个 Excel 文件， 但启动的 VBA 的 IDE 环境只有一个。缺省情况下，VBA IDE 环境上方为菜单和工具条（图 2-1） ，左侧 上方窗口为工程资源管理器窗口，资源管理器窗口之下为属性窗口，右侧最大的窗口为代 码窗口。 在资源管理器窗口可以看的所有打开和加载的 Excel 文件及其加载宏。每一个 Excel 文件，对应的 VBA 工程都有 4 类对象，包括：Microsoft Excel 对象、窗体、模块和类模块 （图 2-2） 。Microsoft Excel 对象代表了 Excel 文件及其包括的工作薄等几个对象，包括所 有的 Sheet 和一个 Workbook，分别表示文件（工作薄）中所有的工作表（包括图表） ，例 如缺省情况下，Excel 文件包括 3 个 Sheet，在资源管理器窗口就包括 3 个 Sheet，名字分别 是各 Sheet 的名字。 ThisWorkbook 代表当前 Excel 文件。 双击这些对象会打开代码窗口 （图 2-1 右侧窗口） ，在此窗口中可输入相关的代码，响应工作薄或者文件的一些事件，例如文 件的打开、关闭，工作薄的激活、内容修改、选择等（有关事件、Excel 对象模型见后） 。 窗体对象代表了自定义对话框或界面，模块为自定义代码的载体，类模块则是以类或对象 的方式编写代码的载体，关于各对象的具体含义和使用见后。在工程资源管理器窗口的右Excel 与 VBA 程序设计Copyright ? 2005 马维峰2. VBA 简介9键菜单下，有添加用户窗体、模块、类模块的选项，也可以将已有的模块移除、导入和导 出。在工程资源管理器之下，也可以通过将一个工程中的模块用鼠标拖拽到另一个工程实 现模块在工程之间的拷贝。图 2-2 VBA 工程资源管理器窗口建议随时更改 Excel VBA 工程的名称，其缺省名称为“VBAProject” ，可以通过选中 工程，在属性窗口更改为有意义的名称，或者在菜单的“工具 － VBAProject 属性” 对话框中更改。在 VBA 工程资源管理器之下是属性窗口（图 2-3） ，主要用于对象属性的交互式设计 和定义，例如选中图 2-2 中的 VBAProject，在属性窗口即可更改其名称。属性窗口除了更 改工程、各对象、模块的基本属性外，主要用途是用户窗体（自定义对话框）的交互式设 计。图 2-3 显示的就是一个打开的窗体（UserForm）的属性窗口。Excel 与 VBA 程序设计Copyright ? 2005 马维峰2. VBA 简介10图 2-3 VBA 属性窗口在 IDE 窗口的右侧，可以打开代码窗口。在资源管理器窗口中的每一个对象会对应一 个代码窗口（用户窗体包括一个设计窗口和一个代码窗口） 。可以通过在对象上双击、在右 键菜单或资源管理器工具栏上选择查看代码（或对象）打开代码窗口。 对于 IDE 环境、菜单、工具栏的具体使用和说明，在后面的讲解中会逐步讲解说明。 单击“视图 － 对象浏览器”或工具栏上的“对象浏览器”按钮即可打开对象浏览器 窗口（图 2-4） ，在此窗口内可查看当前工程及其引用对象的属性、方法和事件。对象浏览 器对于熟悉和查看相应的 Excel 对象、引用对象（包括 COM 对象、其他 Excel 程序）所包 含的类、属性、方法和事件非常有用，特别是在没有相应的帮助资料或者文档的情况下， 对象浏览器是查看一个对象的内容的最有效的工具。Excel 与 VBA 程序设计Copyright ? 2005 马维峰2. VBA 简介11图 2-4 VBA IDE 环境的对象浏览器2.1.2. 在 VBA IDE 下进行开发熟悉了 VBA 的 IDE 环境后，我们来开发 VBA 之旅的第一个程序。新建一个 Excel 文“插 件， 通过菜单或键盘快捷键打开 VBA 集成开发环境， 在 VBAProject 上单击右键， 选择。这样，系统将打开一个代码窗口，在窗口中输入以下代码4。 入 － 模块” #001 Sub MyFirstVBAProgram() #002 Dim strName As String #003 Dim strHello As String #004 strName = InputBox(&请输入你的名字：&) #005 strHello = &你好，& & strName & &!& #006 MsgBox strHello #007 End Sub 将鼠标光标放置在这段代码之内，单击菜单“运行 － 运行子过程/用户窗体” ，或者 在工具栏单击运行按钮，则可运行这段代码。运行结果会显示一个对话框，输入一些内容4代码内的“#003”为行号，在实际代码中不能输入，在此只为文中叙述方便，之后不再重复。 Copyright ? 2005 马维峰Excel 与 VBA 程序设计2. VBA 简介12后，会显示相应的问候语。同样，这段代码可以和宏一样，在 Excel 下选择并执行。与其他程序设计语言不同，VBA 程序是事件驱动的，没有 Main 函数之类的入口的概 念。如果在 IDE 环境下，鼠标光标不在任何过程内，单击工具栏或运行菜单的运行， 会显示一个对话框，要你选择要运行的过程。 本质上，VBA 代码应该只是一些完成具体工作的集合，而通过界面元素或者 Excel 的 事件驱动执行，你可以通过自定义按钮、菜单，并指定一个宏（VBA 过程，自定义界 面也可以通过编程手段完成此类工作） ，通过单击此按钮即可调用相应的 VBA 代码， 或者将调用绑定在 Excel 的某个事件下。下面我们简单看一下这段代码的组成，代码第 1 行表示这是一个新的过程，名称为 “MyFirstVBAProgram” ，第 2、3 行定义了 2 个变量，其类型为字符串类型，第 4 行调用 InputBox 这个内置函数，并将返回值赋给 strName 这个变量，第 5 行将几个字符串组合成 一个新的字符串，第 6 行调用 MsgBox 这个函数，显示一个对话框，第 7 行表示过程结束。 VBA 程序由不同的模块组成，在模块内部，可以定义不同的变量、过程或函数，由此组成 一个完整的程序。代码窗口的设置 中文环境下 VBA IDE 代码窗口缺省的设置比较糟糕，字体为宋体，大小是 9 磅，使用 不很方便，可以在“工具 － 选项”对话框下的“编辑器格式”页内设置代码窗口字 体、颜色、背景。在此模块内，再新建一段代码： #001 Function MyAdd(varA, varB) As Variant #002 MyAdd = varA + varB #003 End Function 此段代码非常简单， 只有 3 行， 第 1 行表示这是一个函数， 具有 2 个参数 varA,和 varB， 函数与过程的差别在于函数有返回值，第 2 行将参数 varA,和 varB 的和赋给函数，代表其 返回值。函数无法直接运行，必须从工作表或者其他程序调用，例如，我们可以写以下一 段简单的程序调用此函数： #001 Sub TestAdd() #002 Dim a, b, c #003 a = 12 #004 b = 34Excel 与 VBA 程序设计 Copyright ? 2005 马维峰2. VBA 简介13#005 c = MyAdd(a, b) #006 MsgBox c #007 End Sub 其中第 5 行为函数 MyAdd 的调用，函数将返回值赋给 c。需要说明的是，VBA 中， 调用过程可以使用 Call 语句，也可省略，调用过程时，其参数的括号可以省略，但调用函 数必须有括号。 也可以直接在工作表内使用自定义的函数，例如在工作表中，我们可以和 Excel 内置 函数一样使用自定义的函数（图 2-5） ，Excel 会负责参数传递，将返回值赋给相应的单元 格，在引用参数改变时会自动重新计算，总之，与内置函数的使用没有什么不同。图 2-5 在工作表中使用自定义函数以上通过 2 个 例子简单介绍了 VBA 编程的过程和概念，后面我们将正式进入 VBA 编程之旅，逐步讲解模块、函数与过程、基本语法、数据类型、类模块与面向对象编程等 概念。2.1.3. 善用工具及其他VBA 集成开发环境，提供了很多便利的工具可以帮助或辅助我们写出好的程序，其中 的方便必须亲自使用才可以体会，因此，一定要善用工具5。 VBA 的代码编辑器提供了几项非常有用的功能，如代码大小写自动切换，代码自动格 式化，即时代码提示。代码自动大小写切换可以帮助我们发现拼写错误，如果我们所有的 过程和变量都是按照首字母大写的规则定义的，那么输入这些过程或者变量时，可以全部 小写，如果编辑器自动将其首字母改成了大写，那么说明拼写没有错误。代码即时提示可 以使我们不必记忆太多的东西，输入对象后会自动列出其属性、方法等内容；输入方法函 数后会提示参数信息。代码自动格式化可以使我们在书写代码时不必过于关心格式化的问 题，如等号前后加空格之类。 在实际的编程过程中，一定要善于利用在线帮助，VBA 的在线帮助包含了大量对编程5不仅是 VBA 开发，所有的程序开发工作中都应该善用工具。 ： ） Copyright ? 2005 马维峰Excel 与 VBA 程序设计2. VBA 简介14有用的参考信息，任何人都不可能记得住所有的函数、对象的用法和程序语言的语法，所 以一定要利用好帮助。帮助的使用可以在任何关键字上按 F1 键查找相关内容，也可以通 过帮助目录浏览，或者通过查找输入关键字查找相关内容。 VBA 的对象浏览器可以浏览一个对象的属性、方法和事件，通过对象浏览器，我们可 以获得一个对象的整体概念，特别对于某些没有提供帮助的对象或第三方对象，对象浏览 器更有帮助文档的作用。 VBA 中使用了很多常量，可以在编程中直接使用其代表的数字，也可以使用定义好的 常量，例如 MsgBox 中的一些参数（按钮参数 vbYesNo 等） 。使用常量一者可以获得好的 可读性，二者也容易记忆。关于 VBA 以及 Excel 的常量，可以随时通过帮助文档查阅。 在代码书写中，如果一句代码过长，应该使用接行字符（ “-” ）将其分为几行，而不是 书写为一行，一般来说，代码的长度不要超过 80 个字符为宜，这样阅读方便，不需要横向 拉动滚动条，也不容易出错。例如以下打开文件语句使用了 3 个接行符： Workbooks.OpenText Filename:=strFilename, _ Origin:=936, StartRow:=1, DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 1), Array(37, 1), _ Array(52,1),Array(64,1)), TrailingMinusNumbers:=True 对于代码格式，一定要养成缩进的习惯，在过程之后，循环语句、判断语句之内，如 本书的例子样子，缩进 4 个字符，便于阅读。代码中，在一个逻辑或者操作完成之后，应 该空一行，以表示其逻辑关系，在过程与过程之间，也应该空一行。 VBA 中，使用单引号“’”表示注释，编写程序时，一定要养成注释的习惯。注释不 是所有代码都要注释；一般来说，对一个模块、过程、函数，要大概说明其功能，参数； 对于一个过程，如果涉及较复杂的算法，要说明其使用的算法或流程。在过程和函数中， 对关键代码，说明其操作的目的、算法或流程。2.2. 模块、函数和过程2.2.1. 模块模块是自定义的过程、函数保存的地方，也是录制的宏保存的场所。有两种基本类型 的模块：类模块和标准模块，本节介绍标准模块，类模块将在专门介绍。模块可以通过右 键单击工程资源管理器的工程名，选择“插入 － 模块”来新建，新建的模块缺省的名称Excel 与 VBA 程序设计Copyright ? 2005 马维峰2. VBA 简介15为“模块 1” ， “模块 2”,建议在属性窗口内更改为有意义的名称。模块有 2 个任务： （1） 保存过程和函数； （2）定义模块内的私有变量或整个工程的公有变量。关于 VBA 中的命名 VBA 中可以为模块、函数、过程、变量赋于中文名称，但笔者不推荐这么做。至于可 能出现的在英文系统下的兼容性问题，在 Office 2000 以后，应该已不存在，只是一个 编程习惯问题。 另外，变量名称一般应该由 2 部分组成：类型（小写） + 含义（各单词首字母大写） ； 而 过 程则 由 一个 或 数个 表 示 其 意 义 的 单 词 组 成 ， 首 字 母 大 写 ， 例 如 strName ， ChangeName 等。对于过程内部的临时变量，如循环变量，则直接以 i、j、temp 之类 命名即可。详细的 VBA 命名规范和代码规范见附录。在模块中可以声明变量（包括对象） ，定义过程和函数。过程和函数的定义见下文，变 量的声明如下。 变量的声明： Dim i as Long Dim strName as String Dim 表示声明，i 和 strName 为变量名称，As 之后的 Long 表示数据类型（参见数据类 型一节） 。变量的声明还可以通过以下两种方式定义： Private i as Long Public strName as String Private 表示此变量是私有的，只有在此模块内部的函数、过程才可以访问；而 Public 则表示此变量为公用的， 可以在其他模块中访问使用。 应用 Dim 声明的变量也是私有变量。 一般来说，除非必要，一定要少使用公用变量。要求变量声明 VBA 缺省可以不声明变量，在第一次使用的时候自动声明，但此功能也是 VBA 代码 （ 包 括 其 他 Basic 代 码 ） 的 一 个 主 要 错 误 之 源 。 试 想 第 一 次 使 用 了 一 个 变 量 strMyFirstName，之后又通过 strMyFirstNme（少一个 a）来使用它，但由于拼写不同， VBA 以为是一个新的变量，于是会新声明一个变量，这样的错误极其难以发现。 可以通过“工具 － 选项”对话框，在“编辑器”页，选中“要求变量声明” ，则在使 用变量前，都必须先通过 Dim 语句声明。Excel 与 VBA 程序设计Copyright ? 2005 马维峰2. VBA 简介162.2.2. 过程过程是最基本的运行单位。一个完整的过程一般类似如下格式： Sub Test() … … End Sub 在以上程序中，Sub 代表过程种类，表示运行指定的操作，但不返回运行结果；Test 表示过程名称，最后以 End Sub 结束。过程的长度（行数） 过程并不限定代码行数，一个过程可以有一行到数百行代码，但随着过程或函数长度 的增加，错误也会随之增加，因此一般的编程书籍都建议过程（函数）的长度不要超 过计算机屏幕的一屏为宜，对于一些特殊的过程，也不要超过 200 行代码。对于太长 的代码可以分拆为几个子过程。正式的过程描述如下： [Private | Public] [Static] Sub name [(arglist)] [statements] [Exit Sub] [statements] End Sub 其组成和含义见表 2-1：表 2-1 过程的组成部分及其含义部分 Public Private Static name arglist statements 描述 可选的。表示所有模块的所有其它过程都可访问这个 Sub 过程。 如果在包含 Option Private 的模块中使用，则这个过程在该工程外是不可使用的。 可选的。表示只有在包含其声明的模块中的其它过程可以访问该 Sub 过程。 可选的。表示在调用之间保留 Sub 过程的局部变量的值。Static 属性对在 Sub 外 声明的变量不会产生影响，即使过程中也使用了这些变量。 必需的。Sub 的名称；遵循标准的变量命名约定。 可选的。代表在调用时要传递给 Sub 过程的参数的变量列表。多个变量则用逗号 隔开。 可选的。Sub 过程中所执行的任何语句组。其中的 arglist 参数的语法以及语法各个部分如下，描述见表 2-2：Excel 与 VBA 程序设计Copyright ? 2005 马维峰2. VBA 简介17[Optional] [ByVal | ByRef] [ParamArray] varname[( )] [As type] [= defaultvalue]表 2-2 过程参数的语法及其含义部分 Optional 描述 可选的。表示参数不是必需的关键字。如果使用了该选项，则 arglist 中的后 续参数都必须是可选的，而且必须都使用 Optional 关键字声明。如果使用了 ParamArray，则任何参数都不能使用 Optional。 ByVal ByRef ParamArray 可选的。表示该参数按值传递。 可选的。表示该参数按地址传递。ByRef 是 Visual Basic 的缺省选项。 可选的。 只用于 arglist 的最后一个参数， 指明最后这个参数是一个 Variant 元 素的 Optional 数组。使用 ParamArray 关键字可以提供任意数目的参数。 ParamArray 关键字不能与 ByVal，ByRef，或 Optional 一起使用。 varname type defaultvalue 必需的。代表参数的变量的名称；遵循标准的变量命名约定。 可选的。传递给该过程的参数的数据类型，参加数据类型一节。 可选的。任何常数或常数表达式。只对 Optional 参数合法。如果类型为 Object，则显式的缺省值只能是 Nothing。其中按值传递参数指一种将参数值而不是将地址传递给过程的方式，这就使过程访问 到变量的复本。结果，过程不可改变变量的真正值。按地址传递参数指一种将参数地址而 不是将值传递给过程的方式，这就使过程访问到实际的变量。结果，过程可改变变量的真 正值。VBA 缺省按地址传递参数。 例如以下程序，11 行的过程 ByValTest 的参数 var 是按值传递，因此不会改变参数的 值（5 行的调用） ，而 15 行的过程 ByRefTest 的参数 var 是按引用（地址）传递的，因此会 改变参数的值（6 行的调用） 。 #001 Sub ParameterTest() #002 Dim var1 As Long, var2 As Long #003 var1 = 12 #004 var2 = 12 #005 ByValTest var1 #006 ByRefTest var2 #007 MsgBox &还是 12: & & var1 #008 MsgBox &不是 12: & & var2 #009 End Sub #010 #011 Sub ByValTest(ByVal var As Long) #012 var = var * 2 #013 End Sub #014Excel 与 VBA 程序设计Copyright ? 2005 马维峰2. VBA 简介18#015 Sub ByRefTest(var As Long) #016 var = var * 2 #017 End Sub 不过和变量不同，如果没有使用 Public、Private 显式指定，Sub 过程按缺省情况就是 公用的（Public） ，因此一定要注意把不打算公开的过程定义为私有的。 所有的可执行代码都必须属于某个过程。不能在别的过程中定义 Sub 过程。 Exit Sub 语句使执行立即从一个 Sub 过程中退出。程序接着从调用该 Sub 过程的语 句下一条语句执行。在 Sub 过程的任何位置都可以有 Exit Sub 语句。 在 Sub 过程中使用的变量分为两类：一类是在过程内显式定义的，另一类则不是。 在过程内显式定义的变量（使用 Dim 方法）都是局部变量（参加数据类型一节） 。 过程（包括函数等）的创建可以通过在代码窗口直接键入“Sub…”来创建，也可以 使用菜单的“插入 － 过程”对话框来创建。2.2.3. 函数函数是具有返回值的过程，其正式描述如下： [Public | Private][Static] Function name [(arglist)] [As type] [statements] [name = expression] [Exit Function] [statements] [name = expression] End Function 各部分含义同 Sub 过程相同，在此不在重复。需要说明的是，函数要返回一个值，其 类型通过在定义时通过 As Type 来定义，要从函数返回一个值，只需将该值赋给函数名。 在过程的任意位置都可以出现这种赋值。如果没有对 name 赋值，则过程将返回一个缺省 值：数值函数返回 0，字符串函数返回一个零长度字符串 (&&)，Variant 函数则返回 Empty。 如果在返回对象引用的 Function 过程中没有将对象引用赋给 name（通过 Set） ，则函数返 回 Nothing。 VBA 中有大量内置函数，例如前边例子里使用过的 MsgBox，InputBox。VBA 的函数 主要包括数学函数（包括三角函数、随机数等） 、字符串函数等等，熟悉 VBA 的内置函数 可以提高工作效率，更好的完成工作。对于 VBA 的函数，可以参考其帮助文档。Excel 与 VBA 程序设计Copyright ? 2005 马维峰2. VBA 简介192.2.4. 调用过程和函数从其它过程调用一个过程（Sub）时，必须键入过程名称以及任何需要的参数值。Call 语句可有可无，如果使用它，则参数必须以括号括起来。 可以使用 Sub 过程去组织其它的过程。在下面的示例中，Sub 过程 Main 传递参数值 56 去调用 Sub 过程 MultiBeep（第 2 行） 。运行 MultiBeep 后，控件返回 Main，然后 Main 调用 Sub 过程 Message（第 3 行） 。Message 显示一个信息框；当按“确定”键时，控件会 返回 Main，接着 Main 退出执行。 #001 #002 #003 #004 #005 #006 #007 #008 #009 #010 #011 #012 #013 #014 Sub Main() MultiBeep 56 Message End Sub Sub MultiBeep(numbeeps) For counter = 1 To numbeeps Beep Next counter End Sub Sub Message() MsgBox &Time to take a break!& End Sub下面的示例展示了调用具有多个参数的 Sub 过程的两种不同方法。当第二次调用 HouseCalc 时，因为使用 Call 语句（第 3 行） ，所以需要利用括号将参数括起来。 #001 #002 #003 #004 #005 #006 #007 #008 #009 #010 #011 #012 Sub Main() HouseCalc 9 Call HouseCalc(500) End Sub Sub HouseCalc(price As Single, wage As Single) If 2.5 * wage &= 0.8 * price Then MsgBox &You cannot afford this house.& Else MsgBox &This house is affordable.& End If End Sub在调用函数（Function）时，为了使用函数的返回值，必须指定函数给变量，并且用 括号将参数封闭起来；如下示例所示：Excel 与 VBA 程序设计Copyright ? 2005 马维峰2. VBA 简介20Answer3 = MsgBox(&Are you happy?&, 4, &Question 3&) 如果不在意函数的返回值，可以用调用 Sub 过程的方式来调用函数。如下面示例所 示，可以省略括号，列出参数并且不要将函数指定给变量： MsgBox &Task Completed!&, 0, &Task Box& 如果在上述例子中包含括号，则语句会导致一个语法错误。2.3. 数据类型与变量2.3.1. 常量和变量变量用于保存在程序运行过程中需要临时保存的值或对象，变量具有不同的类型，例 如整型、浮点型（见后节） ，变量可能包含不同的数值，在程序运行时，变量的数值可以改 变。而当需要存储静态信息时，可以使用常量。使用常量有两个原因， （1）常量可以存放 数值供程序运行时多次引用而不改变； （2） 使用常量可以增加程序的可读性， 例如 BookTitle 比“Excel 与 VBA 程序设计”要容易记忆和修改。 定义变量可以使用 Dim 语句： Dim 变量名 As 数据类型 变量的名称变量名必须以字母开始，并且只能包含字母、数字和特定的特殊字符，不 能包含空格、句号、惊叹号，也不能包括字符@、&、$和#。名字最大长度为 255 字符。 在 Dim 语句中不必提供数据类型。如果没有提供数据类型，变量将被指定为 Variant 类型，因为 VBA 中默认的数据类型是 Variant。一般应该明确指定数据类型，因为这样程 序可读性更强；Variant 类型一般来说，要占用更多空间（16 字节） ，运行速度也会更慢一 。对于模块级别的变量，可以 些（根据不同数据类型，从基本无差别到大概慢 0.5～1 倍6） 使用 Public、Private 来定义（见上节） 。变量定义中要特别注意的一个问题是， VBA 的变量定义， 每个变量之后必须加 “As 数 据类型” ，例如： Dim i As Long, j As Long 而不可以这样：6本书所涉及的效率和速度快慢的说明虽然也参考了大量资料，但所有数据都经过笔者的测试，测试方法 见“其他话题”一章的“提高效率的一些建议”一节，一般都经过 3-5 次测试，取平均结果。 Copyright ? 2005 马维峰Excel 与 VBA 程序设计2. VBA 简介 Dim i, j As Long 这样，只有 j 是 Long 型，i 为 Variant 型。21要声明常量并设定常量的值，需要使用 Const 语句。常量声明后，不能对它赋一个新 的数值。例如，假设需要声明一个常量来保存书本价格，可以使用如下语句： Const BOOKPRICE As Long = 23.50 可以在 Const 语句中可以指定数据类型。常量的命名惯例是全部字母都用大写，这样 就容易区分代码中的变量和常量。 在 VBA 中，赋值表达式使用的是“=” ，和比较表达式相同，可以通过表达式给变量 赋值，如果表达式左右两侧的数据类型不同，VBA 会尝试进行自动数据类型转换，如果无 法转换，会发生一个类型不匹配的运行时错误。例如运行如下程序代码，第一和第二个对 话框（5 和 7 行）都可以显示，但第 8 行的赋值会产生一个类型不匹配的运行时错误。 #001 Sub TestType() #002 #003 Dim i As Long #004 i = 1 #005 MsgBox i #006 i = 12.12 #007 MsgBox i #008 i = &123a& #009 MsgBox i #010 #011 End Sub2.3.2. 数据类型数据类型，指变量的特性，用来决定可保存何种数据。数据类型包括 Byte、Boolean、 Integer、Long、Currency、Decimal、Single、Double、Date、String、Object、Variant（默认） 和用户定义类型等。 表 2-3 显示所支持的数据类型，以及存储空间大小与范围。表 2-3 VBA 的数据类型、存储空间大小、数值范围数据类型 Byte Boolean Integer 存储空间大小 1 个字节 2 个字节 2 个字节 0 到 255 True 或 False -32,768 到 32,767 范围Excel 与 VBA 程序设计Copyright ? 2005 马维峰2. VBA 简介 Long (长整型) Single (单精度浮点型) Double (双精度浮点型) 8 个字节 4 个字节 负数时从 -3. 到 -1.；正数时从 1. 到 3. 负数时从 -1.31E308 到 -4.47E-324；正数时从 4.47E-324 到 1.32E308 Currency (变比整型) Decimal 14 个字节 8 个字节 从 -922,337,203,685,477.5808 到 922,337,203,685,477.5807 没有小数点时为 +/-79,228,162,514,264,337,593,543,950,335， 而小数点右 边有 28 位数时为 +/-7.0335； 最小的非零值为 +/-0.0001 Date Object String (变长) String (定长) Variant (数字) Variant (字符) 用户自定义 （利用 Type） 22 个字节加字 符串长度 所有元素所需 数目 每个元素的范围与它本身的数据类型的范围相同。 与变长 String 有相同的范围 16 个字节 任何数字值，最大可达 Double 的范围 8 个字节 4 个字节 10 字节加字符 串长度 字符串长度 1 到大约 65,400 100 年 1 月 1 日 到 9999 年 12 月 31 日 任何 Object 引用 0 到大约 20 亿 4 个字节 -2,147,483,648 到 2,147,483,64722当使用 Variant 数据类型的时候， VBA 会根据实际需要将数据转换为特定的数据类型， 但有时 VBA 的自动转换并不正确，就需要使用类型转换函数。类型转换函数可以如下方 式使用： bResult = CBool(expression) 其中 expression 参数可以是任何字符串表达式或数值表达式，类型转换函数的类型和 使用说明见表 2-4，如果参数超出可以接受的范围会导致一个运行时错误。表 2-4 VBA 的类型转换函数及其说明函数 CBool CByte CCur CDate CDbl 返回类型 Boolean Byte Currency Date Double expression 参数范围 任何有效的字符串或数值表达式。 0 至 255。 -922,337,203,685,477.5808 至 922,337,203,685,477.5807。 任何有效的日期表达式。 负 数 从 -1.31E308 至 -4.47E-324 ； 正 数 从Excel 与 VBA 程序设计Copyright ? 2005 马维峰2. VBA 简介 4.47E-324 至 1.32E308。 CDec Decimal 零变比数值，即无小数位数值，为 +/-79,228,162,514,264,337,593,543,950,335。对于 28 位小数的数值，范 围则为+/-7.0335；最小的可能非零值是 0.0001。 CInt CLng CSng CStr CVar Integer Long Single String Variant -32,768 至 32,767，小数部分四舍五入。 -2,147,483,648 至 2,147,483,647，小数部分四舍五入。 负 数 为 -3. 至 -1. ； 正 数 为 1. 至 3.。 依据 expression 参数返回 Cstr。23若为数值， 则范围与 Double 相同； 若不为数值， 则范围与 String 相同。很多资料和书籍会告诉读者不要使用 Variant 类型，因为速度和空间占用。笔者也推荐 显式定义数据类型，但原因不是因为速度，而是程序的清晰和可读性。 Variant 代表 Integer 类型时，速度慢 50%，但作为 Double 的 Variant 类型，速度没有什 么差别，作为 Currency 类型的 Variant 类型，速度有些时候还要快一些。2.3.3. 运算符VBA 中的运算符有以下几类运算符： 1. 算术运算符，用来进行数学计算的运算符； 2. 比较运算符，用来进行比较的运算符； 3. 连接运算符，用来合并字符串的运算符； 4. 逻辑运算符，用来执行逻辑运算的运算符。 逻辑运算符和比较运算符将在介绍条件语句时介绍，连接运算符在介绍字符串时做介 绍，本小节只介绍算术运算符。 算术运算符有以下运算符： 1. ^ 运算符：求一个数字的某次方，如 A^B； 2. * 运算符：乘法运算； 3. / 运算符：除法运算； 4. \ 运算符：对两个数作除法并返回一个整数； 5. Mod 运算符：求两数的余数； 6. + 运算符：加法运算；Excel 与 VBA 程序设计Copyright ? 2005 马维峰2. VBA 简介247. - 运算符：减法运算。 当一个表达式牵扯到多个运算符时，就必须考虑运算符的优先顺序。运算符的优先顺 序是指在一个表达式中进行若干操作时，每一部分都会按预先确定的顺序进行计算求解， 称这个顺序为运算符的优先顺序。 在表达式中，当运算符不止一种时，要先处理算术运算符，接着处理比较运算符，然 后再处理逻辑运算符。所有比较运算符的优先顺序都相同；也就是说，要按它们出现的顺 序从左到右进行处理。而算术运算符和逻辑运算符则必须按下列优先顺序（由上至下）进 行处理（表 2-5） 。可以用括号改变优先顺序，强令表达式的某些部分优先运行。括号内的 运算总是优先于括号外的运算。但是，在括号之内，运算符的优先顺序不变。表 2-5 运算符的优先顺序算术 指数运算 (^) 负数 (C) 乘法和除法 (*、 /) 整数除法 (\) 求模运算 (Mod) 加法和减法 (+、 C) 字符串连接 (&) 比较 相等 (=) 不等 (&&) 小于 (&) 大于 (&) 小于或相等 (&=) 大于或相等 (&=) Like Is Not And Or Xor Eqv Imp 逻辑2.3.4. 数组数组是具有相同数据类型并且共享同一个名字的一组变量的集合。数组中的元素通过 索引数字加以区分。定义数组的语法如下： Dim ArrayName(n) As Type Dim ArrayName(a to b) As Type 其中 n、a、b 是数组中的元素的数目。n 表示数组元素为 0 到 n，共 n+1 个，a 表示数 组元素最小索引为 a，最大为 b，元素个数为(a-b+1)个。 例如，如果要创建保存 10 个学生名字的数组，可以使用如下语句： Dim strStudents(9) As String 注意，括号中的数字是 9 而不是 10。这是因为在默认情况下，第一个索引数字是 0。 数组在处理相似信息时非常有用。假设需要处理 15 门考试成绩，可以创建 15 个独立的变 量，也可以创建一个数组来保存考试成绩，具体语句如下：Excel 与 VBA 程序设计 Copyright ? 2005 马维峰2. VBA 简介25Dim iTestScores(14) As Integer 大多数情况下，可以使用像上面例子中类似的一维数组。VBA 也支持多维数组。可以 认为二维数组和工作表或者表格具有相似的结构。 要创建 4×4 的数组， 可以使用如下语句： Dim iTable(3,3) As Integer 声明数组时的另一种选择是不给定大小。这样，当程序开始运行时，就具有定义数组 大小的灵活性。如果声明数组时没有给定大小，就成为动态数组。声明动态数组的语法如 下： Dim DynamicArray() As Type 例如，你的应用程序让用户创建一张表格，可以提示用户确定要创建的表格的行和列 的数目。通过创建动态数组就可以做到这样，甚至还可以使用户在创建完表格（实际上是 数组）后对改变行或列的维数。 对数组进行声明后，可以在运行时用 ReDim 语句重新指定数组的大小： ReDim DynamicArray(size) 参数 size 代表数组的新大小。 如果要保留数组的数值， 请在 ReDim 语句后使用关键字 Preserve，具体语法如下： ReDim Preserve DynamicArray(size) 任何数据类型的数组都需要 20 个字节的内存空间， 加上每一数组维数占 4 个字节， 再 加上数据本身所占用的空间。数据所占用的内存空间可以用数据元数目乘上每个元素的大 小加以计算。例如，以 4 个 2 字节之 Integer 数据元所组成的一维数组中的数据，占 8 个 字节。这 8 个字节加上额外的 24 个字节，使得这个数组所需总内存空间为 32 个字节。包 含一数组的 Variant 比单独的一个数组需要多 12 个字节。2.3.5.}