【免费】《Excel-VBA教程.docx》涵盖了从基础到高级的VBA编程知识，旨在帮助用户掌握Excel环境中基于应用程序自动化的编程技能资源-CSDN下载

需积分: 0 85 浏览量 2025-05-15 14:55:19 上传评论收藏 3.02MB DOCX 举报

资源推荐

资源详情

资源评论

一、VBA 语言基础 ..........................................................................................................................1

第一节标识符.....................................................................................................................................................1

第二节运算符.....................................................................................................................................................1

第三节数据类型.................................................................................................................................................1

第四节变量与常量.............................................................................................................................................1

第五节数组.........................................................................................................................................................2

第六节注释和赋值语句.....................................................................................................................................2

第七节书写规范.................................................................................................................................................2

第八节判断语句.................................................................................................................................................2

第九节循环语句.................................................................................................................................................3

第十节其他类语句和错误语句处理.................................................................................................................4

第十一节过程和函数.........................................................................................................................................4

一．

Sub

过程

...................................................................................................................................................4

二．

Function

函数

...........................................................................................................................................5

三．

Property

属性过程和

Event

事件过程

....................................................................................................5

第十二节内部函数 ..............................................................................................................................................5

一．测试函数

..................................................................................................................................................5

二．数学函数

..................................................................................................................................................5

三．字符串函数

..............................................................................................................................................6

四．转换函数

..................................................................................................................................................6

五．时间函数

..................................................................................................................................................6

第十三节文件操作.............................................................................................................................................6

文件

..................................................................................................................................................................6

删除

..................................................................................................................................................................7

打开

..................................................................................................................................................................7

读入

..................................................................................................................................................................7

写入

..................................................................................................................................................................7

关闭

..................................................................................................................................................................7

其他文件函数

..................................................................................................................................................7

二、VISUAL BASIC 程序设计网络教学 ......................................................................................1

第一课 VBA 是什么 ........................................................................................................................1

1.1 VBA 是什么...................................................................................................................................................1

1.2 EXCEL 环境中基于应用程序自动化的优点...............................................................................................1

1.3 录制简单的宏................................................................................................................................................2

1.4 执行宏............................................................................................................................................................2

1.5 查看录制的代码............................................................................................................................................2

1.6 编辑录制的代码............................................................................................................................................3

1.7 录制宏的局限性............................................................................................................................................4

1.8 小结................................................................................................................................................................4

第二课处理录制的宏 .....................................................................................................................4

2.1 为宏指定快捷键............................................................................................................................................4

2.2 决定宏保存的位置........................................................................................................................................5

2.3 个人宏工作簿................................................................................................................................................5

2.3.1

保存宏到个人宏工作簿

........................................................................................................................5

2.3.2

使用并编辑个人宏工作簿中的宏

........................................................................................................5

2.4 将宏指定给按钮............................................................................................................................................6

2.5 将宏指定给图片或其他对象........................................................................................................................6

2.6 小结................................................................................................................................................................7

第三课学习控件..............................................................................................................................7

3.1 EXCEL 开发过程简介...................................................................................................................................7

3.2 认识不同的控件............................................................................................................................................7

3.3 向工作表添加控件........................................................................................................................................7

3.4 设置控件的特性............................................................................................................................................8

3.5 给控件命名....................................................................................................................................................8

3.6 使用用户窗体................................................................................................................................................8

3.7 疑难解答........................................................................................................................................................9

第四课理解变量和变量的作用 .....................................................................................................9

4.1 代码存在的位置：模块................................................................................................................................9

4.2 对模块的概览..............................................................................................................................................10

4.2.1

创建过程

..............................................................................................................................................10

4.2.2

运行宏

..................................................................................................................................................11

4.3 保存对模块所做的改变..............................................................................................................................11

4.4 变量..............................................................................................................................................................11

4.4.1

变量的数据类型

..................................................................................................................................11

4.4.2

用

Dim

语句创建变量

(

声明变量

).......................................................................................................13

4.4.3

变量命名的惯例

..................................................................................................................................13

4.4.4

使用数组

..............................................................................................................................................14

4.4.5

变量赋值

..............................................................................................................................................15

第五课利用 VBA 设置工作表使用权限.....................................................................................15

1．使用 WITH 语句。 .......................................................................................................................................17

2．使用对象变量。 ..........................................................................................................................................17

方法 3：减少对象的激活和选择 .....................................................................................................................17

方法 4：关闭屏幕更新 .....................................................................................................................................18

第六课提高 EXCEL 中 VBA 的效率 .........................................................................................19

方法 1：尽量使用 VBA 原有的属性、方法和 WORKSHEET 函数 ................................................................19

方法 2：尽量减少使用对象引用，尤其在循环中 .........................................................................................19

．使用

With

语句。

.....................................................................................................................................19

．使用对象变量。

......................................................................................................................................20

．在循环中要尽量减少对象的访问。

......................................................................................................20

方法 3：减少对象的激活和选择 .....................................................................................................................20

方法 4：关闭屏幕更新 .....................................................................................................................................20

第七课如何在 EXCEL 里使用定时器........................................................................................20

