VBA学习笔记.docx

VBA学习笔记.docx

《VBA学习笔记.docx》由会员分享，可在线阅读，更多相关《VBA学习笔记.docx（81页珍藏版）》请在冰豆网上搜索。

VBA学习笔记

EXCEL之VBA

学习笔记

姓名：

刘磊

时间：

2015年9

第一章VBA基础知识

1：

代码帮助：

F1

2：

代码换行：

下划线+空格+回车

3：

.常用代码操作excel中的对象

（1）、工作簿（Workbooks）

Workbooks（N）第N个工作簿

Workbooks（"工作簿名"）

ActiveWorkbook活动工作簿

ThisWorkBook代码所在工作簿

（2）、工作表（Worksheets）

Sheets（N）第N个工作表

Sheets（"工作表名"）

SheetN第N个工作表

ActiveSheet活动工作表

worksheets与Sheets的区别

（3）、单元格（cells）

Range（"单元格地址"）

Cells（行号,列号）

[A1]单元格简写

Activecell活动单元格

Selection当前被选取的区域

4：

常量与变量

（1.）常量：

常量是定义了之后就不做变化了。

常量定义格式：

Const常量名=常量表达式

（2）.变量:

在定义之后还能再次赋值

变量定义格式：

Dim变量As变量类型

5：

数据类型

（1.）VBA中的常见数据类型：

类型注释简写占用内存

Integer整型%2Byte

Single单精度!

4Byte

Double双精度#8Byte

Long长整型&4Byte

String字符型$定长或变长（变长字符串最多可包含大约

20亿（2^31）个字符。

定长字符串可包含1到大约64K（2^16）个字符。

）

Currency货币型@8Byte

6:

if条件语句

1.单行形式1（If...Then）

If条件判断Then条件成立结果

注意在单行形式中，按照If...Then判断的结果也可以执行多条语句。

所有语句必须在同一行上并且以冒号分开?

例子:

Subtest（）

If1>10Thena=a+1:

b=1+a:

c=1+b

EndSub

2.单行形式1（If条件判断Then条件成立Else条件不成立）

例子:

Subtest2（）

If1>1ThenMsgBox"yes"ElseMsgBox"no"

EndSub

3.块形式（If...Then…End）

If条件判断Then

条件成立结果

EndIf

例子:

Subtest3（）

If11>10Then

a=1+a

b=1+a

c=1+b

EndIf

EndSub

4.块形式的If嵌套

If条件判断Then

成立时的结果

ElseIf条件判断Then

成立时的结果

……

Else

不成立时的结果

EndIf

例子:

Sub等级判断（）

IfSheet1.Range（"b1"）>=90Then

Sheet1.Range（"b2"）="优"

ElseIfSheet1.Range（"b1"）>=80Then

Sheet1.Range（"b2"）="良"

ElseIfSheet1.Range（"b1"）>=70Then

Sheet1.Range（"b2"）="中"

Else

Sheet1.Range（"b2"）="差"

EndIf

EndSub

7：

select语句用于判断选择

Selectcase

Case1

Case2

…..

Caseelse

Endselect

8：

循环语句

（1）：

doloop语句

Do

…..

Loop

（2）:

dowhileloop语句

Dowhile（条件成立时候循环）

Loop

（3）dountilloop语句

Dountil（直到条件成立）

Loop

[注]：

while与until不但可以放在DO后面，也可以放在LOOP后面事实上有时在循环的最后一行进行判断，更具有意义。

Do[{While|Until}表达式]

[执行的一条或多条语句]

[ExitDo]

[执行的一条或多条语句]

Loop

---------------------------------------------------------------------------------

while：

当这个条件为True时就循环

until：

直到这个条件为True时就跳出循环

---------------------------------------------------------------------------------

或者可以使用下面这种语法:

Do

[执行的一条或多条语句]

[ExitDo]

[执行的一条或多条语句]

Loop[{While|Until}表达式]

---------------------------------------------------------------------------------

用Do…Loop循环要注意的几点:

1.While与Until是放在Do后面还是Loop后面，取决于是先判断再循环，还是先循环再判断。

前者则在Do后面，后者则在Loop后面。

2.可以在Do...Loop中的任何位置放置任意个数的ExitDo语句，随时跳出Do...Loop循环。

3.Do...Loop+If...Then+ExitDo通常结合使用.

4.如果ExitDo使用在嵌套的Do...Loop语句中，则ExitDo会将控制权转移到ExitDo所在位置的外层循环。

