在VBA中使用数组(表格区域与数组的相互映射)

使用数组一次可以处理多个变量。数组的维数可以是一维,也可以是二维(最多可以定义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(字节)