现在的位置: 首页 > 数据库 > 正文

Excel VBA学习笔记(2)

2016年03月19日 数据库 ⁄ 共 8730字 暂无评论 ⁄ 阅读 1,279 次
文章目录

Excel_VBA

【总述】

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

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

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

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

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

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

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

【变量使用】

变量定义语法为Dim 变量名 as 数据类型,可同时定义多变量,多变量定义时之间用英文逗号隔开即可,如:Dim a as integer,b as integer。

定义变量时也可使用类型声明符来声明,如integer可用%,string可用$,使用如Dim a%,b$。

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

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

变量作用范围修饰:

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

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

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

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

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

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

变量赋值

变量赋值使用Let关键字,该关键字也可省略,如:Let A = 10,A=10。

注意,对象型变量(存在的对象,如工作表、单元格等)赋值使用关键字Set,如:

Dim a as range

set a = worksheets(“sheet1”).range(“A10”)

a.value = “单元格的值”

在上面的代码中看到了,字母a被定义成一个单元格,而不是一个值,给这个单元格赋值要在对象a后加其属性value。

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

Set方法:对对象变量进行赋值,是必选参数。

对象变量销毁:Set A = Nothing

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

【判断语句】

IF语法:

if 条件2 then

结果1

[elseif 条件2 then

结果2]

[else

结果3]

endif

其中,elseif为多分支判断时使用,可以省略;else为当所有条件不满足时的处理,也可以省略。

SELECT…CASE语法

select case 变量

case 条件1

结果1

[case 条件2

结果2]

[case else

结果3]

end select

在条件中,直接写值表示=判断,使用to来表示范围,用is来进行大小判断,如

Select Case score

Case Is < 0, Is > 100

str = "输入错误"

Case Is < 60

str = "不及格"

Case 60

str = "及格"

Case 60 To 80

str = "良"

Case Else

str = "优"

End Select

【循环语句】

For Next循环语法:

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

Next

Do while|until循环语法:

语法一:

Do while|until条件

Loop

语法二:

Do

Loop while|until条件

With语句

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

With 对象

属性赋值

End with

【对象】

【Excel VBA常用的对象】

Application:表示Excel应用程序;

Workbook:表示Excel工作簿,即代表一个Excel文件;

Worksheet:表示Excel工作表,即一个Excel文件里的一个sheet工作表;

Range:表示Excel工作表中的单元格,可以是单个,也可以是一个范围;

【Range常用属性】

单参数及双参数引用单元格区域

给指定单元格填值:Range(“A1:A5”).value = 10

Dim x as string

x = “A1:A5”

Range(x) = 10

选中多个不连续单元格区域并集(单参逗号分隔):Range(“A1:A5,B3:B8,A3:C7”).select

选中多个单元格区域间交集(单参空格分隔):Range(“B1:B5 A2:C4”)=Range(“B2:B4”)

选中两个区域对角线的矩形区域(双参逗号):Range(“A1:A5”,”C3:C7”)=Range(“A1:C7”)

Range()的单参数用双引号,其中参数内可是两个单元格对角线矩形选定的区域,若参数中有变量则使用文本拼接符号&来拼接成双引号内的一个区域;双参数时之间要用逗号,然后表示两参数之间对角线矩形选定区域,两参数可用文本形式也可用Cells()或Range(),在有变量时也可用文本拼接符号&。

Range(“A1:A5”) = Range(“A1”,”A5”)=Range(Range(“A1”),Range(“A5”))=Range(Cells(1,1),Cells(1,5))

Range的参数也支持表达式,即字符或数值运算、变量等,其中列标加双引号,不同类型参数间用&连接,如Range(“B”&1+2)表示B3,Range(“C”&i)表示C列i行的单元格。

Worksheet或Range对象的Cells属性

Cells(m,n)双参时返回指定工作表或单元格区域中指定行与列相交的一个单元格,第1个参数是行号,第2个参数是列号(可为数字或列字母),如:

ActiveSheet.Cells(2,2)=ActiveSheet.Cells(2,”B”)= Cells(2,2)

省略ActiveSheet则表示对象引用的是当前活动工作薄的活动工作表。

Range(“B2:D4”).Cells(2,2)=Range(“C3”)

Cells(n)单参时表示引用Worksheet或Range区域内的第n个单元格,单元格按从左往右,从上到下的顺序编号。

需要注意Cells与Range的区别在于,Cells只能引用一个单元格,而Range则可以引用一个或多个单元格。

