vba数组学习.docx

1、vba数组学习VBA数组学习一、数组概念二、数组就是一个列表或者一组数据表。它是由连续可索引的具有相同内在数据类型的元素所组成的集合，数组中每一个元素都具有唯一的索引号。更改其中一个元素并不会影响到其它元素。数组存在内存，可以利用索引号获取该集合中每一个子集。数组的两个特点：1、读写速度快VBA读取对象中的值永远慢于读取内存中的值。可以借助VBA数组对程序提速。2、无法永远保存数据存于工作表区域内，可以永久保存。但存入内存中的变量数组和常量数组却受其作用域影响生命周期。过程级别的私有数组变量或者常量数组在过程结束后会自动释放，结束其生命周期；而公有的变量数组和常量数组在excel应用程序关闭后

2、会自动释放。也就是重新启动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

3、+Enter，就可以在A1：A6之间得到了纵向区域的值。而VBA数组也可以得到同样的效果：2.1 VBA 代码Sub 横向数组()A1:F1 = 1,2,3,4,5,6End Sub1,2,3,4,5,6代表是一维横向数组。2.2 VBA代码Sub 纵向数组()A1:A6 = 1;2;3;4;5;6End Sub1;2;3;4;5;6代表是一维纵向数组。从上面两个赋值过程来看，看到数组在VBA中的优势，不仅可以对单元格进行循环赋值，还可以把原本需要循环6次的操作集中一次完成。2、二维数组对于三行三列的数组，我们可以使用数组公式：=1,1,1;2,2,2;3,3,3在VBA中也可以用数组表示一个

4、二维数组，如下面例子Sub 二维数组()A1:C3 = 1,1,1;2,2,2;3,3,3End Sub三、利用索引号获取数组中的元素类似Range可以使用索引号访问区域中每一个单元格一样，一维数组和二维数组也可以使用索引号获取数组中每一个值。主要有两种形式：形式一：Arr(Item)形式二：Arr（RowIndex，ColumnIndex）这两种形式看起来在形式上和Range的索引号完全一致，但在事实上存在很多差异。请看差异在哪里，例子1：Sub 索引号引用数组()Dim arr1() 声明数组变量arr1 = Array(wise, rose, 小花鸟, 小花鸭) 对数组赋值MsgBox

5、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中两种方式只能使用第一种方式，而后者会产生错误。当使用索引号引用数组时候，值得注意是：第一个元素的默认索引值。在默认状态下，如果模块中未指定第一个元素的索引号，

6、那么默认为0。即数组中arr中的第一个值用arr(0)来表示，最后一个元素的索引号则为数组元素个数减1来表示。如下例子：Sub 索引号引用数组()Dim arr1() 声明数组变量arr1 = Array(wise, rose, 小花鸟, 小花鸭) 对数组赋值MsgBox arr1(1) 正确的引用End Sub该例子显示的结果是rose而不是wise。如果不习惯这种默认的索引方式，可以利用option base语句。option base 1：表示数组中第一个元素的索引号为1option base 语句只能置于模块的顶部，并且可选值只能为0或1。因为默认状态为0，那么option base

7、0可以忽略。四、声明数组与赋值1、声明数组变量声明数组和声明其他变量一样，可以使用dim 、static、private 或public等语句声明。一般来说，有两种声明方式，一种是静态数组声明，该种声明方式在声明数组变量时已经指明了数组的大小。一种是动态数组声明，它在程序运行后，数组大小可以被重置、改变。数组声明：当数组变量的参数是一个数值时，表示它是一维横向数组，元素个数等于该值加1。如：(1) dim arr(5):表示声明一个具有6个元素一维横向数组，其数据类型是变体变量variant；（2）dim arr(4) as byte:表示声明一个具有5个元素的横向数组，其数据类型为byte如

8、果借助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对一维数组变量赋值、直接将区域赋予数组。循

9、环赋值：Sub 数组赋值()Dim arr(3) As String, Item As Integer 循环数组四个元素For Item = 0 To 3 逐个赋值，将A1：A4值赋予每个变量arr(Item) = Range(A & Item + 1)NextMsgBox arr(1)End SubArray 数组赋值：Sub 数组赋值1()Dim arr As Variant 必须使用变体变量一次性对数组赋值，横向一维数组arr = Array(wise, Rose, 鸟, 鸭)MsgBox arr(1)End Sub也可以对一维数组进行纵向赋值：Sub 数组赋值2()Dim arr As

