现在的位置: 首页 > 其他 > 正文

Excel VBA学习笔记

2014年01月17日 其他 ⁄ 共 6932字 评论数 2 ⁄ 阅读 5,115 次

Excel_VBA
【总述】

VBA有对象、属性、方法、事件,其中对象是核心;

对象的操作如“对象.方法”、“对象.属性”或者“父对象.子对象.属性”;

对象:某个存在的东西,常见对象如:工作簿workbook、工作表worksheet、单元格range、图形shape等;

属性:对象的外部和内部特征,是对这个对象的设置,表述对象的某种状态或样子,是静态的,是一个名词,如大小、颜色、名称等;

方法:某个对象所能执行的动作,它是一个动词,而对象是一个名词,如Add、Copy、Delete、Move、Select;

事件:对象在某个状态下由用户或系统触发的动作,可以在代码中响应的一段代码;

过程:执行某些动作的代码组合,VBA的主体结构就是过程,VBA包括子过程(Sub过程)、函数过程(Function过程)和属性过程三种。

 

【变量定义】

Dim 变量 as 类型‘定义为局部变量’

Private 变量 as 类型‘定义为私有变量’

Public 变量 as 类型‘定义为公有变量’

Global 变量 as 类型‘定义为全局变量’

Static 变量 as 类型‘定义为静态变量’

数据类型是一类数据的集合,它决定了变量的占用空间及变量种类,合理的数据类型定义变量可以使程序具有更高的执行效率。

Byte 0~255的数字;Integer -32768到32768的数字;Long、Single、Double、Currency、Decimal、Variant依次为更大的数字范围;String文本;Boolean逻辑。

变量声明有助于提升程序执行效率,降低内存消耗,防止出错。

【变量赋值】

Let方法:用于对对象以外的变量赋值,是可选参数,如:Let A = 10,A=10。

Set方法:对对象变量进行赋值,是必选参数,如:Set A = Range(“A10”),A = [A10]。

对象变量销毁:Set A = Nothing

Sub过程名可跟本过程变量名同名,但不可跟公有变量同名;Function过程名不能与参数名同名。

 

【单元格引用】

【Range方式】

引用单元格对象的方式为:单元格的列标加行号作为参数,并且左右加上引号,如Range(“C3”)表示C3单元格。

引用区域时是利用区域左上角单元格地址加冒号再加右下角单元格地址为其参数,如Range(“A2:D6”),还可以通过索引号引用区域中的值,如Range(“A2:D6”)(1)代表A2,Range(“A2:D6”)(3)代表C2,索引号从左到右上,从上到下排序,索引号为小数时进行四舍五入;同样,索引内还可以是两个参数,第一参数表示行索引,第二参数表示列索引,如Range(“A2:D6”)(2,2)代表B3,参数还可以为0或负数,表示向左上偏移。Range的参数也支持表达式,即字符或数值运算、变量等,其中列标加双引号,不同类型参数间用&连接,如Range(“B”&1+2)表示B3,Range(“C”&i)表示C列i行的单元格,注,在变量型单元格引用中十分重要。Range也可以通过逗号分隔进行多区域引用,如Range(“A2:D6,E3”)表示A2:D6区域及单元格E3。

引用整行整列,Range(“2:2”)表示引用第2行,Range(“1:3”)表示引用第1到3行,Range(“A:A”)表示引用A列等。同样整行整列引用也可使用Rows和Columns完成,如Rows(“2”)表示引用第2行,Rows(“1:3”)表示引用第1到3行,Column(2)表示引用第2列,Column(“B”)也表示引用第2列,Column(“A:C”)表示引用A列到C列。

嵌套使用,Range支持利用单元格作为参数,具体语法为:Range(Cell1,Cell2),如Range(Range(“A2”),Range(“D6”))表示引用A2:D6区域,Range(Range(“A2:D6”),Range(“E3”))表示引用A2:E6区域,注,最大化区域。

【Range的常用方法属性】

Range.Copy

用于将单元格区域复制到指定的区域或剪切板中,语法为:Range.Copy Destination,参数Destination用于指定数据将要复制的新区域,如果省略该参数,Excel会将数据复制到剪贴板。

