VBA学习笔记2:数组的使用

对于办公人员来说,使用数据最大的好处是,需要写入或计算的数据会在内存里组织好后一次性贴入单元格内,而不是逐个贴入。这样可以大大提升VBA代码的运行速度,特别是针对数据量大的表格,避免了屏幕反复刷新的情况。本文梳理几个常与数组配合使用的知识点。

1.数组的参数

数组的参数指明对数组的引用,类似对单元格区域的引用,如果是二维数组,同样遵循“先行后列”原则。如:

range("A1:B6")(3,2)    '引用区域"A1:B6"中第3行第2列的值

arr(3,2)    '引用二维数组第3行第2列的值

但是,Range("A1:B6")(3) 是引用区域"A1:B6"中的第3个单元格的值(先行后列),而对二维数组arr使用“arr(3)”语句的话,就会“下标越界”,因为二维数组必须使用两个参数。

2.数组的产生

数组产生的方式有多种,每种方式产生的结果有细微差异。

①最简单的方式,和工作表中的数组公式类似,即使用{}括住数组的内容,横向数组采用“,”分隔,纵向数组采用“;”分隔。VBA中的数组还要在大括号外面加上一对“[]”。

Sub 数组产生()

Dim arr(), arr1()

arr = [{"语文";"数学";"英语";"体育"}]    '产生纵向二维数组

arr1 = [{"语文","数学","英语","体育"}]    '产生横向一维数组

Debug.Print arr(2, 1)    ‘结果:数学

Debug.Print arr1(2)    ‘结果:数学

End Sub

上例,arr是纵向数组,因为VBA中只有横向一维数组,没有纵向一维数组,所以arr是二维数组,引用时必须用两个参数。而arr1则是一维数组。

②以上手工录入数组元素的方式,中括号可以用evaluate函数代替。但是evaluate函数的参数是文本类型,所以要在大括号外加上双引号。为了避免产生歧义,大括号内的双引号要变成”双双引号“。如:arr = evaluate("{""语文"";""数学"";""英语"";""体育""}")

也可以使用array函数,不过array只能创建一维数组,其创建的数组如果需要用在列上,需要用transpose函数转换。

③直接引用区域也是创建数组的办法,因为range的默认方法是value,所以直接使用arr=range("A1:B6")这样的语句就能为数组赋值了(相关文章)。引用区域产生的数组均是二维数组。这里需要注意,如果使用set语句,如set rng=range("A1:B6"),rng会是一个range类型,而非数组。也不能使用set语句为数组变量赋值,否则会提示”不能给数组赋值“。

④使用split函数也可以为数组赋值,可以把它理解为工作表的分列功能,针对某一字符串,使用特定的符号进行分列,分列的结果是一维数组。

⑤使用字典产生数组(还没学到)

3.数组元素的变更

①范围的更改。动态数组可以使用redim来重新定义大小,用redim preserve在保留原值的情况下更改大小。但后者只能更改一次维度,且后续更改的大小只能修改最后一维的上限。如果一个二维数组的行数增加,列数不变,用redim重新声明时,要先用transpose转换再更改。例:

Sub redim使用()

Dim arr(), i As Integer

Range("C1:C6") = [{1;-1;3;-4;5;6}]

i = WorksheetFunction.CountIf(Range("C1:C6"), ">0")

ReDim arr(1 To i, 1 To 1)    '重新定义arr的大小

ReDim arr(1 To 7, 1 To 2)    '重新定义arr第一维和第二维的大小

MsgBox UBound(arr)    '结果是7

ReDim Preserve arr(1 To 8, 1 To 2)    ‘重新定义arr第一维的大小为8,出错

End Sub

如上例,用redim可以多次重新定义数组大小,但是用redim preserve的话,不能再修改除最后一维之外其他维度的上限。如果我想把arr变成8行,就要先把第一维转换成最后一维:

arr = WorksheetFunction.Transpose(arr)

ReDim Preserve arr(1 To 2, 1 To 8)    '转换后修改第二维的上限

arr = WorksheetFunction.Transpose(arr)    ‘再转换

MsgBox UBound(arr)

由上例还可以看出,redim另一个作用是可以使用变量,而用dim声明数组时不行。

4.数组写入单元格

如果在给数组赋值的过程中使用了变量,那么在把数组写入单元格时,最好使用range的resize方法重新定义合适大小的区域,以便数据写入完整。 resize中还会使用ubound函数来获取数组的上限。接上例,重新定义大小的arr写入以A8开头的单元格,使用如下语句:

Range("A8").Resize(UBound(arr), UBound(arr, 2)) = arr

5.Option Base 1 应时刻开启, 确保数组的起始下标均为1,避免引用中出现的习惯性疏忽。

评论