10、 Variant 必须使用变体变量一次性对数组赋值，纵向一维数组arr = WorksheetFunction.Transpose(Array(wise, Rose, 鸟, 鸭)D1:D4 = arrEnd Sub区域赋值：Sub 区域赋值()Dim arrarr = A1:A6MsgBox arr(4, 1)End Sub五、静态数组和动态数组静态数组在执行期间不可以改变其上界（最后一个元素的索引号），而动态数组可以随时修改其上界。如：dim arr(10) as long dim arr(1 to 100) 等，这些都是静态数组。而对于动态数组，需要dim语句配合Redim 语句或者Red

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

12、Dim 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 Variantarr = Array(wise, Rose, susu, ting)MsgBox arr(1)End SubArray方式创建数组，默认状态下下界为0，随着o

13、ption base语句的设置而变化。Sub bb()MsgBox Array(wise, Rose, susu, ting)(1)End Sub如该程序，也是和aa模块的效果一样。另外，Array可以一次完成赋值，如：Sub cc()D1:G1 = Array(wise, Rose, susu, ting)End SubArray的参数个数可以就是数组的上界，数组上界的大小受计算机的可用内存限制，内存越大，它支持的上界就越大。Array的参数各元素的值可以不互相干扰，它可以是任意数组数据。如下例子：Sub dd()arr = Array(wise, Date, 123, Format(tod

14、ay, yyyy), 13)For i = 0 To UBound(arr) Cells(i + 1, 1) = arr(i)Next iEnd Sub注意：Array只能对Variant 型变量赋值，且声明该变量时不能包含括号七、内置数组函数之ISArray函数Isarray函数可以返回Boolean值，指出其参数是否为一个数组。具体请看以下例子：例子1：Sub a1()If IsArray(Array(wise, Rose, 12) Then MsgBox 这是一个数组Else MsgBox 这不是一个数组End IfEnd Sub例子2：Sub a2()If IsArray(A1:A10

15、.Value) Then MsgBox 这是一个数组Else MsgBox 这不是一个数组End IfEnd Sub例子3：Sub A3()If IsArray(Range(A1).Value) Then MsgBox 这是一个数组Else MsgBox 这不是一个数组End IfEnd Sub 对比以上三个例子，就可以理解Isarray函数的作用。一、数组的分类按元素数目分：元素数目大小固定的数组和元素数目大小不固定的动态数组。按维数分：一维数组、多维数组。Arr(1 to 12)、Arr1(0 to 24)-一维固定数组；Arr2(1 to 5,1 to 8)- 二维固定数组；Arr3(5

16、 to 10,6 to 12,1 to 100) - 三维固定数组。动态数组Dim Arr2(),r%r=r+1ReDim Preserve Arr2(1 To r) 动态数组；可以重新声明（只有最后一维的数目才能重新声明）；用了关键字 Preserve 可确保原来包含数据的数组中的任何数据都不会丢失二、数组的赋值2.1，单元格区域保存到数组arr = e22:i24arr=Range(“e22:i24”)2.2，Array函数myArray = Array(AAA, BBB, 200, 500, 2006-7-12)如果代码头没有 Option Base 1 的语句，则数组myArray的上

17、限为4，下限为0。即下限LBound(myArr)=0 ，上限 UBound(myArr)=4二维数组的第一维的上限：UBound(Arr,1)二维数组的第二维的上限：UBound(Arr,2)多维数组上限的求法一样。2.3，把单元格区域公式赋给数组如果a5=B4+1arr = a4:c8.Formula 将单元格绝对引用公式保存到数组e4:g8=arr 此时e5中的公式也B4+1;如果将单元格相对引用公式保存到数组arr = a4:c8.FormulaR1C1 如果a5=B4+1e4:g8=arr 此时e5中的公式就E4+1;三、数组的处理3.1，数组里的最大值和最小值最大值aa = App

18、lication.WorksheetFunction.Max(Arr)aa= Application.WorksheetFunction.Large(Arr,1)最小值aa = Application.WorksheetFunction.Min(Arr)aa= Application.WorksheetFunction.Small(Arr,1) 3.2，数组里搜索Temp = Filter(Arr, xm(i) 搜索数组Sub yy()Dim Arr(), aa$, x%aa = asssfffssssaaasss: bb = sFor x = 1 To Len(aa) ReDim Prese

19、rve Arr(1 To x) Arr(x) = Mid(aa, x, 1)Next xtemp = Filter(Arr, bb)cc = UBound(temp) + 1 cc=”s”的个数End Sub用于对字符串数组进行搜索，得到一个新的数组temp，缺点：只告诉你某元素是否存在于数组中，而不知道其具体位置；数组精确搜索：Sub FilterExactMatch() 该函数在一个字符串数组中搜索那些 与搜索字符串完全匹配的元素。 Dim astrFilter() As String Dim astrTemp() As String Dim lngUpper As Long Dim ln

20、gLower As Long Dim lngIndex As Long Dim lngCount As Long astrItems = Array(a, sas, s, Sas, s, f, f, f, f, sas, s, sas, a, a, Sas, s, s) strSearch = Sas 为搜索字符串而过滤数组。 astrFilter = Filter(astrItems, strSearch) 存储结果数组的上限和下限。 lngUpper = UBound(astrFilter) lngLower = LBound(astrFilter) 将临时数组调整到相同大小。 ReDim

21、 astrTemp(lngLower To lngUpper) 在经过滤的数组的每个元素中循环。 For lngIndex = lngLower To lngUpper 检查该元素是否与搜索字符串完全匹配。 If astrFilter(lngIndex) = strSearch Then 在另一个数组中存储完全匹配的元素。 astrTemp(lngCount) = strSearch lngCount = lngCount + 1 End If Next lngIndex 重新调整包含完全匹配的元素的数组的大小。 ReDim Preserve astrTemp(lngLower To lngC

22、ount - 1) 返回包含完全匹配的元素的数组。 a5.Resize(1, UBound(astrTemp) + 1) = Application.Transpose(astrTemp)End Sub3.3，转置取工作表区域的转置到数组：arr=Application.Transpose(a1:c5) 此时arr是转置成3行5列的数组，arr(1 to 3,1 to 5)e1:i3=arr 此时3行5列。数组间也可以转置：arr1=Application.Transpose(arr)取数组arr的第n列赋值到某列区域：e1:e5=Application.Index(arr, 0, n) 也可

23、写成 e1:e5=Application.Index(arr, , n)赋值产生一个新数组：arr1=Application.Index(arr,0 , n)取数组arr的第n行赋值到某行区域：a6:c6=Application.Index(arr,n ,0 ) 也可写成 a6:c6=Application.Index(arr,n ) 省略0，也省略了“，“赋值产生一个新数组：arr1=Application.Index(arr, n )3.4，数组的比较（字典法）题目：将A列中的数据与C列相比较,输出C列中没有的数据到D列： Sub cc()by:ccwan Dim arr, brr, i&

24、, x&, d As Object arr = Range(a1:a & a65536.End(xlUp).Row) brr = Range(c1:c & c65536.End(xlUp).Row) Set d = CreateObject(scripting.dictionary) For i = 1 To UBound(arr) d(arr(i, 1) = Next For x = 1 To UBound(brr) If d.exists(brr(x, 1) Then d.Remove brr(x, 1) End If Next d1.Resize(d.Count, 1) = Applic

25、ation.Transpose(d.keys)End Sub 3.5，数组的排序字符串数组不能用Large(Arr,i) 或者 Small(Arr,i) 来排序；但数值数组可以；一个很好的字典+数组排序的实例：Sub yy1()by:oobird Dim i%, c As Range, x, d As ObjectSet d = CreateObject(Scripting.Dictionary)For Each c In Sheet2.UsedRange If c.Value Then If Not d.exists(c.Value) Then d.Add c.Value, 1 Else d

26、(c.Value) = d(c.Value) + 1 End If End IfNext k = d.keys: t = d.items k是各个不重复值，t是各个不重复值的个数 ReDim x(1 To 2, 1 To d.Count) For i = 1 To d.Count x(2, i) = Application.Large(k, i) 从大到小排序 x(1, i) = d(x(2, i) Next i With Sheet1 .b2.Resize(2, i - 1) = x ReDim x(1 To 2, 1 To d.Count) For i = 1 To d.Count x(1

27、, i) = Application.Max(t) 从大到小排序 w = Application.Match(x(1, i), t, 0) 1 查找此值在不重复值系列中的排位，因为w是从0开始的，所以1 x(2, i) = k(w) 求得对应的不重复值 t(w) = 使前面的最大值为空，继续循环 Next i .b5.Resize(2, i - 1) = x 两行一起赋值给B5开始的单元格End WithEnd Sub字符串数组的排序，可以使用辅助列，把数组各元素依次赋给单元格，然后对这些单元格运用Excel自有的数据排序功能进行排序，再把单元格排过序的值重新赋给数组。3.6，数组赋给单元格区

28、域r=Ubound(Arr) r为一维数组的上限;Range(a2).Resize(1, r) = Arr 填充到工作表的一行之中（Arr为一维数组）或者写成 Range(a2).Resize(1, Ubound(Arr) = Arr二维数组Arr(100,5)Range(“a1”).Resize(100,5)=Arra1:e100=Arr或者写成 Range(a1).Resize(Ubound(Arr,1), Ubound(Arr,2) = Arr赋值方面的补充：Sub y()Dim arrarr = mmult(row(1:100),column(a:f)a1.Resize(100, 6) = arrEnd SubSub yy()Dim arrarr = column(a:z)3MsgBox Join(arr, ,)arr = transpose(row(1:222)MsgBox Join(arr, ,)End SubSub yyy()Dim arrarr = Split(a b c d e f g)MsgBox Join(arr, ,)End Sub-红绳子 = Array(辣1, 辣2, 辣3, 辣4, 辣5, 辣6, 辣7, 辣8, 辣9, 辣10) cName = Array(林思明, 曾玉婷, 曾国文, 林伟权, 林兴发,