excel2010 vba笔记 (实战教程)(基础实例)

VBA笔记

8-21

是什么：

微软开发出来的应用程序一种能共享通用的自动化语言，VBA能使已有的应用程序(excel等)自动化，可以创建自定义的解决方案.等同:可以用excel的宏语言来使excel自动化,使用word BASIC使word自动化,等等。VBA可以称作excel的“遥控器”.此外,如果你愿意,还可以将excel用做开发平台实现应用程序.

可以实现的功能

1. 使重复的任务自动化.

2. 自定义excel工具栏,菜单和界面.

3. 简化模板的使用.

4. 自定义excel,使其成为开发平台.

5. 创建报表.

6. 对数据进行复杂的操作和分析.

3.宏

3.1录制简单的宏

选择“工具”—“宏”—“录制新宏”—输入宏名—确定—开始录制（状态栏中显示“录制”）—结束宏录制（“工具”—“宏”—“停止录制”。）*开始录制并非一个按钮，而是你的一系列操作，宏会记录下来变为自己的操作。

3.2执行宏

选择任何一个单元格—选择“工具”—“宏”—“宏”（出现“宏”对话框）—选择相应的宏名—“执行”。

3.3查看录制的代码

工具”—“宏”—“宏”（显示“宏”对话框）—选择某个宏—“编辑”

VBA的编辑器窗口（VBE）

Sub 改变颜色() //宏名

’

’改变颜色 Macro

’ xw 记录的宏2000-6-10

’

’//以上五行录制时自动生成

Range("A5").Select //表示无论选择哪个单元格，最后都只作用于A5

With or//设置属性在选择区域的内部（开始录制属性）

.ColorIndex = 3 //颜色为3号色：红色

.Pattern = xlSolid //区域内部图案=纯色（录制时自动生成，可删）

PatternColorIndex = xlAutomatic //内部图案底纹颜色=自动（自动生成，可删）

End With //结束属性录制

End Sub//结束宏录制

一个名为练习的宏：

Sub 练习()

'

' 练习宏

'

'

With s(xlEdgeLeft)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

1

.Weight = xlMedium

End With

With s(xlEdgeTop)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlMedium

End With

With s(xlEdgeBottom)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlMedium

End With

With s(xlEdgeRight)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlMedium

End With

s(xlInsideVertical).LineStyle = xlNone

s(xlInsideHorizontal).LineStyle = xlNone

With or

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

.Color = 5296274

.TintAndShade = 0

.PatternTintAndShade = 0

End With

With

.Color = -16727809

.TintAndShade = 0

End With

End Sub

3.4为宏指定快捷键

创建宏时指定：只须在录制宏时在输入宏名后，在“快捷键”文本框中输入相应的键。

录制宏后指定：选择“工具”—“宏”，显示“宏”对话框—选择要指定快捷键的宏—“选项”—选项”对话框

3.5决定宏保存的位置

在创建时选择保存位置。

1）当前工作簿。（只有该工作簿打开时，该宏才可用。） 2）新工作簿。

3）个人宏工作簿。（要让某个宏在多个工作簿都能使用，那么就应当创建个人宏工作簿，并将宏保存于其中。个人宏工作簿保存在“XLSTART”文件夹中。）

3.6将宏指定给按钮

工具栏—“按钮”—添加按钮完成-“指定宏”对话框—选择宏-确定—改名按钮名称

3.7将宏指定给图片或其他对象

指定给图片：单击某个图片—单击菜单中“指定宏”进行设置即可；

2

将宏指定给“工具栏按钮”：

1）打开“HOUR2”工作簿，选择“工具”-“定义”，显示“自定义工具栏”对话框。

2）从“类别”列表框中选择“宏”，从“命令”列表框中选择“自定义按钮”。

3）将“自定义按钮”拖动到工具栏。4）右键单击该按钮，选择“指定宏”，显示“指定宏”对话框。

5）选择“格式化文本”并确定。6）单击“关闭”按钮，关闭“自定义工具栏”对话框。

7）试着在某个单元格中输入文本，单击工具栏按钮运行该宏。

