|
一、数组概念 二、数组就是一个列表或者一组数据表。它是由连续可索引的具有相同内在数据类型的元素所组成的集合，数组中每一个元素都具有唯一的索引号。更改其中一个元素并不会影响到其它元素。 数组存在内存，可以利用索引号获取该集合中每一个子集。 数组的两个特点： 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}，然后按
而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函数的作用。
一、数组的分类 按元素数目分：元素数目大小固定的数组和元素数目大小不固定的动态数组。 按维数分：一维数组、多维数组。 Arr(1 to 12)、Arr1(0 to 24)----一维固定数组； Arr2(1 to 5,1 to 8)---- 二维固定数组； Arr3(5 to 10,6 to 12,1 to 100) ---- 三维固定数组。 动态数组 Dim Arr2(),r% r=r+1 ReDim Preserve Arr2(1 To r) ―――动态数组；可以重新声明（只有最后一维的数目才能重新声明）； 用了关键字 Preserve 可确保原来包含数据的数组中的任何数据都不会丢失 二、数组的赋值 2.1，单元格区域保存到数组 arr = [e22:i24] arr=Range(“e22:i 2.2，Array函数 myArray = Array("AAA", "BBB", 200, 500, " 如果代码头没有 Option Base 1 的语句，则数组myArray的上限为4，下限为0。 即下限LBound(myArr)=0 ，上限 UBound(myArr)=4
二维数组的第一维的上限：UBound(Arr,1) 二维数组的第二维的上限：UBound(Arr,2) 多维数组上限的求法一样。
2.3，把单元格区域公式赋给数组 如果a5=B4+1 arr = [a4:c8].Formula '将单元格绝对引用公式保存到数组 [e4:g8]=arr 此时e5中的公式也＝B4+1; 如果将单元格相对引用公式保存到数组 arr = [a4:c8].FormulaR 如果a5=B4+1 [e4:g8]=arr 此时e5中的公式就＝E4+1;
三、数组的处理 3.1，数组里的最大值和最小值 最大值aa = Application.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() 用于对字符串数组进行搜索，得到一个新的数组temp， 缺点：只告诉你某元素是否存在于数组中，而不知道其具体位置；
数组精确搜索： Sub FilterExactMatch() ' 该函数在一个字符串数组中搜索那些 ' 与搜索字符串完全匹配的元素。 Dim astrFilter() As String Dim astrTemp() As String Dim lngUpper As Long Dim lngLower 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 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 lngCount - 1) ' 返回包含完全匹配的元素的数组。 [a5].Resize(1, UBound(astrTemp) + 1) = Application.Transpose(astrTemp) End Sub
3.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) 也可写成 [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&, 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) = Application.Transpose(d.keys) 3.5，数组的排序 字符串数组不能用Large(Arr,i) 或者 Small(Arr,i) 来排序； 但数值数组可以； 一个很好的字典+数组排序的实例： Sub yy1() ‘by:oobird
Dim i%, c As Range, x, d As Object Set 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(c.Value) = d(c.Value) + 1 End If End If Next 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, 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 With End Sub 字符串数组的排序，可以使用辅助列，把数组各元素依次赋给单元格，然后对这些单元格运用Excel自有的数据排序功能进行排序，再把单元格排过序的值重新赋给数组。 3.6，数组赋给单元格区域 r=Ubound(Arr) r为一维数组的上限; Range("a2").Resize(1, r) = Arr '填充到工作表的一行之中（Arr为一维数组） 或者写成 Range("a2").Resize(1, Ubound(Arr)) = Arr
二维数组Arr(100,5) Range(“a [a1:e100]=Arr 或者写成 Range("a1").Resize(Ubound(Arr,1), Ubound(Arr,2)) = Arr 赋值方面的补充： Sub y()
Sub yy()
--------------------------------------------------------------------------------
红绳子 = Array("辣1", "辣2", "辣3", "辣4", "辣5", "辣6", "辣7", "辣8", "辣9", "辣10")
cName = Array("林思明", "曾玉婷", "曾国文", "林伟权", "林兴发", "刘卓怀", "曾仙婷", "林平") cj = Array(99, 85, 92.5, 70, 78.5, 65, 84, 100) -----------------------------
Private Sub CommandButton1_Click() '点击按钮C，运行本程序
cname = Array("林思明", "曾玉婷", "曾国文", "林伟权", "林兴发", "刘卓怀", "曾仙婷", "林平") '把数组元素写入工作表c4单元格向右8个单元格内 Range("c4:j4") = cname '或者 'Range("c4").Resize(1, 8) = cname End Sub Private Sub CommandButton2_Click() '点击按钮B，运行本程序 cname = Array("林思明", "曾玉婷", "曾国文", "林伟权", "林兴发", "刘卓怀", "曾仙婷", "林平") Range("d8:d15") = WorksheetFunction.Transpose(cname) '或者 'Range("d8").Resize(8, 1) = WorksheetFunction.Transpose(cname) End Sub --------------------------------------
Private Sub CommandButton1_Click() '点击按钮，运行本程序
'建立数组 cname = Array("林思明", "曾玉婷", "曾国文", "林伟权", "林兴发", "刘卓怀", "曾仙婷", "林平") '循环 For i = 1 To 8 'i是从1到8的变量 Range("a" & i) = cname(i) '相当于：range("a1")=cname(1)，range("a2")=cname(2)…… Next End Sub ----------------------------------------------
Private Sub CommandButton1_Click()
xm = Split(Range("a1"), ",") r = UBound(xm) Range("b1:b" & r + 1) = WorksheetFunction.Transpose(xm) MsgBox "数组最大下标是 " & r & Chr(13) _ & "数组共有 " & r + 1 & " 个元素 ", 64, "信息" End Sub -------------------------------
Private Sub Worksheet_Change(ByVal Target As Range) '修改单元格的值时运行本程序 If Target.Address = "$D$10" Then '如果修改D10单元格的值 cname = Array("林思明", "曾玉婷", "曾国文", "林伟权", "林兴发", "刘卓怀", "曾仙婷", "林平") Range("d11") = cname(Target) 'Target表示D10单元格的值 End If End Sub 数据保存到数组 arr = Sheet2.Range("a1:e83")
把数组内容写入到工作表 Range("a1:e83") = arr '光标定位到这一行，按F9设置断点。 Sub 声明数组()
Dim MyArray(10) '在过程中间声明一个数组，过程结束，本数组将被释放。 MyArray(1) = 100 '给数组赋值 MyArray(5) = 90 MyArray是数组名称； 10是数组最大下标。当Option Base 0的模式下，数组的序号(索引值)从0到10，表示数组有11个元素，当Option Base 1的模式下，数组的索引值从1到10，该语句创建的数组共有10个元素 -------------------- Dim Data(10,5) ’声明一个二维数组
Data (1,1) = “A001” '给数组赋值 Data (1,2) = 90 Data (3,1) = “A003” Data (3,2) = 80 Data是一个二维数组。二维数组的两维，我们通常用“行、列”称呼它，如Data，最大行号是10，最大列号是5，在Option Base 1 模式下，表示是一个10行5列共50个元素的数组空间。在Option Base 0 模式下，表示一个11行6列共66个元素的数组空间。 二维数组是使用频率最高的数组形式，因为我们的工作表也是二维的。 用工作表来领会二维数组，将会更加直观： Data(1,1)相当于Cells(1,1)，表示第1行，第1列； Data(5,3)相当于Cells(5,3)，表示第5行，第3列。 -------------------
Dim x(3, 30, 3)
x(1, 1, 1) = "1班" '给数组赋值 x(1, 1, 2) = "张三" x(1, 1, 3) = 88 x(1, 2, 1) = "1班" '给数组赋值 x(1, 2, 2) = "李四" x(1, 2, 3) = 95 x(2, 1, 1) = "2班" '给数组赋值 x(2, 1, 2) = "刘日" x(2, 1, 3) = 92 MsgBox "数组x第 1 维的最大下标 = " & UBound(x, 1) & Chr(13) _ & "数组x第 2 维的最大下标 = " & UBound(x, 2) & Chr(13) _ & "数组x第 3 维的最大下标 = " & UBound(x, 3) *********************************************
Dim MyArray(10) '声明一个数组 MyArray(1) = 100 '给数组赋值 MyArray(5) = 90 Dim cArr(11 To 20, 1 To 3) As String '声明一个数组，定义为文本型 cArr(11, 1) = "上" '给指定位置(11行1列)上的元素赋值 cArr(12, 1) = "中" cArr(13, 1) = "下" cArr(12, 2) = "人" cArr(13, 2) = "口" cArr(14, 2) = "手" cArr(14, 3) = "A" cArr(15, 3) = "B" cArr(16, 3) = "C" Dim nArr(-10 To 10, 1 To 2) As Integer '声明一个数组，定义为整型 For i = -6 To 6 '循环给数组赋值(部分) nArr(i, 1) = i nArr(i, 2) = Int(Rnd * 100) Next ------------------
数组声明
Dim 在模块级别中用 Dim 声明的变量，对该模块中的所有过程都是可用的。在过程级别中声明的变量，只在过程内是可用的。
Private Private 变量只能在包含其声明的模块中使用。 Public Public 语句声明的变量在所有应用程序的所有没有使用 Option Private Module 的模块的任何过程中都是可用的；若该模块使用了 Option Private Module，则该变量只是在其所属工程中是公用的。 Static 模块的代码开始运行后，使用 Static 语句声明的变量会一直保持其值，直至该模块复位或重新启动。可以在非静态的过程中使用 Static 语句显式声明只在该过程内可见，但具有与包含该过程定义的模块相同生命期的变量。 在模块级别中声明数组Arr()，各个过程中分别调用它。 Option Base 1
Dim Arr(10) '声明一个模块级别的数组变量，该变量在本模块下的各个过程中都可以使用 Sub 过程1() Arr(1) = "张三" Arr(2) = "李四" MsgBox "Arr(2) = " & Arr(2) End Sub Sub 过程2() Arr(2) = "山菊花" MsgBox "修改 Arr(2) 的值" End Sub Sub 过程3() MsgBox "Arr(2) = " & Arr(2) End Sub *******************************************************************
A1="朱清燕(女),林鑫,林秋静(女),林永鑫,林云艳(女),林嘉惠(女),曾文婷(女),林悦,陈诚,林伟健,赖紫岚(女),曾雁(女),赖文强,钟娟(女),张琪(女),林文彬,黄晓婷(女),李朕,林依婷(女),林佳利(女),曾德福"
把A1单元格的姓名整理成B、C两列，B列为姓名，C列为性别，从第6行开始排列
Dim Xm() As String, Arr() As String '声明两个动态数组，定义为字符型
Xm = Split(Range("a1"), ",") '把文本转换为数组 s = UBound(Xm) '求最大下标 ReDim Arr(0 To s, 1 To 2) '改变动态数组的大小，共s行，2列 For i = 0 To s If Right(Xm(i), 3) = "(女)" Then '如果后面三个字符是“(女)” Arr(i, 1) = Left(Xm(i), Len(Xm(i)) - 3) '取前面的字符 Arr(i, 2) = "女" Else Arr(i, 1) = Xm(i) Arr(i, 2) = "男" End If Next Range("b6").Resize(s, 2) = Arr '把结果保存到工作表指定的单元格中 |
|
|