三、学习微软 EXCEL 2002 VBA 编程和 XML，ASP 技术 ..................................................22

第一章电子表格自动化简介和了解宏命令 ...............................................................................22

1 了解宏 .............................................................................................................................................................22

2 宏命令的普通应用 .........................................................................................................................................22

3 写宏之前的计划 .............................................................................................................................................23

4 录制宏 .............................................................................................................................................................24

5 运行宏 .............................................................................................................................................................26

6 修改宏代码 .....................................................................................................................................................26

7 添加注释 .........................................................................................................................................................29

8 分析宏代码 .....................................................................................................................................................29

9 清除宏代码 .....................................................................................................................................................30

10 测试修改好的宏 ...........................................................................................................................................31

11 两个层面运行宏的方法 ...............................................................................................................................32

12 完善你的宏代码 ...........................................................................................................................................32

13 重新命名宏 ...................................................................................................................................................34

14 运行宏的其它方法 .......................................................................................................................................34

15 使用键盘快捷键运行宏 ...............................................................................................................................34

16 通过菜单运行宏 ...........................................................................................................................................35

17 通过工具栏按钮运行宏 ...............................................................................................................................37

18 通过工作表里面的按钮运行宏 ...................................................................................................................38

19 保存宏 ...........................................................................................................................................................39

20 打印宏 ...........................................................................................................................................................39

21 保存宏在个人宏工作簿 ...............................................................................................................................40

22 打开含有宏的工作簿 ...................................................................................................................................41

23VB 编辑窗口..................................................................................................................................................42

24 了解工程浏览窗口 .......................................................................................................................................43

25 了解属性窗口 ...............................................................................................................................................43

26 了解代码窗口 ...............................................................................................................................................44

27 VB 编辑器里的其它窗口.............................................................................................................................46

28 接下来…… ...................................................................................................................................................46

第二章 VBA 第一步 .....................................................................................................................46

1 了解指令，模块和过程 .................................................................................................................................47

2 VBA 工程命名................................................................................................................................................47

3 模块重命名 .....................................................................................................................................................48

4 从其它工程调用过程 .....................................................................................................................................49

5 了解对象，属性和方法 .................................................................................................................................50

6 学习对象，属性和方法 .................................................................................................................................51

7 句法和文法 .....................................................................................................................................................53

8 打断很长的 VBA 语句...................................................................................................................................55

9 了解 VBA 错误...............................................................................................................................................55

10 查找帮助 .......................................................................................................................................................57

11 语法和编程快捷助手 ...................................................................................................................................58

12 属性/方法列表 ..............................................................................................................................................59

13 常数列表 .......................................................................................................................................................59

14 参数信息 .......................................................................................................................................................60

15 快速信息 .......................................................................................................................................................60

16 自动完成关键字 ...........................................................................................................................................60

17 缩进/凸出 ......................................................................................................................................................61

18 设置注释块/解除注释块 ..............................................................................................................................61

19 使用对象浏览器 ...........................................................................................................................................61

20 使用 VBA 对象库.........................................................................................................................................66

21 用对象浏览器来定位过程 ...........................................................................................................................67

22 使用立即窗口 ...............................................................................................................................................67

23 获取立即窗口里的信息 ...............................................................................................................................69

24 学习对象 .......................................................................................................................................................70

25 电子表格单元格操作 ...................................................................................................................................70

26 使用 RANGE 属性..........................................................................................................................................70

27 使用 CELLS 属性 ...........................................................................................................................................71

28 使用 OFFSET 属性 .........................................................................................................................................71

29 选择单元格的其它方法 ...............................................................................................................................72

30 选择行和列 ...................................................................................................................................................73

31 获取工作表信息 ...........................................................................................................................................73

32 往工作表输入数据 .......................................................................................................................................73

33 返回工作表中的信息 ...................................................................................................................................74

34 单元格格式 ...................................................................................................................................................74

35 移动，复制和删除单元格 ...........................................................................................................................74

36 操作工作簿和工作表 ...................................................................................................................................75

37 操作窗口（WINDOWS） ..............................................................................................................................76

38 管理 EXCEL 应用程序 ..................................................................................................................................76

39 接下来…… ...................................................................................................................................................77

第三章了解变量，数据类型和常量 ...........................................................................................77

1 保存 VBA 语句的结果...................................................................................................................................77

2 变量是什么 .....................................................................................................................................................77

3 数据类型 .........................................................................................................................................................78

4 如何产生变量 .................................................................................................................................................78

5 如何声明变量 .................................................................................................................................................79

6 明确变量的数据类型 .....................................................................................................................................80

7 变量赋值 .........................................................................................................................................................81

8 强制声明变量 .................................................................................................................................................84

9 了解变量范围 .................................................................................................................................................85

10 过程级别（当地）变量 ...............................................................................................................................85

11 模块级别变量 ...............................................................................................................................................85

12 工程级别变量 ...............................................................................................................................................86

13 变量的存活期 ...............................................................................................................................................86

14 了解和使用静态变量 ...................................................................................................................................86

15 声明和使用对象变量 ...................................................................................................................................87