4.控件

4.1 向工作表添加控件

4.2 设置控件的特性

5.理解变量

5.1创建一个简单的过程

1)创建一个名为"你叫什么名字"的过程.

2)在过程中输入如下代码:

Inputbox "输入你的名字:"

现在不要担心inputbox语句的语法,将在第六学时中了解到有关这条命令的更多信息.

3)按下F5键运行过程,这时会显示一个输入框,要求输入你的名字.

4)输入你的名字并按"确定"按钮,则结束该过程.

5.2变量的数据类型

VBA数据类型：byte ，string，variant, integer,single。。。

5.3声明变量

Dim 变量名 AS 数据类型

5.4简单的子程序

1)创建一个名为"显示你的名字"的子程序.

2)输入以下代码:

Public Sub 显示你的名字()

Dim s名字 As String

s名字 = Inputbox("请输入你的名字:")

Msgbox "你好"& s名字
End Sub

3)将鼠标放到过程中的任何地方,按下F5键运行过程,会显示一个输入框.

4)输入你自己的名字并按回车键,会显示一个消息框,显示的文字中包含你自己的名字.

5)单击"确定"按钮,返回过程中.

5.5使用数组

Dim array_name(n) As type (其中n是数组元素的个数) 例：Dim s学生名字(9) As Integer （保留10个学生名字）

Dim dyn_array() As type （N可以省略）

ReDim dyn_array()(array_size) ：在程序运行时用：ReDim语句指定数组的大小，array_size代表数组的新大小。

ReDim Preserve dyn_array(array_size)：保留数组的数值

5.6 变量赋值

Dim i人数 As Integer

Dim i考试成绩 As Integer

Dim i As Integer

i人数 = inputbox("输入学生的人数：")

ReDim Preserve i考试成绩(i数量)

For i = 1 to i人数

i考试成绩(i) = inputbox("输入考试成绩"& i )

Next

5.7使用常量

3

const S销项税率 As Long = 0.17

通常常量声明时用全大写字母以区分变量.

5.8公共级变量/常量

变： Public variablename As datatype

常： Public const CONSTANAME datatype = value

5.9作用域

1.某一过程顶部：局部or过程级； 2.某一模块顶部：模块级 3.所有过程（通用声明区域）：公共级/全局

8-22

语言基础

6.1标识符

标识变量、常量、过程、函数、类等。利用它可以完成对变量、常量、过程、函数、类等的引用。

*字母打头，不能与VB保留字重名，如public,private,dim,goto,next,with,integer,single等。

6.2运算符

1）赋值运算符

2）数学运算符&、+(字符连接符)、+(加)、-（减）、Mod（取余）、（整除）、*（乘）、/（除）、-（负号）、^（指数）

3）逻辑运算符Not（非）、And（与）、Or(或)、Xor(异或)、Eqv(相等)、Imp(隐含)

4）关系运算符=（相同）、<>（不等）、>（大于）、<（小于）、>=（不小于）、<=（不大于）、Like、Is

5）位运算符Not（逻辑非）、And（逻辑与）、Or（逻辑或）、Xor（逻辑异或）、Eqv（逻辑等）、Imp（隐含）

6.3数据类型

名称符号长度

字符串型String $字符长度(0-65400)

字节型Byte 无1

布尔型Boolean 无2

整数型Integer %2

长整数型Long &4

单精度型Single !4

双精度型Double #8

日期型Date 无8公元100/1/1-99/12/31

货币型Currency @8

小数点型Decimal 无14

变体型Variant 无以上任意类型，可变

对象型Object 无4

6.4变量与常量

Dim变量as类型'定义为局部变量，如Dimxyzasinteger

Private变量as类型'定义为私有变量，如Privatexyzasbyte

Public变量as类型'定义为公有变量，如Publicxyzassingle

Global变量as类型'定义为全局变量，如Globlalxyzasdate

Static变量as类型'定义为静态变量，如Staticxyzasdouble

*作用域的原则是，哪部份定义就在哪部份起作用

*常量为变量的一种特例，用Const定义，且定义时赋值，程序中不能改变值，见5.7和5.8节