Range.pastespecial

选择性粘贴,语法:Range.pastespecial (Paste,Operation,SkipBlanks, Transpose),Paste参数,选择性粘贴的内容,参数值-4163(粘贴值)、-4123(粘贴公式)、11(粘贴公式和数字格式)、12(粘贴值和数字格式)

Range.Select

选择对象,可以选择单元格、区域等。

Range.Addresss

用于获取单元格的地址,语法为:Range.Address()

【Offset方式】

Offset是Range对象的属性,可对单元格或单元格区域做偏移引用。

Resize重置区域大小,返回调整后的区域,语法为:Range.Resize(Rowsize,ColumnSize);

Offset根据偏移量引用区域,基本语法:Range.Offset(Rowoffset,Columnoffset),行列偏移量可以为空,表示不偏移;

【Cells方式】

1、  WorkSheet.Cells(横坐标,纵坐标)形式

引用某工作表中行、列坐标所指定的单元格,基本语法为:[Sheet].Cells([RowIndex],[ColumnIndex]),如Sheets(3).Cells(2,4)表示第三个工作表中D2单元格。

2、  WorkSheet.Cells(行号,列标)形式

该方式通过确定的行号和列标引用,列表外需有双引号,且仅能引用一个单元格不能引用区域。

3、  Range.Cells(横坐标,纵坐标)形式

该方式以其父对象Range左上角单元格作为参照系,向下向右累加坐标系数来指定单元格,同时坐标还可以为0或负数,相应偏移方向为上或左。该形式中,还可用Range.Cells(索引号)形式,索引数从左向右从上向下依次排序。

【[A1]方式】

该方式在左、右中括号中直接录入单元格或者区域地址来引用目标的方式,它不区分大小写,也不区分相对引用还是绝对引用。

三种单元格引用方式比较:Cells方式在代码循环中可以进行行循环与列循环,缺点是无法引用区域;Range的优势在于支持自动列出成员,支持行列循环,缺点是书写不便;[A1]方式书写方便、可以引用区域,缺点是不支持循环不能自动列出成员。

 

【工作表引用】

1、  使用工作表名

如,激活sheet1,则可用:worksheets(“sheet1”).active或sheets(“sheet1”).active,其中,sheets的范围比worksheets大,除了包含工作表外还包含图表、宏表等,worksheets(“sheet1”).index则返回sheet1的索引号。

2、  使用索引号

如,激活sheet1,则可用:worksheets(1).active或sheets(1).active,注,如果工作簿中包含图表、宏表等,则sheets(2)可能并非表示第二个工作表,表示最后一个工作表则可以用sheets(sheets.count)。若需要同时选择当前三个工作表,则可以采用以下两种方式:sheets(array(“sheet1”,”sheet2”,”sheet3”)).select或sheets.select。

在最后一个工作表后添加一个工作表,并重命名为“新工作表”:

Sheets.add after:=sheets(sheets.count)

Activesheet.name = “新工作表”

【Sheets的常用方法属性】

Sheets.Add

该方法用于新建工作表,新建的工作表将成为活动工作表,语法:Sheets.Add(参数),可选参数有Before,After,Count,Type,如果忽略参数,则表示在当前活动工作表之前添加一个新工作表。如,在最前面插入一个新表Sheets.Add Sheets(1);在第二个表后插入“Sheets.Add , Sheets(2)”,第一个参数只需保留逗号即可,同样,也可以“Sheets.Add After:=Sheets(2)”,完全忽视第一个参数,直接对第二参数赋值,但必须注明After;如果需要在末尾新建三个图表,则可以“Sheets.Add , Sheets(Sheets.count), 3, xlchart”,其中,第三个参数为新建数量,第四个参数为新建类型,有xlworksheet、xlchart等,分别表示工作表和图表,默认为工作表。

 

【工作簿引用】

【workbooks的常用方法属性】

Workbooks.Add

用于新建一个工作簿,新工作簿将成为活动工作簿。

Workbooks.Close