（4）：

foreachnext语句

Eg:

Subforeachnext循环1（）

DimrngAsRange,n!

（range为单元格对象）

ForEachrngInSheet1.Range（"a2:

a10"）取a2:

a10中的每个单元格

Ifrng="A1"Thenrng.Interior.ColorIndex=3

Next

EndSub

Subforeachnext循环2（）

DimwshAsWorksheet,nAsByte,mAsString（worksheet为工作表变量）

ForEachwshInWorksheets取当前工作表集合中的每个成员

n=n+1

Sheet1.Cells（n,3）=wsh.Name

Next

EndSub

9：

exit语句与end语句

（1）：

exit是退出当前语句

1.ExitDo

2.ExitFor

3.ExitFunction

4.ExitSub

（2）：

结束一个过程或块

End

EndFunction

EndIf

EndSelect

EndSub

[注]：

end有时候在某些地方的功能和exitfor的作用相同。

10：

跳转语句

GoToline无条件地转移到过程中指定的行。

Gosubreturn跳转到某行，而且能够返回。

注意太多的GoTo语句，会使程序代码不容易阅读及调试。

尽可能使用结构化控制语句（Do...Loop、For...Next、If...Then...Else、SelectCase）。

Forexample

Subgotoreturn（）

Dimi!

Fori=2To10

IfSheet1.Range（"a"&i）>1/3ThenGoSub100

Nexti

ExitSub

100:

（作为gosub的跳转标示符号）

Sheet1.Range（"b"&i）="迟到"

Return（return语句返回到跳转的地方）

EndSub

11：

对错误语句的处理

方法1：

OnErrorResumeNext当错误的时候继续执行下去

方法2：

OnErrorgoto当错误时候去哪儿。

12：

with语句

当对某个对象执行一系列的语句时，不用重复指出对象的名称。

Forexample

Subwith嵌套1（）

Range（"a1"）.Value="Whoami?

"

Range（"a1"）.Parent.Name="HelloWorld"

Range（"a1"）.Font.Size=20

Range（"a1"）.Font.Bold=True

EndSub

Subwith嵌套2（）

WithRange（"a1"）

.Value="Whoami?

"

.Parent.Name="HelloWorld"

With.Font

.Size=20

.Bold=True

EndWith

EndWith

EndSub

13:

VBA与公式

Forexample

Sub普通公式（）

Sheet1.Cells（1,3）="=a1+b1"

EndSub

Sub批量计算（）

DimiAsInteger

Fori=1To10

Sheet1.Cells（i,4）="=a"&i&"+b"&i

Nexti

EndSub

Sub数组公式（）

Range（"e1:

e10"）.FormulaArray="=a1:

a10+b1:

b10"（FormulaArray为数组公式）

EndSub

Sub公式带引号的计算（）

Cells（12,1）="=COUNTIF（A1:

A10,"">9""）"（如果公式当中含有引号，则需要添加双重引号，才能够使公式的输入格式正确）

Cells（12,2）="=sum（INDIRECT（""a1:

a10""））"

EndSub

14:

运算符

运算符是代表VBA某种运算功能的符号。

1）赋值运算符:

=

2）数学运算符:

&（字符连接符）、+（加）、-（减）、Mod（取余）、\（整除）、*（乘）、/（除）、-（负号）、^（指数）

3）逻辑运算符:

Not（非）、And（与）、Or（或）、Xor（异或相同为0，不同为1）、Eqv（相等，相同为1，不同为0）、Imp（隐含）

4）关系运算符:

=（相同）、<>（不等）、>（大于）、<（小于）、>=（不小于）、<=（不大于）、Like（判断两个字符串是否相同）

?

：

代表任何单一字符

*：

代表零个或多个字符。

[charlist]：

代表charlist.中的任何单一字符?

[!

charlist]：

代表不在charlist中的任何单一字符。

第二章工作簿以及工作表的操作

1：

VBA中工作表与工作簿的表示方法

1：

workbooks（“工作表的文件名”）

Workbooks（“工作表的文件名”）.parent返回工作簿对象的父对象

2：

工作簿引索号表示法

workbooks（数字）.name返回工作表的名称

3：

窗口表示方法

Windows.count返回当前excel工作簿打开的个数

Windows（N）.parent.Name返回第N个工作簿的名称

[注：

工作簿索引号的表示法与窗口表示法表示的工作簿的顺序相反。

]