6.5数组

同5.5节

6.6书写规范

1）VBA不区分标识符的字母大小写，一律认为是小写字母；

2）一行可以书写多条语句，各语句之间以冒号:分开；

3）一条语句可以多行书写，以空格加下划线_来标识下行为续行；

4

4）标识符最好能简洁明了，不造成歧义。

7语句

7.1判断语句

1）If„Then„Else语句：①IfconditionThen[statements][Elsestatements]。。

② IfconditionThen[statements]

[ElseIfconditionThenstatements][elseif。。lse…] …EndIf

例：IfNumber<10ThenDigits=1

ElseIfNumber<100ThenDigits=2ElseDigits=3EndIf

2）SelectCase„Case„EndCase语句：

SelectCasePid

Case“A101”

Price=200

Case“A102”

Price=300

……

CaseElse

Price=900

EndCase

3）Choose函数

choose(index,choce-1,choice-2,„,choice-n)，可以用来选择自变量串列中的一个值，并将其返回。

index为必要参数，数值表达式或字段，它的运算结果是一个数值，且界于1和可选择的项目数之间。

GetChoice=Choose(Ind,"Speedy","United","Federal")

4）Switch函数

Switch(expr-1,value-1[,expr-2,value-2_[,expr-n,value-n]])

switch函数和Choose函数类似，但它是以两个一组的方式返回所要的值，在串列中，最先为TRUE的值会被返回。expr为必要参数，要加以计算的Variant表达式。value为必要参数。如果相关的表达式为True，则返回此部分的数值或表达式，没有一个表达式为True，Switch会返回一个Null值。

7.2循环语句

1）ForNext语句以指定次数来重复执行一组语句

ForcounterstartToend[Step n] //step n缺省值为step 1

[statements]

[ExitFor]

[statements]

Next[counter]

如1：

ForWords=10To1Step 1 //建立10次循环

ForChars=0To9 / /建立10次循环

MyString=MyString&Chars //将数字添加到字符串中

NextChars //counter+1 进入下一次chars循环

MyString=MyString&"" //每一步words循环，添加一个空格

NextWords

2）ForEach„Next语句主要功能是对一个数组或集合对象进行，让所有元素重复执行一次

语句

ForEachelementIngroup

Statements

[Exitfor]

5

Statements

Next[element]

如1：

ForEachrang2Inrange1

or

.colorindex=6

.pattern=xlSolid

Endwith

Next

这上面一例中用到了With„EndWith语句，目的是省去对象多次调用，加快速度；语法

为：

Withobject

[statements]

EndWith

3）Do„loop语句在条件为true时，重复执行区块命令

Do{while|until}condition' //while为当型循环，until为直到型循环，顾名思义，不多说啦

Statements

Exitdo

Statements

Loop

8过程与函数

过程是构成程序的一个模块，往往用来完成一个相对独立的功能。过程可以使程序更清晰、更具结构性。

VBA具有四种过程：Sub过程、Function函数、Property属性过程和Event事件过程。

8.1 Sub过程

Sub过程的参数有两种传递方式：按值传递(ByVal)和按地址传递(ByRef)。如下例：

Subpassword(ByValxasinteger,ByRefyasinteger)

Ify=100theny=x+yelsey=x-y

x=x+100

Endsub

Subcall_password()

Dimx1asinteger

Dimy1asinteger

x1=12

y1=100

Call password(x1,y1) //‘调用过程方式：过程名(参数1,参数2„);2.过程名参数1,参数2„

x1,y1 //‘结果是12、112，y1按地址传递改变了值，而x1按值传递，未改变原值

Endsub

8.2 Function函数

Functionpassword(ByValxasinteger,byrefyasinteger)asboolean

Ify=100theny=x+ y elsey=x-y

x=x+100

ify=150thenpassword=trueelsepassword=false

EndFunction

Subcall_password()

Dimx1asinteger

Dimy1asinteger

6

x1=12

y1=100

