VBA 数组 巧用

 数组是一个相当好的变量集合，里面可以存放许多按实际要求但是不可意料其值的值！

　　要使用数组，首先要定义数组，方能使用，如何定义，在上一篇已做了说明，在此不再阐述！

　　数组根据不同的需求，可分为静态数组和动态数组，静态数组存储欲先设置话的值，相当于里面存储一个或多个静态变量的值；动态数组根据需要，可随时改变数组长度，并随时能修改存储的值；

　　本文提供几个简单的例子，以方便各位的学习！

　　代码一：将预定单元格的值存储到数组中

　　----------------------------------------------------

　　Sub ArrayGetValue() '
　　　 MyArray = Range("E3:E8")
　　　 For i = 1 To 6
  　　　　   MsgBox MyArray(i, 1)
  　　　Next
　　End Sub

　　----------------------------------------------------

　　代码解释：

　　MyArray = Range("E3:E8")　'将E3到E8单元格的值存储到数组MyArray中；

　　For i = 1 To 6
  　　　　   MsgBox MyArray(i, 1)
　　Next
　　'通过循环语句，读取MyArray的值并通过对话框形势显示出来；

　　代码二：将一列数据存储到数组中

　　----------------------------------------------------

　　Sub ArrayGetValue() '
　　　Dim MyArray() As String
　　　Dim iNum As Integer
　　　Dim i As Integer
　　　iNum = Sheets("sheet1").[E65536].End(xlUp).Row
　　　ReDim Preserve MyArray(iNum) As String
　　　For i = 1 To iNum
　　　　MyArray(i - 1) = Sheets("sheet1").Range("E" & i).Value
　　　MsgBox MyArray(5) '用于测试E6单元格的值，因为数组下标从0开始！
　　End Sub

　　----------------------------------------------------

　　代码解释：

　　iNum = Sheets("sheet1").[E65536].End(xlUp).Row　'获取E列单元格的个数；

　　ReDim Preserve MyArray(iNum) As String　　'重新定义数组的长度；以iNum为参考对象；

 
数组基础知识

一、数组概念

二、数组就是一个列表或者一组数据表。它是由连续可索引的具有相同内在数据类型的元素所组成的集合，数组中每一个元素都具有唯一的索引号。更改其中一个元素并不会影响到其它元素。

数组存在内存，可以利用索引号获取该集合中每一个子集。

数组的两个特点：

1、读写速度快

VBA读取对象中的值永远慢于读取内存中的值。可以借助VBA数组对程序提速。

2、无法永远保存

数据存于工作表区域内，可以永久保存。但存入内存中的变量数组和常量数组却受其作用域影响生命周期。

过程级别的私有数组变量或者常量数组在过程结束后会自动释放，结束其生命周期；

而公有的变量数组和常量数组在excel应用程序关闭后会自动释放。也就是重新启动excel后，以前任何数组都不存在。

3、数组分类

按照数组元素是否固定来分，可以分为静态数组和动态数组；

按照数组维度来分，可以分为一维数组、二维数组等，最多只能为60维。

二、数组的维度

数组可以是一维、二维直到六十维。而对于excel工作表来说，

excel的每一行或者每一列就可以转换成一维数组，而多行多列就可以转换成二维数组。

1、一维数组

在数组公式中，在A1：F1区域中输入数组公式：={1,2,3,4,5,6}

，然后按Ctrl+Shift+Enter，就可以在A1：F1之间得到了横向区域的值。

在A1：A6区域中输入数组公式：={1；2；3；4；5；6}，然后按

Ctrl+Shift+Enter，就可以在A1：A6之间得到了纵向区域的值。

而VBA数组也可以得到同样的效果：

2.1 VBA 代码

Sub 横向数组()

[A1:F1] = [{1,2,3,4,5,6}]

End Sub

[{1,2,3,4,5,6}]代表是一维横向数组。

