使用数组一次可以处理多个变量。数组的维数可以是一维,也可以是二维(最多可以定义60维)。
Excel工作表就是一个天然的二维数组。工作表的行、列对应二维数组的行、列。
工作表数据区域与二维数组对元素的定位就相当于坐标定位,如工作表区域的cells()写法就是就是对应工作表的某一行,某一列。
在excel中,数据可以直接使用[{}]赋值,列元素用逗号,分隔,行元素用分号;分隔。
一维数组可以与行或列相互映射,二维数据可以与一个单元格区域相互映射。所谓相互映射,也就是说数组可以通过单元格区域定义,而定义格区域也可以通过数组赋值。
单元格区域可以通过地址引用,同样的,数组可以很方便地通过索引(下标)引用(字典通过key引用)。
通过数组,多了一种数据处理的方式,并且速度更快(将单元格区域映射为数组,相当于把单元格区域的数据搬入到了内存),更灵活(可以利用数组处理的内置函数)。
目录
1 一维数组与二维数组
2 数组的开始下标(最小下标)和结束下标(最大下标)
3 单元格区域映射到数组
4 利用索引号(下标)获取数组中的元素
5 数组转置
6 动态数组
6 数组的声明与赋值
7 数组与字符串
8 数组的最值、求和、个数统计
9 数组的查询和拆分
10 与数组相关的其它函数
1 一维数组与二维数组
1.1 一维数组与行
Sub 一维数组与行()
Dim arr()
arr = [{1,2,3,4}] '直接整体赋值时,上面的声明不能指定维数
Range("A3").Resize(1, UBound(arr)) = arr
End Sub
1.2 一维数组与列
Sub 数组()
Dim arr()
arr = [{1,"b",3,4}]
Range("A5").Resize(UBound(arr), 1) = Application.WorksheetFunction.Transpose(arr)
End Sub
1.3 二维数组
Sub 二维数组()
arr = [{11,12,13;21,22,23;31,32,33}]
Range("B5").Resize(UBound(arr, 2), UBound(arr)) = arr
End Sub
2 数组的开始下标(最小下标)和结束下标(最大下标)
UBound(arr)表示数组arr索引号的结束下标(最大下标,Largest Subscript),LBound(arr)表示索引号的开始上标(最小下标,smallest Subscript)。UBound(arr) -LBound(arr) + 1 即表示一维数组的长度(或多维数组第一维的长度)。如果一维数组的索引号从1开始,即LBound(arr)等于1,则UBound(arr)即表示一维数组的长度。
LBound(ArrayName[,dimension])
ArrayName - 必需的参数。该参数对应于数组的名称。
Dimension - 一个可选参数。 这需要一个与数组的维度相对应的整数值。如果是“1”,则返回第一维的下界; 如果是“2”,则返回第二维的下界,依此类推。
二维数组的行数:UBound(arr,1) -LBound(arr,1) + 1
(可以写为:UBound(arr) -LBound(arr) + 1)
二维数组的列数:UBound(arr,2) -LBound(arr,2) + 1
3 单元格区域映射到数组
如将Range("A1:E4")映射到数组arr:
Sub 单元格区域映射到数组()
arr = Range("A1").Resize(4, 5)
Range("A6").Resize(UBound(arr), UBound(arr, 2)) = arr
End Sub
将单元格区域的数据保存到数组以后,数据在内存中处理,速度更快。
4 利用索引号(下标)获取数组中的元素
数组元素的值可以通过下标获取。
Sub 二维数组()
arr = [{11,12,13;21,22,23;31,32,33}]
Range("B5").Resize(UBound(arr, 2), UBound(arr)) = arr
cells(1,1) = arr(2,2) 'arr(2, 2)对应22
End Sub
5 数组转置
正如单元格区域可以转置一样,数组也可以同样操作。
Sub 数组转置()
Dim arr
arr = [{1,2,3,4}]
arr2 = Application.WorksheetFunction.Transpose(arr)
Range("A5").Resize(UBound(arr), 1) = arr2
End Sub
以上操作相当于一维转二维,一维转为一个n行1列的二维数组。
同样的,二维数组也可以进行相同的操作。
6 数组的声明与赋值
声明一维数组:
dim arr(i to j) as variant
声明二维数组:
dim arr(i to j, n to m) as variant
上面的i,n必须是正整数,表示索引号(下标)开始值(最小值)。i可以通过Lbound(arr)获得,n可以通过Lbound(arr,2)获得;
如果i,n是零,可以略写为:dim arr(j) as variant,dim arr( j, m) as variant
j,m必须是正整数,表示索引号(下标)的结束值(最小值)。j可以通过Ubound(arr)区得,m可以通过Ubound(arr,2)获得;
当然,声明时也可以不考虑维数,如dim arr() as variant
Sub 二维数组的声明与赋值()
Dim arr(1 To 3, 1 To 4) '表示行、列的索引号都是从1开始,声明一个3行4列的数组
For i = 1 To 3
For j = 1 To 4
arr(i, j) = i * 10 + j
Next j
Next i
Range("A1").Resize(UBound(arr), UBound(arr, 2)) = arr
End Sub
用缺省索引号开始值的声明:
Sub 一维数组的声明与赋值()
Dim arr(3) '表示索引号从0-3的4个元素,等同于arr(0 to 3)
For i = 0 To 3
arr(i) = i + 10
Next i
Range("A1").Resize(1, UBound(arr) + 1) = arr
End Sub
关于列索引号的开始上标的默认值,如果采用缺少上标的声明,则开始上标的默认值是0.。如果没有声明直接使用数组变量,则上标的默认值是1。
另外,如果在声明时没有声明数据类型,或声明的是variant数据类型,在对数组元素分别赋值时,可以是不同的数据类型。
Sub 不同类型的数组元素()
Dim arr(5)
arr(0) = "1" 'Number as String
arr(1) = "VBScript 'String"
arr(2) = 100 'Number
arr(3) = 2.45 'Decimal Number
arr(4) = #10/7/2013# 'Date
arr(5) = #12:45:00 PM# 'Time
Range("A1").Resize(1, UBound(arr) + 1) = arr
End Sub
也可以利用VBA的内置函数Array()声明常量数组:
arr = Array(Array("a", 10), Array("b", 20), Array("c", 30))
[a1] = arr(1)(1)
6 动态数组
上面使用的都是静态数组,静态数组在执行期间不可以改变其上界(最后一个元素的索引号),而动态数组可以随时修改其上界。
有dim语句定义了一个静态数组后,可以用Redim 语句或者Redim Preserve 对定义的静态数组重新定义大小。
Redim 语句或者Redim Preserve语句的作用是为了动态数组变量重新分配内存空间,包括指定的维数及声明其上界。但Redim语句重置数组变小,会使数组中的值丢失;而Redim Preserve语句重置数组的变小时可以保留原数组中的值。可以使用Redim语句反复地改变数组的元素及维数的数目,但是不能将一个数组定义为某种数据类型后,再使用Redim将该数组改成为其他数据类型,除非是variant所包含的数组。具体看以下数组:
Sub 重置数组()
Dim arr1(), arr2()
arr1 = [A1:D11].Value
arr2 = [A1:D11].Value
ReDim arr1(1 To 2, 1 To 3) '重置数组大小为2行3列的二维数组,数组的中的值丢失
ReDim Preserve arr2(1 To 11, 1 To 3) '重置数组大小为11行3列的二维数组
MsgBox arr1(2, 3) '结果显示为空
MsgBox arr2(2, 3) 'C2单元格的数值
End Sub
7 数组与字符串
数组与字符串可以使用split()函数和join()函数相互转换。
7.1 利用Split()函数将字符串分割为一个数组
Split函数可以将字符串按指定的分隔符转换成下标为0的一维数组(下标为0,并且不受Option Base语句的影响)。它的语法如下:
Split(expression[,delimiter[,count[,compare]]])
expression 必选参数包含字符串和分隔符的字符串表达式。
delimiter 可选参数用于标识字符串边界的字符串字符。如果忽略,则使用空格字符("")作为分隔符
count 一个可选参数。要返回的子字符串的数量,如果指定为-1,则返回所有子字符串。
如:
Sub splitstr()
Sheets("数组与字符串").Select
s = "红,red,橙,orange,黄,yellow,绿,green,青,cyan,蓝,blue,紫,purple"
arr = VBA.Split(s, ",")
Range("A11").Resize(1, UBound(arr)) = arr
End Sub
7.2 利用Join()函数将数组元素合并为一个字符串
Join函数可用于连接数组中的所有字串符,从而创建一个新的字串符,可随意指定分隔符,其语法如下:
Join(sourcearray [,delimiter])
sourcearray 代表数组,参数delimiter代表分隔符
如:
Sub joinstr()
a = Array("Red", "Blue", "Yellow")
b = Join(a)
b = Join(a, "$")
arr = Range("A1:B7")
For i = 1 To UBound(arr)
Range("d" & i) = Join(Array(arr(i, 1), arr(i, 2)), "-")
Next i
End Sub
7.3 利用Filter()函数筛选数组元素
Sub itemFilter()
arr = Array("A056", "A079", "B003", "A007", "B017")
arrf = Filter(arr, "A0")
MsgBox ("The Filter array: " & Join(arrf))
End Sub
单元格A1的值即显示为:A056 A079 A007
8 数组的最值、求和、个数统计
Application.Max(arr) 最大值
Application.Min(arr) 最小值
Application.Large(arr, 2) 求出第二大值
Application.Small(arr, 2) 求出第二小值
Application.Sum(arr) 求和
Application.Count(arr) 统计数组元素(数字)的个数
Application.Counta(arr) 统计数组元素(数字+文本)的个数
COUNT函数和COUNTA函数都是计算非空单元格个数。区别在于:
COUNT函数在计算非空单元格的个数时,将把数字型的数字计算进去,错误值、文字、逻辑值、空值将被忽略;
如果要统计含有错误值、文字、逻辑值,则使用COUNTA函数。
9 数组的查询和拆分
9.1 Mach()查询数组
Match()函数可以查询一个指定值在一组数中的位置,它也可以用于VBA数组的查询。如:
Sub query()
arr = Array(1, 35, 4, 13)
MsgBox Application.Match(4, arr, 0) '查询数值4在数组Arr中的位置
End Sub
9.2 Index拆分数组
数组的拆分在VBA中是一个难题,如果是按行拆分数组,除了用循环外也只能借用API函数完成了。幸好我们可以借用工作表函数index达到按列拆分数组,即多列构成的数组,你可以任意拆分出一列构成新的数组。方法是:Application.Index(数组, 列数) ,例:
Sub Index拆分数组()
arr2 = Range("A1:B4") ‘把单元格区域A1:B4的值装入数组arr2
arr3 = Application.Index(arr2, , 2) '把数组第2列拆分出来装入新数组arr3中,新数组为二维数组
MsgBox arr3(2, 1) '取出新数组第2行的值
End Sub
10 与数组相关的其它函数
IsArray()函数返回一个布尔值,指示指定的输入变量是否是数组变量。
Erase()函数用于重置固定大小数组的值并释放动态数组的内存。
Erase ArrayName
固定数值数组,数组中的每个元素重置为零。
固定字符串数组,数组中的每个元素被重置为零长度""。
对象数组,数组中的每个元素被重置为特殊值Nothing。
-End-
本页共193段,5840个字符,10809 Byte(字节)