if password (x1,y1）=false then‘调用函数：1.作为一个表达式放在=右端;2.作为参数使用

1

endif

Endsub

8.3Property属性过程和Event事件过程

技术比较复杂，请参考相关书籍。

9内部函数

9.1测试函数

IsNumeric(x)‘是否为数字,返回Boolean结果，TrueorFalse

IsDate(x)‘是否是日期,返回Boolean结果，TrueorFalse

IsEmpty（x）‘是否为Empty,返回Boolean结果，TrueorFalse

IsArray(x)‘指出变量是否为一个数组。

IsError(expression)‘指出表达式是否为一个错误值

IsNull(expression)‘指出表达式是否不包含任何有效数据(Null)。

IsObject(identifier)‘指出标识符是否表示对象变量

9.2数学函数

Sin(X)、Cos(X)、Tan(X)、Atan(x)三角函数，单位为弧度

Log(x)返回x的自然对数

Exp(x)返回ex

Abs(x)返回绝对值

Int(number)、Fix(number)都返回参数的整数部分，区别：Int将-8.4转换成-9，而Fix将-8.4转换成-8

Sgn(number)返回一个Variant(Integer)，指出参数的正负号

Sqr(number)返回一个Double，指定参数的平方根

VarType(varname)返回一个Integer，指出变量的子类型

Rnd（x）返回0-1之间的单精度数据，x为随机种子

9.3字符串函数

Trim(string)去掉string左右两端空白

Ltrim(string)去掉string左端空白

Rtrim(string)去掉string右端空白

Len(string)计算string长度

Left(string,x)取string左段x个字符组成的字符串

Right(string,x)取string右段x个字符组成的字符串

Mid(string,start,x)取string从start位开始的x个字符组成的字符串

Ucase(string)转换为大写

Lcase(string)转换为小写

Space(x)返回x个空白的字符串

Asc(string)返回一个integer，代表字符串中首字母的字符代码

Chr(charcode)返回string,其中包含有与指定的字符代码相关的字符

9.4转换函数

CBool(expression)转换为Boolean型

CByte(expression)转换为Byte型

CCur(expression)转换为Currency型

CDate(expression)转换为Date型

CDbl(expression)转换为Double型

7

CDec(expression)转换为Decemal型

CInt(expression)转换为Integer型

CLng(expression)转换为Long型

CSng(expression)转换为Single型

CStr(expression)转换为String型

CVar(expression)转换为Variant型

Val(string)转换为数据型

Str(number)转换为String

9.5时间函数

Now返回一个Variant(Date)，根据计算机系统设置的日期和时间来指定日期和时间。

Date返回包含系统日期的Variant(Date)。

Time返回一个指明当前系统时间的Variant(Date)。

Timer返回一个Single，代表从午夜开始到现在经过的秒数。

TimeSerial(hour,minute,second)返回一个Variant(Date)，包含具有具体时、分、秒的时间。

DateDiff(interval,date1,date2[,firstdayofweek[,firstweekofyear]])返回Variant(Long)的值，表示两个指定日期间的时间间隔数

Second(time)返回一个Variant(Integer)，其值为0到59之间的整数，表示一分钟之中

的某个秒

Minute(time)返回一个Variant(Integer)，其值为0到59之间的整数，表示一小时中的某分钟

Hour(time)返回一个Variant(Integer)，其值为0到23之间的整数，表示一天之中的某一钟点

Day(date)返回一个Variant(Integer)，其值为1到31之间的整数，表示一个月中的某一日

Month(date)返回一个Variant(Integer)，其值为1到12之间的整数，表示一年中的某月

Year(date)返回Variant(Integer)，包含表示年份的整数。

Weekday(date,[firstdayofweek])返回一个Variant(Integer)，包含一个整数，代表某个日期是星期几

8-26至9-6基础实例练习

1000000000认识工程窗口，包括：excel对象，窗体，模块等，彼此内部的对象，过程，函数等，在不声明为private 的情况下可用交互引用，不存在隔阂，写在对象工作表的过程和函数默认调用该模块的对象。

8

10．一些实用自学提升的指导

按F2：调出对象浏览器，通过指定库和输入相应关键字进行对象和属性查询：如想详细了解，选中对象，按F1调出帮助对话框

F4：调出属性窗口 F5 ：将鼠标放在某过程或函数，可运行该过程；F8分步运行，适合检错；CTRL+G 调出立即窗口

9

11．在打开的workbook的某个excel worksheet对象中练习

sheet2：

sheet3：

10

thisworkbook：

可以看出写在sheet 中的过程不需要加工作表的对象引用，写在workbook中的过程则需要添加工作表对象引用

例子：

sheet1中： range(“A1”).value=1

thisworkbook : sheets(1).range(“A1”).value=1

达到的效果相同，但写在不同的位置，表达语句不同。

再次观察以上实例，发现是不是这个样子？

11

12．在模块中进行练习

A添加模块在模块菜单上右击-插入-模块即可，添加窗体和类模块的方法相同。

B. 更改模块名称在其属性窗口的名称处更改即可

C．实际操作

过程模块

以下均为过程模块中依次输入的过程的截图，不同过程之间用长划线自动隔开

12

13

14

15

函数模块

16

数组模块

17

18

13. 在窗体中进行练习

a .窗体工具箱控件介绍

点击工具箱上的按钮，然后点击窗体，即出现相应控件，可选择控件调整大小，双击工具箱控件标识可连续添加多个，再点击后停止选择

19

B．将控件添加到窗体后可以通过更改属性来设置控件样式等：如下，在相应控件处有基础使用介绍，更多可自行实践摸索

20

仔细阅读上面的图例，你已经掌握基本使用下面是开始实际应用

创建一个新的窗体，本实例为“窗体实践” ：设计内部如下：

注意：1、图片空间 内部的图片需要在属性 picture 中进行上传设置然后更改 Picturesizemode （=0,1,2）

2、每一个控件都有其自己的“名称”，一定要设置，通过设置名称，我们就可以在代码中对其进行引用修改等操作。

“名称”表示该控件的名字，方便在VBA编码中引用；caption 表示该控件中填充的内容

21

在下面的例子中，使用概率的名称给了文字框，该文字框上方的文字为标签文字：

22

【不再一一举例】

C．右击你的窗体名字，选择查看代码，进行VBA编写

代码窗口的上方有两个复合框，左侧下拉菜单中有相应控件的名字，可点击，右侧下拉菜单中相应控件可执行的动作：

首先初始化窗体：选择 UserForm Initialize

设置控件的动作—只为需要的设置，不一定一一设置：

PS： 注意先更改 左上角的控件复合框；也可以从窗体中双击相应控件，然后选择相关动作。

23

以下两个私有模块 当选择某个选项按钮控件时 会改变另一个控件—列表中的内容。

以上设置中 调用了两个过程，我将这两个过程写在一个新建的辅助模块，如下：

回到窗体实践编码窗口继续：

由于用到了旋转按钮与文字框组合的形式，则需将二者的值关联起来，分别设置代码如下：（注意改变控件复合框的选择哦！）

24

完毕，该设置过程的控件已设置，现在设置关键的OK 和CANCEL按钮

25

D．使用 设计完的窗体实践：

在第一行输入标题如下：

点击窗体实践，弹出窗体实践对话框，输入相应值点确定观察变化。

窗体实践命令按钮的代码如下：PS首先你要更改这个按钮的“名称”，不是其显示的内容，在上面已经介绍过，名称是用来在VBA中使用的。我的按钮插在表4中，故代码会自动写入表4

26

点OK 后

观察可以发现，执行后并没有在第四行加入数据，而是替换原来第三行的数据，这是代码的bug，主要原因是OK按钮的代码中，是一A列非空单元格计数的，则只要不选择数学，改行的数据就会被替换，直到选择数学时，才会向下一行填充，小伙伴们可以改一下OK代码哈。简单的就是不以A列为准，而是以 必不为空的C列或者I列为准。

以上只是VBA最基础的实战练习，VBA功能非常强大，学会了基本的语法后，可以通过不断 的实战练习提升自己。

网上有很多VBA教程，不乏非常详细的，可自行下载。

27