2.2 VBA代码

Sub 纵向数组()

[A1:A6] = [{1;2;3;4;5;6}]

End Sub

[{1;2;3;4;5;6}]代表是一维纵向数组。

从上面两个赋值过程来看，看到数组在VBA中的优势，不仅可以对单元格进行循环赋值，还可以把原本需要循环6次的操作集中一次完成。

2、二维数组

对于三行三列的数组，我们可以使用数组公式：={1,1,1;2,2,2;3,3,3}

在VBA中也可以用数组表示一个二维数组，如下面例子

Sub 二维数组()

[A1:C3] = [{1,1,1;2,2,2;3,3,3}]

End Sub

三、利用索引号获取数组中的元素

类似Range可以使用索引号访问区域中每一个单元格一样，一维数组和二维数组也可以使用索引号获取数组中每一个值。主要有两种形式：

形式一：Arr(Item)

形式二：Arr（RowIndex，ColumnIndex）

这两种形式看起来在形式上和Range的索引号完全一致，但在事实上存在很多差异。

请看差异在哪里，

例子1：

Sub 索引号引用数组()

Dim arr1() '声明数组变量arr1 = Array("wise", "rose", "小花鸟", "小花鸭") '对数组赋值

MsgBox arr1(1) '正确的引用

MsgBox arr1(1, 1) '错误的引用

End Sub

在例子1中，对于一维数组的两种索引方式只能前者可以正常执行，后者会产生错误。

例子2：

Sub 索引号引用数组1()

Dim arr1() '声明数组变量

arr1 = [{1,1,1,1;2,2,2,2;3,3,3,3;4,4,4,4}] '对数组赋值

MsgBox arr1(4, 2) '正确的引用

MsgBox arr1(4) '错误的引用

End Sub

对于二维数组，例子2中两种方式只能使用第一种方式，而后者会产生错误。当使用索引号引用数组时候，值得注意是：第一个元素的默认索引值。

在默认状态下，如果模块中未指定第一个元素的索引号，那么默认为0。即数组中arr中的第一个值用arr(0)来表示，最后一个元素的索引号则为数组元素个数减1来表示。

如下例子：

Sub 索引号引用数组()

Dim arr1() '声明数组变量

arr1 = Array("wise", "rose", "小花鸟", "小花鸭") '对数组赋值

MsgBox arr1(1) '正确的引用

End Sub

该例子显示的结果是rose而不是wise。如果不习惯这种默认的索引方式，可以利用option base语句。option base 1：表示数组中第一个元素的索引号为1

option base 语句只能置于模块的顶部，并且可选值只能为0或1

。因为默认状态为0，那么option base 0可以忽略。

四、声明数组与赋值

1、声明数组变量声明数组和声明其他变量一样，可以使用dim 、static、private 或public等语句声明。一般来说，有两种声明方式，一种是静态数组声明，该种声明方式在声明数组变量时已经指明了数组的大小。一种是动态数组声明，它在程序运行后，数组大小可以被重置、改变。数组声明：当数组变量的参数是一个数值时，表示它是一维横向数组，元素个数等于该值加1。

如：(1) dim arr(5):表示声明一个具有6个元素一维横向数组，其数据类型是变体变量variant；

（2）dim arr(4) as byte:表示声明一个具有5个元素的横向数组，其数据类型为byte

如果借助to关键字，可以指定数组第一个元素的索引值。

如（1）dim arr(1 to 3) as string:表示声明一个具有3个元素的一维横向数组，数据类型是string,其第一个元素索引号为1；