2：

当前工作簿与活动工作簿

当前工作簿：

thisworkbook代码所在工作簿

活动工作簿：

activeworkbook已经激活的工作簿

[注]：

当前工作簿可能是已经激活的工作簿，也可能不是已经激活的工作簿。

3：

工作簿的基本操作

workbooks由当前所有在内存中打开的workbook对象组成的集合

（1）：

.新建工作簿

Sub新建工作簿（）

DimwkbAsWorkbook声明wkb为工作簿

Setwkb=Workbooks.Add新建工作秒簿

wkb.SaveAs"c:

\123.xls"保存为工作簿

EndSub

（2）.打开工作簿

Sub打开工作簿（）

DimwkbAsWorkbook

Setwkb=Workbooks.Open（"c:

\123.xls"）

EndSub

（3）.关闭工作簿

Sub关闭（）

Workbooks（"123"）.CloseTrue（默认为自动保存，不提示）

EndSub

（4）.文件复制与删除

Sub文件复制与删除（）

FileCopy"c:

\123.txt","c:

\321.txt"（对所有文件类型都起作用）

Kill"c:

\321.txt"

EndSub

4：

工作薄的应用实例

（1）判断文件是否存在

Sub文件是否存在（）

a=Dir（"c:

\123.xls"）（Dir函数用来取出路径下的目录文件）

Ifa=""Then

MsgBox"不存在"

Else

MsgBox"存在"

EndIf

EndSub

（2）打开指定目录下的文件

Sub打开指定目录下的文件（）

Dima$,n!

wbsAsWorkbook

a=Dir（"c:

\*.txt"）

Workbooks.Open"c:

\"&a

Do

a=Dir

Ifa<>""Then

Workbooks.Open"c:

\"&a

Else

ExitSub

EndIf

Loop

EndSub

5：

工作簿的表示方法

在workbook对象中，有一个SHEETS集合,其成员是worksheet对象或chart对象。

worksheets仅指的是工作表，而sheets包含图表，工作表，宏表等等

VBA中，经常在工作表之间转换或者对不同工作表中的单元格区域进行操作.

通常有下面几种方法:

（1）：

Sub直接使用工作表名称法（）

MsgBoxWorksheets（"我的工作表"）.Name

MsgBoxSheets（"我的图表"）.Name

EndSub

（2）Sub索引号表示法（）

MsgBoxWorksheets

（1）.Name

EndSub

（3）Sub工作表代码索引号表示法（）

MsgBoxSheets

（1）.Name

EndSub

（4）Sub直接取工作代码法（）

MsgBoxSheet1.Name

EndSub

（5）Sub活动工作表（）

MsgBoxActiveSheet.Name

EndSub

注意：

当工作簿包括工作表、宏表、图表等时，

使用索引号引用工作表如Sheets

（1）与

WorkSheets

（1）引用的可能不是同一个表。

Subworksheetss（）

MsgBoxWorksheets

（1）.Name

MsgBoxSheets

（1）.Name

EndSub

Subsheetss（）

Fori=1ToSheets.Count

MsgBoxSheets（i）.Name

Next

EndSub

6：

工作表集合的应用

（1）Sub遍历sheets下的所有对象（）

ForEachshsInSheets

k=k+1

Cells（k,1）=shs.Name

Next

EndSub

（2）Sub遍历worksheets下的所能对象（）

ForEachshsInWorksheets

k=k+1

Cells（k,2）=shs.Name

Next

EndSub

（3）Sub工作表存在与否（）

Dimsn$

ForEachshtInSheets

sn=sht.Name

Ifsn="我的工作表"Then

MsgBox"存在"

ExitSub

EndIf

Next

MsgBox"不存在"

EndSub

（4）Sub工作表存在与否1（）

Dimsn$

Fori=1ToSheets.Count（Sheets.Count指sheet里面的数量）

a=Sheets（i）.Name

IfSheets（i）.Name="我的工作表"Then

MsgBox"存在"

ExitSub

EndIf

Next

MsgBox"不存在"

EndSub

7：

工作表的增加与删除

Sheets.Add方法

表达式.Add（Before,After,Count,Type）

XlSheetType常量之一:

xlWorksheet工作表

xlChart图表

xlExcel4MacroSheet宏表

xlExcel4IntlMacroSheet对话框

默认值为xlWorksheet?

Sub新建sheets（）

Sheets.Add（默认在活动工作表之前添加一个工作表）