16 使用明确的对象变量 ...................................................................................................................................88

17 查找变量定义 ...............................................................................................................................................88

18 在 VB 过程里面使用常量............................................................................................................................89

19 内置常量 .......................................................................................................................................................89

20 接下来…… ...................................................................................................................................................90

第四章 VBA 过程：子程序和函数 ..............................................................................................91

1.关于函数过程 .................................................................................................................................................91

2.创建函数过程 .................................................................................................................................................91

3.执行函数过程 .................................................................................................................................................92

4.从工作表里运行函数过程 .............................................................................................................................93

5.从另外一个 VBA 过程里运行函数过程.......................................................................................................94

6.传递参数 .........................................................................................................................................................95

7.明确参数类型 .................................................................................................................................................96

8.按地址和按值传递参数 .................................................................................................................................97

9.使用可选的参数 .............................................................................................................................................97

10.定位内置函数 ...............................................................................................................................................98

11.使用 MSGBOX 函数 ......................................................................................................................................99

12.MSGBOX 函数的运行值 .............................................................................................................................103

13.使用 INPUTBOX 函数 ..................................................................................................................................104

14.数据类型转变 .............................................................................................................................................105

15.使用 INPUTBOX 方法 ..................................................................................................................................106

16.使用主过程和子过程 .................................................................................................................................109

17.接下来…… .................................................................................................................................................111

第五章基于 VBA 做决定 ...........................................................................................................111

1.关系和逻辑运算符 .......................................................................................................................................111

2.IF…THEN 语句 ..............................................................................................................................................112

3.基于多于一个条件的决定 ...........................................................................................................................114

4.THE IF…THEN…ELSE 语句 ..........................................................................................................................115

5.IF…THEN…ELSEIF 语句...............................................................................................................................118

6.嵌套的 IF…THEN 语句 .................................................................................................................................119

7.SELECT CASE 语句.........................................................................................................................................120

8.和 CASE 子句一起使用 IS ............................................................................................................................121

9.确定 CASE 子句里数值的范围.....................................................................................................................122

10.在 CASE 子句里确定多个表达式...............................................................................................................123

11.接下来… .....................................................................................................................................................123

第六章在 VBA 中重复操作 .......................................................................................................123

1.DO LOOPS: DO…WHILE 和 DO…UNTIL .......................................................................................................123

2.观察过程执行 ...............................................................................................................................................126

3.WHILE…WEND 循环.....................................................................................................................................127

4.FOR…NEXT 循环..........................................................................................................................................128

5.FOR EACH…NEXT 循环 ................................................................................................................................129

7.提前跳出循环 ...............................................................................................................................................130

8.循环嵌套 .......................................................................................................................................................131

9.接下来… .......................................................................................................................................................131

第七章利用 VBA 数组管理数据清单和表格...........................................................................131

1.了解数组 .......................................................................................................................................................132

2.声明数组 .......................................................................................................................................................133

3.数组的上界和下界 .......................................................................................................................................133

4.在 VBA 过程里使用数组.............................................................................................................................134

5.数组和循环语句 ...........................................................................................................................................135

6.使用二维数组 ...............................................................................................................................................136

7.静态和动态数组 ...........................................................................................................................................137

8.数组函数 .......................................................................................................................................................139

9.ARRAY 函数...................................................................................................................................................139

10.ISARRAY 函数 .............................................................................................................................................140

11.ERASE 函数..................................................................................................................................................140

12.LBOUND 函数和 UBOUND 函数 .................................................................................................................141

13.数组中的错误 .............................................................................................................................................141

14.数组作为参数 .............................................................................................................................................143

15.接下来… .....................................................................................................................................................143

第八章利用 VBA 操纵文件和文件夹 .......................................................................................144

1.获取当前文件夹的名称（CURDIR 函数） .................................................................................................144

2.更改文件或文件夹名称（NAME 函数） ....................................................................................................145

3.检查文件或文件夹是否存在（DIR 函数） ................................................................................................145

4.获得文件修改的日期和时间（FILEDATETIME 函数）..............................................................................147

5.获得文件大小（FILELEN 函数） ................................................................................................................147

6.返回和设置文件属性（GETATTR 函数和 SETATTR 函数）......................................................................147

7.更改缺省文件夹或驱动器（CHDIR 语句和 CHDRIVE 语句） ..................................................................149

8.创建和删除文件夹（MKDIR 语句和 RMDIR 语句）.................................................................................149

9.复制文件（FILECOPY 语句）......................................................................................................................149

10.删除文件（KILL 语句） ............................................................................................................................151

11.从文件读取和写入数据（INPUT/OUTPUT）.............................................................................................151

12.文件访问类型 .............................................................................................................................................152

13.使用顺序文件 .............................................................................................................................................152

14.读取储存于顺序文件里的数据 .................................................................................................................152

15.逐行读取文件 .............................................................................................................................................152

16.从顺序文件中读取字符 .............................................................................................................................153

17.读取分隔文本文件 .....................................................................................................................................155

18.往顺序文件里写数据 .................................................................................................................................155

19.使用 WRITE # 和 PRINT # 语句..................................................................................................................156