表示关闭工作簿,其语法为:表达式.Close(Savechanges,Filename,Routeworkbook ),若工作簿有更改,且尚未命名,则使用Fliename,如果省略Filename则要求用户提供文件名,第三个参数为是否向收件人发送工作簿。

 

【数组】

数组主要用于批量地管理数据,提高代码执行速度,将区域对象转换成内存数组,从而减少读取时间。它是一个列表或一组数据表,由连续可索引的具有相同内在数据类型的元素所组成的集合,数组中每一元素都具有唯一的索引号。

数组存于内存中,VBA读取对象中的值慢于读取内存中的值。

数组按元素是否固定可分为动态数组和静态数组,按维数可分为一维数组、二维数组等。

【举例】

如A1:F1,单元格内取值从1到6,其数组表示可为”={1,2,3,4,5,6}”;如为纵列A1:A6单元格取值从上到下分别为1到6,则其数组表示可为”={1;2;3;4;5;6}”。行数组元素之间用逗号“,”,列数组元素之间用分号“;”,若为二维数组,则先用逗号“,”将第一行表示完,然后用分号“;”开始表示下一行。

数组的表示形式有两种:Arr(Item)和Arr(RowIndex,ColumnIndex),前者用于一维数组,后者用于二维数组;注意,默认状态下,数组索引值从0开始,最后一个元素的索引值为总元素个数减1。使用Option Base语句也可以更改默认的第一元素索引值,如Option Base 1表示数组索引值从1开始。

【数组使用】

Dim arr1() ‘首先,需要声明数组变量

Arr1 = Array(“甲”,”乙”,”丙”,”丁”) ‘其次,对数组进行赋值

Debug.print arr1(2) ‘最后,引用数组

声明数组变量

声明数组变量可以使用Dim、Static、Private或Public。若声明变量时即指明数组大小,则它是固定大小的数组,若程序动作后数组的大小可以被重置、改变,则它是动态数组。在声明数组变量时,可以通过To指定数组的第一元素索引号,也可以使用逗号分隔参数以指定数组的维数。当数组变量的参数是一个数值时,表示它是一维横向数组,元素个数等于该值加1。

Dim arr(5)——表示声明一个具有6个元素的一维横向数组,其数据类型为变体Variant;

Dim arr(4) as byte——表示声明一个具有5个元素的横向一维数组,其数据类型为Byte;

Dim arr(1 to 3) as string——表示声明一个具有3个元素的横向一维数组,数据类型为String,第一元素索引号为1;

Dim arr(1 To 3,3) as string——表示声明一个3行4列的二维数组,第一维第一元素索引值为1,第二维第一元素索引值为0;

数组变量赋值

数组赋值通常采用三种方式:利用循环逐个赋值、利用Array对一维数组变量赋值、直接将区域赋予数组。

循环赋值

Dim arr(3) as String, Item ‘声明变量

For Item = 0 to 3 ‘循环函数

Arr(Item) = Cells(Item+1,1) ‘循环程序语句

Next ‘循环

利用Array赋值

Dim arr as String

Arr = Array(“甲”,”乙”,”丙”,”丁”)

区域赋值

Dim rng

Rng = Activesheets.Usedrange.Value

声明动态数组时,需要Dim语句配合ReDim语句或ReDim Preserve语句来实现,ReDim重置数组大小时会使数组中的值丢失;而ReDim Preserve语句重置数组大小时可以保留原数组中的值。ReDim语句可以反复改变数组元素及维数等,但不能改变数据类型。

Transpose转置数组

工作表函数,在数组中可以用来转置数组,VBA中声明的一维数组总是横向的,当需要产生一维纵向数组时通常用Transpose函数实现转换。

举例,将星期一到星期日的横向数组赋值到A1:A7区域:

Sub 对纵向区域赋值()

Dim arr as Variant

Arr = Array(“星期一”, “星期二”, “星期三”, “星期四”, “星期五”, “星期六”, “星期日”)

数组从默认从0开始

 

【循环判断】

【条件判断】

IIF(条件,正确结果,错误结果)