（2）如果需要声明二维数组，可以使用逗号将参数分开，其形式为arr(一维，二维），如：

dim arr(3,2) as string:表示声明一个四行三列的二维数组，默认第一元素索引值为0；

dim arr(1 to 3,1 to 2) as string:表示声明一个三行二列的

二维数组

2、对数组变量赋值

数组赋值通常采用三种方式：利用循环逐个赋值、利用Array对

一维数组变量赋值、直接将区域赋予数组。

循环赋值：

Sub 数组赋值()

Dim arr(3) As String, Item As Integer '循环数组四个元素

For Item = 0 To 3 '逐个赋值，将A1：A4值赋予每个变量

arr(Item) = Range("A" & Item + 1)

Next

MsgBox arr(1)

End Sub

Array 数组赋值：

Sub 数组赋值1()

Dim arr As Variant '必须使用变体变量'一次性对数组赋值，横向一维数组

arr = Array("wise", "Rose", "鸟", "鸭")

MsgBox arr(1)

End Sub

也可以对一维数组进行纵向赋值：

Sub 数组赋值2()

Dim arr As Variant '必须使用变体变量'一次性对数组赋值，纵向一维数组

arr = WorksheetFunction.Transpose(Array("wise", "Rose", "鸟", "鸭"))

[D1:D4] = arr

End Sub

区域赋值：

Sub 区域赋值()

Dim arr

arr = [A1:A6]

MsgBox arr(4, 1)

End Sub

五、静态数组和动态数组

静态数组在执行期间不可以改变其上界（最后一个元素的索引号

），而动态数组可以随时修改其上界。

如：dim arr(10) as long      dim arr(1 to 100) 等，这些都是静态数组。

而对于动态数组，需要dim语句配合Redim 语句或者Redim Preserve 语句来实现。

Redim 语句或者Redim Preserve语句的作用是为了动态数组变量重新分配内存空间，包括指定的维数及声明其上界。但Redim语句重置数组大小，会使数组中的值丢失；而Redim Preserve语句重置数组的大小时可以保留原数组中的值。可以使用Redim语句反复地改变数组的元素及维数的数目，但是不能将一个数组定义为某种数据类型后，再使用Redim将该数组改成为其他数据类型，除非是variant所包含的数组。具体看以下数组：

Sub a()

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)

End Sub

六、内置数组函数之Array函数

Array函数用于创建一个包含数组的Variant。它只能创建一维横向数组。

如下例：

Sub aa()

Dim arr As Variant

arr = Array("wise", "Rose", "susu", "ting")

MsgBox arr(1)

End Sub

Array方式创建数组，默认状态下下界为0，随着option base语句的设置而变化。

Sub bb()

MsgBox Array("wise", "Rose", "susu", "ting")(1)

End Sub

如该程序，也是和aa模块的效果一样。

另外，Array可以一次完成赋值，如：

Sub cc()

[D1:G1] = Array("wise", "Rose", "susu", "ting")

End Sub

Array的参数个数可以就是数组的上界，数组上界的大小受计算机的可用内存限制，内存越大，它支持的上界就越大。Array的参数各元素的值可以不互相干扰，它可以是任意数组数据。

如下例子：

Sub dd()

arr = Array("wise", Date, 123, Format(today, "yyyy"), 13)

For i = 0 To UBound(arr)

    Cells(i + 1, 1) = arr(i)

Next i

End Sub

注意：Array只能对Variant 型变量赋值，且声明该变量时不能包含括号

七、内置数组函数之ISArray函数

Isarray函数可以返回Boolean值，指出其参数是否为一个数组。

具体请看以下例子：

例子1：

Sub a1()

If IsArray(Array("wise", "Rose", 12)) Then

    MsgBox "这是一个数组"

Else

    MsgBox "这不是一个数组"

End If

End Sub

例子2：

Sub a2()

If IsArray([A1:A10].Value) Then

    MsgBox "这是一个数组"

Else

    MsgBox "这不是一个数组"

End If

End Sub

例子3：

Sub A3()

If IsArray(Range("A1").Value) Then

    MsgBox "这是一个数组"

Else

    MsgBox "这不是一个数组"

End If

End Sub         对比以上三个例子，就可以理解Isarray函数的作用。