Sheets.AddSheets（"abc"）（在工作表名为ABC的工作表之前添加一个工作表）

Sheets.Add,Sheets（"abc"）（在工作表名为ABC的工作表之后添加一个工作表）

Sheets.Addafter:

=Sheets（"abc"）（与上式等价）

Sheets.AddCount:

=2（在活动工作表前添加两个工作表）

Sheets.Add,,2（与上式等价）

Sheets.Add,,,xlChart（添加图表）

EndSub

Sub删除工作表（）

Sheet10.Delete

EndSub

8：

工作表的删除与添加

如果想批量新建工作表，可以结果循环来制作

Sub新建1到12月份的工作表（）

Dimj%

Forj=12To1Step-1

Sheets.Add.Name=j&"月"

Next

EndSub

'删除工作表

Sub删除sheet（）

OnErrorResumeNext（当出现错误时候忽略错误）

Application.DisplayAlerts=False（当屏幕有警告提示时候忽略开启）

Dimi%

Fori=1To12

Sheets（i&"月"）.Delete

Next

Application.DisplayAlerts=True（当屏幕有警告提示时候忽略关闭，否则，下次运行代码时候依旧是忽略关闭状态）

EndSub

9：

工作表的移动与复制

（1）工作表的复制

表达式.copy（Before,After）

Sub复制（）

Sheet1.CopySheets（Sheets.Count）

EndSub

（2）工作表的移动

'表达式.Move（Before,After）

Sub移动（）

Sheet1.Move,Sheet3

EndSub

10：

工作表的选择与激活

Worksheet.Select方法不支持隐藏选取

Worksheet.Activate方法支持隐藏选取

（1）：

Sub快速选择所有工作表（）

Worksheets.Select（只选择工作表）

Sheets.Select（工作表，图表等全部选择）

EndSub

（2）：

Sub自定义选择（）

Worksheets（Array（1,3,5））.Select

EndSub

11：

拆分工作簿实例

Sub拆分到工作簿（）

DimwkAsWorkbook,ss$,k%声明wk为一个工作簿类型变量

Application.DisplayAlerts=False

ForEachshtInWorkbooks（"2-11.工作簿综合运用（拆分工作簿）"）.Sheets

Setwk=Workbooks.Addwk为一个对象，对象的方法为添加工作表

k=k+1

Workbooks

（1）.Sheets（k）.CopyWorkbooks

（2）.Sheets

（1）

ss=ThisWorkbook.Path&"\"&sht.Name&".xlsx"

wk.SaveAsss

wk.Close

Next

Application.DisplayAlerts=True

MsgBox"拆分工作簿完成！

"

EndSub

第三章：

单元格区域操作

1：

range对象

单元格对象在VBA中一个非常基础，同时也很重要的。

它的表达方式也是非常的多样化。

Range对象

代表某一单元格、某一行、某一列、某一选定区域（该区域可包含一个或若干连续单元格区域），或者某一三维区域。

Range（"文本型装单元格地址"）

range的常见写法

Subrng（）

Range（"a1"）.Select单元格

Range（"a:

a"）.Select列

Range（"1:

3"）.Select行

Range（"a1:

b10"）.Select相邻区域

Range（"a1:

d7,c4:

e8"）.Select不相个邻区域

Range（"a1:

d7c4:

e8"）.Select相交的区域

EndSub

2：

range的其他写法

Range（"a1:

b10"）.Select'一般写法

Range（"a1","b10"）.Select'变化写法1

Range（Range（"a1"）,Range（"b10"））.Select'变化写法2（方便以后可以使用变量替换）

Range（"a1"）=123（给单元格赋值）

注意：

1.如果在range前没有指定工作表，则默认为活动工作表

2.如果对象不是活动工作表（如活动图表），则会出现错误

Sub单元格对象例子（）

Debug.PrintRange（"a:

a"）.Count'计数工作表最大的行数（Debug.Print意思是在活动窗口中显示出来）

Debug.PrintRange（"1:

1"）.Count'计算工作表最大的列数

Debug.PrintApplication.CountA（Range（"a:

a"））'计算工作表已使用的行数

Debug.PrintApplication.CountA（Range（"1:

1"））'计算工作表已使用的列数

EndSub

3：

range变量与引用

（1）：

range的变化写法

1）:

range（"地址区域"）.range（"地址区域"）

Sub序号表示法（）

Range（"b2:

d4"）.Range（