区域或单元格偏移引用(Offset

offset(m,n):m为行偏移,n为列偏移,正偏移为右下,负偏移为左上。

选中单元格右1下2单元格:Range(“B1”).offset(2,1).select=Range(“C3”).select

选中区域左1水平不动区域:Range(“C3:E5”).offset(0,-1).select=Range(“B3:D5”).select

Range对象的End属性

End属性返回指定单元格向指定区域结尾处的单元格,它有四个可选参数:

xlUp:指定单元格所在列向上最后一个非空单元格;

xlDown:指定单元格所在列向下最后一个非空单元格;

xlToLeft:指定单元格所在行向左最后一个非空单元格;

xlToRight:指定单元格所在行向右最后一个非空单元格;

一般指定单元格选择列最后一个单元格如Range(“A65536”)或行最后一个单元格如Range(Cells(1,256)),65536是2003版Excel的最后一行,而256是其最后一列。

在新版本中行列不再是65536和256了,但只要我们选定一个数据处理时不会用到的“最远”单元格定界即可,若执着最Excel最边界单元格,可用如下:

B列最后一个单元格:Cells(Rows.count,2)

第五行最后一个单元格:Cells(5,Columns.count)

选中A列最后一个非空单元格:Range(“A65536”).End(xlUp).select

选中A列最后一个非空单元格下一个单元格:Range(“A65536”).End(xlUp).offset(1,0).select

Range整行(Rows)或整列(Column)引用

选中活动工作表的第3行: Range(“3:3”).select=Rows(“3:3”).select=Rows(3).select

选中活动工作表的第3到5行:Range(“3:5”).select=Rows(“3:5”).select

选中单元格区域的第2行:Rows(“2:8”).Rows(2).select=Rows(3).select

选中单元格区域的第2行:Range(“B2:D4”).Rows(2).select=Range(“C2:C4”).select

选中活动工作表的第3列:Range(“C:C”).select=Columns(“C:C”).select=Columns(3).select

选中活动工作表的第3到5列:Range(“C:E”).select=Columns(“C:E”).select

选中单元格区域的第2列:

Range(“B2:E5”).Columns(“B:B”).select= Range(“B2:E5”).Columns(2).select=Range(“C2:C5”)

选中多个不连续单元格(Union

使用单参数逗号分隔的Range如:Range(“A1:A5,B3:B8”).select

或Union(Range(“A1:A5”), Range(“B3:B8”)).select

单元格的其他属性(UsedRange、CurrentRegion、Value、Count、Address、Copy、Paste、Delete

返回工作表已经使用的单元格围成的矩形区域:ActiveSheet.UsedRange.select

返回工作表指定单元格所在区域(以空行和空列的组合为边界的矩形区域):

Range(“D4”).CurrentRegion.select

设置指定单元格的值:

Range(“A1”).value=1

Range(“B1”).value=”test”

value是Range的默认属性,也可以省略:

Range(“B1”)=”test”

返回指定区域单元格个数:Range(“A1:C3”).count=9

返回工作表已使用行数或列数:ActiveSheet.UsedRange.Rows.count

返回已选定的单元格地址:Selection.Address

复制已选定的单元格:Range(“A1”).Copy

粘贴已选定的单元格:Range(“B1”).Paste

复制并粘贴单元格:Range(“A1”).Copy Destination := Range(“B2”)

其中,Destination是可省关键字,即:Range(“A1”).Copy Range(“B2”)

删除单元格右侧单元格左移:Range(“B2”).Delete Shift:=xlToLeft

删除单元格下侧单元格上移:Range(“B2”).Delete Shift:=xlUp 或Range(“B2”).Delete

删除整行:Range(“B2”).EntireRow.Delete

删除整行:Range(“B2”).EntireColumn.Delete

【Worksheet常用属性】

引用工作薄中第2个工作表:Worksheets.item(2)或Worksheets(2)

引用工作薄指定工作表:Worksheets(“sheet2”)

新建工作表插入在当前活动工作表前:Worksheets.Add

新建工作表插入指定顺序位置:Worksheets.Add before/after := worksheets(2)

重命名指定工作表:Worksheets(“sheet2”).name = “部门表”

重命名活动工作表:ActiveSheet.name = “部门表”

复制工作表到指定顺序位置:Worksheets(“部门表”).copy before/after := Worksheets(“sheet2”)

复制工作表到新建工作薄:Worksheets(“部门表”).copy

删除工作表:Worksheets(“sheet2”).Delete

指定工作表为活动工作表:Worksheets(“sheet2”).Activate或Worksheets(sheet2).select

获取当前工作薄工作表个数:Worksheets.count

【Workbook常用属性】

引用Workbook可使用其名称+扩展名如.xls,获取文件名、路径及全路径方法如下:

文件名称:Range(“A1”) = ThisWorkbook.Name

文件路径:Range(“A2”) = ThisWorkbook.Path

文件路径+名称:Range(“A3”) = ThisWorkbook.FullName

创建新工作薄:Workbook.add “D:\work\emp.xls”

打开工作薄:Workbook.open “D:\work\emp.xls”

指定工作薄为活动工作薄:Workbooks(“Book1”).activate

保存工作薄:ThisWorkbook.Save

另存工作薄:ThisWorkbook.SaveAs FileName := “D:\work\dept.xls”

关闭所有工作薄:Workbooks.close

关闭指定工作薄:Workbooks(“Book1”).close

代码所在工作薄文件名:ThisWorkbook.Name

当前活动工作薄文件名:ActiveWorkbook.Name

【Application常用属性】

ScreenUpdating:设定VBA操作的数据是否在显示在屏幕上,值为False时将看不到程序执行的过程,可以加快程序的执行速度,使用如下:

Application.ScreenUpdating = False

…(程序执行语句)

Application.ScreenUpdating = True(最后将数据的操作结果显示出来)

DisplayAlerts设定不显示告警信息,如在删除指定工作表时,若该表有数据则程序中断提示告警信息,通过该属性可去除告警提示信息,如:

Application.DisplayAlerts = False

sheet1.Delete

Application.DisplayAlerts = True

WorksheetFunction:在VBA中很多常用函数不能使用,如countif(查找符合条件单元格个数)、vlookup(列检索匹配值对应值)、transpose(转置函数)和iserror(判断单元格内数据是否是错误值),想要使用这些函数就需要借助WorksheetFunction属性,如:

let count_60 =Application.WorksheetFunction.CountIf(Range(“A1:A50”),”>60”)

对象引用要使用全引用确定对象归属,如:Application.Workbook(“文件名”).Worksheet(“表名”).Range(“单元格”),甚至我们还可以在文件名前加上其存储路径,这样就可以在不打开Excel表的情况下使用该表数据。

不过,若我们仅操作当前打开Workbook(工作薄,即一个Excel文件)中的当前sheet(工作表)中的选中单元格,则可省略全引用路径,如:

Application.Selection.Value = 2016

即表示将当前选中的单元格的值修改为2016,其中,Application也可以省略,直接写成Selection.Value = 2016。

Application常用引用属性如:ActiveCell(活动单元格,已选中)、ActiveSheet(活动工作薄中活动工作表,可省略)、ActiveWorkbook(活动工作薄,可省略)、ActiveWindow(当前活动窗口,即已打开的Excel文件)、Selection(当前工作薄选中的对象)、Worksheets(当前工作薄中所有sheet)、Workbooks(当前打开的所有工作薄)。

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

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

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

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

【数组使用】

数组是一组同类变量的集合,可以通过数字索引到具体的某个变量。主要用于批量地管理数据,提高代码执行速度,将区域对象转换成内存数组,从而减少读取时间。它是一个列表或一组数据表,由连续可索引的具有相同内在数据类型的元素所组成的集合,数组中每一元素都具有唯一的索引号。数组存于内存中,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 数组名(Start to End) as 数据类型

Start为索引开始值,End为索引结束值。

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

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对一维数组变量赋值、直接将区域赋予数组、利用循环逐个赋值。

利用Array赋值

Dim Arr1(3) as string

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

逐个单独赋值

Arr1(0) = “甲”

Arr1(1) = “乙”

循环赋值

Dim arr(3) as String, Item as integer

For Item = 0 to 3

Arr(Item) = Cells(Item+1,1)

Next

区域赋值

Dim rng

Rng = Activesheets.Usedrange.Value

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

Transpose转置数组

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

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

Dim arr as Variant

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

【代码美化】

分行:若一行代码过长,可以断句拆成两行写。其中,第一行句子结束处添加“空格+下划线”,即“ _”,然后上下两行代码就连接到一起了;

拼行:若需要对多行进行拼接,则可使用冒号后跟下行内容;

注释:英文单引号后跟注释内容,也可使用关键字Rem 后跟空格再跟注释内容,若想在代码后加注释,则可在代码后使用“: rem 注释内容”;

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

给我留言

留言无头像?