EXCEL_VBA教程完全版

免费 下载 - 分享 -- Excel VBA 应用 教程 —

目 录

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

...................................................................................................................1

...................................................................................................................1

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

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

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

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

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

一． Sub 过程 ........................................................ ......... 4

二． Function 函数 ................................................... ......... 5

三． Property 属性过程和 Event 事件过程 ....................................... .. 5

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

一．测试函数 ......................................................... ....... 5

二．数学函数 ......................................................... ....... 5

三．字符串函数 ........................................................ ...... 5

四．转换函数 ......................................................... ....... 6

五．时间函数 ......................................................... ....... 6

关闭 ............................................................. ........... 7

其他文件函数 ......................................................... ....... 7

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

.....................................................................................1

.....................................................................................1

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

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

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

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

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

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

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

2.3.1 保存宏到个人宏工作簿 ............................................... ... 4

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

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

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

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

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

3.5 给控件命名 ............................................................................................................................... ... 6

3.6 使用用户窗体 ..............................................................................................................................6

3.7 疑难解答 ............................................................................................................................... ....... 7

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

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

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

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

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

4.4 变量 ............................................................................................................................... ............... 9

4.4.1 变量的数据类型 .................................................. ...... 9

4.4.2 用 Dim 语句创建变量 ( 声明变量 ) ......................................... . 10

4.4.5 变量赋值 ..................................................... ........ 11

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

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

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

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

............................................................................................12

............................................................................................12

方法 1 ：尽量使用 VBA 原有的属性、方法和 W ORKSHEET 函数 ............................................................12

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

1 ．使用 With 语句。 .................................................... ...... 12

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

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

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

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

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

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

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

1 了解宏 ............................................................................................................................... ............. 15

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

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

4 录制宏 ............................................................................................................................... ............. 17

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

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

13 重新命名宏 ............................................................................................................................... ... 27

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

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

19 保存宏 ............................................................................................................................... ........... 32

20 打印宏 ............................................................................................................................... ........... 32

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

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

23VB 编辑窗口 ............................................................................................................................... .... 35

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

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

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

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

28 接下来 …… ............................................................................................................................... ... 39

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

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

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

7 句法和文法 ............................................................................................................................... ..... 45

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

9 了解 VBA 错误 ............................................................................................................................... ... 47

10 查找帮助 ............................................................................................................................... ....... 49

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

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

13 常数列表 ............................................................................................................................... ....... 51

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

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

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

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

24 学习对象 ............................................................................................................................... ....... 62

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

26 使用 R ANGE 属性 ..............................................................................................................................62

27 使用 C ELLS 属性 ..............................................................................................................................62

28 使用 O FFSET 属性 ............................................................................................................................63

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

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

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

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

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

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

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

19 内置常量 ............................................................................................................................... ....... 81

20 接下来 …… ............................................................................................................................... ... 82

1.D O L OOPS : D O … W HILE 和 D O … U NTIL ................................................................................................114

4.F OR … N EXT 循环 ...........................................................................................................................119

11.E RASE 函数 ............................................................................................................................... .... 131

12.LB OUND 函数和 UB OUND 函数 ..........................................................................................................131

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