IIF函数与工作表IF函数很相似,然而却不够灵活,主要体现在:第三参数必填、检验第三参数、出错后程序中断。然而,工作表IF函数并不能在VBA中使用。

IF…Then…Else…

Select Case语句

【循环语句】

For Next循环,语法:

For 变量 = 初值 to 末值[step 步长]

程序语句

[Exit for

程序语句]

Next [变量]

Do Loop循环,语法一:

Do while|until条件

程序语句

[Exit Do

程序语句]

Loop

语法二:

Do

程序语句

[exit do

程序语句]

Loop while|until条件

With语句

With语句可以在一个单一对象或一个用户定义类型上执行一系列的语句,它的主要作用是简化代码、提升执行速度及减少变量的作用。语法:

With 对象

程序语句

End with

 

【小技巧】

1、    一次性清除工作表中所有公式结果为错误值的单元格:

On Error Resume Next:Cells.SpecialCells(xlCellTypeFormulas,16)=””

2、    常见符号

Chr(10)回车符

chr(13)换行符

3、    快捷键

Alt+F11 VBE窗口

Alt+F8 宏对话框

Ctrl+R工程资源管理窗

4、    程序效率测试

通常将程序执行1000或10000次,初始用a = timer 结束用timer-a来获取程序执行时间

5、    代码编写规则:

1)添加注释信息

Rem 后跟空格再跟注释内容,如果需要在过程右方添加注释,则需要在注释前使用冒号,: rem 注释内容;

也可以使用半角单引号后跟注释内容,中间不需要有空格;

2)  长代码分行

从需要截断处加空格,再输入下画线_再单击回车;

3)  代码缩进

使用Tab或空格进行缩进对齐,便于阅读;

6、    选区(Selection)与活动单元格(ActiveCell)

Selection可以引用选择区域的所有单元格,可以是一个单元格也可以包含多个单元格甚至多个区域,但活动单元格仅有一个,且包含于选区中。在选区中,通常第一个选择的单元格为活动单元格,也可以使用代码激活,如Range.Activte,其中Range为一个待激活的单元格;还可以使用选区方法激活,如Selection(索引号).Activte,Selection.Cells(行号,列号).Activte,其中,若以最后一个单元格为活动单元格可用Selection(Selection.Count).Activte。选区和活动单元格只能用于当前工作表,如果加前置对象来指定工作表或者工作簿将产生错误。

Activte是一个方法,它可以激活工作表对象,相当于用左键单击工作表名。

Active活动的,当前的,

活动工作表:ActiveSheet

活动单元格:ActiveCell

活动工作薄:ActiveWorkbook

其中,Active等是Application对象的成员,故不可Sheet(1).ActiveCell形式。

UsedRange表示已用单元格区域,如果工作表中所有单元格都未曾使用则已用区域为A1;CurrentRegion表示当前以空行和空列为边界的区域;

Union单元格合集;Intersect单元格、区域交集;

End区属尾端单元格,定位某行列第一个或最后一个非空单元格,语法Range.End(参数),参数可为xlDown、xlToLeft、xlToRight、xlUP。

A列最后一个单元格的经典引用形式,Cells(Rows.count,1),等价于A65536等;

7、Debug.print实现在立即窗口中显示信息,通常在调试代码时使用;

8、引用未打开工作簿中单元格内容:

[a1] = “= ‘D:\[book1.xls]sheet1’!$A$1”

9、“”表示内容为文本,数组表示本身已经是文本形式,外不需要再加引号。

变量与文本之间连接需要有连接符

10、VBA与宏有一定差别:

宏中大都是工作表函数,较复杂。

 

» 声明:本站文章源于个人经验总结或书籍、互联网转载,内容仅用于个人学习,请勿转载,否则后果自负!

目前有 2 条留言 其中:访客:2 条, 博主:0 条

  1. 云中客 : 2014年04月20日16:58:28  -49楼 @回复 回复

    不错的站,我们2个站有很多相似的地方,做友情吗

  2. 博客大全 : 2014年05月02日09:32:05  -48楼 @回复 回复

    我曾经也对这些很感兴趣,现在不敢再想这些了

给我留言

留言无头像?