关于EXCEL中VBA的数组理解-.pdf

《关于EXCEL中VBA的数组理解-.pdf》由会员分享，可在线阅读，更多相关《关于EXCEL中VBA的数组理解-.pdf（15页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、关于 EXCEL 中 VBA 的数组理解一、什么是 VBA 数组不要把 VBA 数组想的太神秘，它其实就是一组数字而已。把一组数按行、列排在一起，放在内存中。就是一个VBA 数组。二、数组的维数数组的维数其实和文件管理的目录一样的，只有一层时就是一维。如果有两层则是二维数组（有行有列为二维），如果有三层，则是三维数组。VBA 中支持 13 维数组运算。Sub 数组示例()Dim x As Long,y As Long Dim arr(1 To 10,1 To 3)创建一个可以容下 10 行 3 列的数组空间For x=1 To 4 For y=1 To 3 arr(x,y)=Cells(x,y

2、)通过循环把单元格区域a1:c4 的数据装进数组中Next y Next x MsgBox arr(4,3)根据提供的行数和列数显示数组arr(1,2)=我改一下试试 你可以随时修改数组内指定位置的数据 MsgBox arr(1,2)End Sub总结：二维是由行和列表示的数组，如ARR(3,2)表示数组中第3 排第 2 列的元素。而一维数组只是由一个元素决定，如ARR（4）表示数组中第 4 个元素三、把单元格数据搬入内存：一、声明：Dim arr as Variant 声明一个变量，不能声明其他数据类型Dim arr(1 to 10,1 to 2),这种声明也是 错误的，固定大小的 VBA

3、数组是不能一次性装入单元格数据或：dim arr()这种声明方式是声明一个动态数组，也可以装入单元格区域，构成一个VBA 数组。二、装入arr=range(a9:c100)装入很简单，变量=单元格区域三、读出装入数组后的单元格数值，可以按数组名称(行数，列数)直接读取该位置的值，如下面的代码。Msgbox arr(3,2)就可以取出搬过去的而构成的数组第3 行第 2 列的内容四、示例 Sub s3()Dim arr()声明一个动态数组（动态指不固定大小）Dim arr1 声明一个 Variant 类型的变量arr=Range(a1:c7)把单元格区域 A1：C7 的值装入数组arr arr1=

4、Range(a1:c7)把单元格区域 A1：C7 的值装入数组arr1MsgBox arr(1,1)读取 arr 数组中第 1 行第 1 列的数值MsgBox arr1(2,3)读取 arr1 数组的第 2 行第 3 列的数值End Sub 四、把内存数据搬入单元格Sub test()Dim arr 声明一个变量用来盛放单元格数据Dim x As Integer arr=Range(a2:d5)把单元格数据搬入到arr 里，它有 4列 4 行For x=1 To 4 通过循环在 arr 数组中循环arr(x,4)=arr(x,3)*arr(x,2)数组的第 4 列(金额)=第 3列*第 2 例

5、Next x Range(a2:d5)=arr 把数组放回到单元格中End Sub Sub test1()Dim arr(1 To 5)声明一维数组For x=1 To 5 arr(x)=x*2 通过循环给每个位置赋值Next x Range(A1:E1)=arr 把数组导入到 excel 中的 a1:e1 单元格中Range(A1:A5)=Application.Transpose(arr)如果是放在一列中,就需要对数组进行转置后再存放End Sub 五、动态数组的声明Sub darr()Dim arr()声明一个动态的 arr 数组(不知道它能盛多少数据)Dim k k=Applicati

6、on.WorksheetFunction.CountIf(Range(a2:a6),10)计算大于 10 的个数ReDim arr(1 To k)再次声明 arr 的大小,正好盛下 k 数量的值For x=2 To 6 If Cells(x,1)10 Then m=m+1 arr(m)=Cells(x,1)通过循环把大于 10 的数字装入数组End If Next x MsgBox arr(2)End Sub 六、数组的上标(lBOUND)和下标(UBOUND)arr(-19 to 8)这个数组的编号就是从-19 开始的.那么它的最小编号就是-19,最大编号是 8,如果用语句返回就是:Sub

7、t1()Dim arr(-19 To 8)MsgBox UBound(arr)返回最大编号,结果为 8 MsgBox LBound(arr)返回最小编号,结果为-19 End Sub 如果是有行列组成的二维数组呢?二维数组返回行的下标和列的下标见下例Sub t2()Dim arr(-19 To 8,2 To 5)MsgBox UBound(arr)返回第 1 维(行的)最大编号,结果为 8 MsgBox LBound(arr)返回第 1 维(行的)小编号,结果为-19 MsgBox UBound(arr,2)返回第 2 维(列的)最大编号,结果为 5 MsgBox LBound(arr,2)返

8、回第 2 维(列的)最小编号,结果为 2 End Sub Sub t3()Dim arr arr=Sheets(1).UsedRange Usedrange 的行数和列数是未知的MsgBox UBound(arr,1)可以计算这个区域有多少行MsgBox UBound(arr,2)可以计算出这个区域有多少列End Sub 七、使用 Array函数创建常量数组：使用 Array 函数创建数组1 维常量数组：Array(A,1,C)2 维常量数组：Array(Array(a,10),Array(b,20),Array(c,30)也可以调用 excel 工作表内存数组：1 维数量：A,1,C 2 维

9、数量：a,10;b,20;c,30内存常量数组有什么作用呢？1、简化赋值比如：我需要给数组arr 分别赋值 10,20,30,40，一般就需要分别赋值，即：arr(1)=10arr(2)=20arr(3)=30arr(4)=40而使用常量数量，只一句话：arr=array(10,20,30,40)2、调用工作表函数时使用：Sub mylook()Dim arr arr=a,10;b,20;c,30 MsgBox Application.VLookup(b,arr,2,0)调用 vlookup 时可以作为第二个参数End Sub 八、数组的合并和字符串拆分（Join&Split）：多个字符的合并

10、和字符串按规律的拆分是经常遇到的，如：A-REW-E-RWC-2-RWC 按分隔符-拆分成 6 个字符放在一个数组中有一组数 array(23,45,7,1,76)想用分隔符-连接成一个字符串上面两种情况 VBA 提供了一对函数，即：split（字符串,分隔符）拆分字符串join(数组,分隔符)用分隔连接数组的每个元成一个字符串Sub t1()Dim arr,myst As String myst=A-REW-E-RWC-2-RWC arr=Split(myst,-)按-分隔成一组数装入数组中 MsgBox arr(0)显示数组的第一个数(分隔后的数组最小下标为 0,不是 1),显示结果为 A

11、 MsgBox Join(arr,)再用,把数组的每个值连接成一个字符串,结果为A,REW,E,RWC,2,RWC End Sub值得注意的是：split 和 join 只能对一维数组进行操作，如果是单元格或二维数组怎么办？只有一条途径，想办法转换为一维数组：Sub t2()Dim ARR ARR=Application.Transpose(Range(a1:a3),用转置的方法，把单元格一列数据转换成一维数组MsgBox Join(ARR,-)End Sub九、Filter 函数实现数组筛选:数组的筛选就是根据一定的条件,从数组中筛选符合条件的值,组成一个新的数组，实现数组筛选的VBA 函数

12、是：Filter 函数用法：Filter(数组,筛选的字符,是否包含)Sub DD()arr=Array(ABC,A,D,CA,ER)arr1=VBA.Filter(arr,A,True)筛选所有含 A 的数值组成一个新数组arr2=VBA.Filter(arr,A,False)筛选所有不含 A 的数值组成一个新数组MsgBox Join(arr2,)查看筛选的结果End Sub 遗憾的是函数只能进行模糊筛选，不能精确匹配。他山之石，可以攻玉，VBA 中除可以利用的 VBA 函数外，还可以调用众多的 Excel 工作表函数对数组进行分解、查询和分析等，调用工作表函数可以省去循环判断的麻烦，进而

13、提高运行效率。一、数组的最值 1、Max 和 Min工作表函数 Max 和 Min 是求最大值和最小值的函数，同样在VBA 中也可以求数组的最大值和最小值。如：Sub t()arr=Array(1,35,4,13)MsgBox Application.Max(arr)最大值MsgBox Application.Min(arr)最小值End Sub 2、large 和 small工作表函数 large 和 small 是返回一组数的第N 大和第 N 小，对 VBA 数组同样适用，如：Sub t1()arr=Array(1,35,4,13)MsgBox Application.Large(arr,

14、2)第 2 大值MsgBox Application.Small(arr,2)第 2 小值End Sub 二、数组的统计与求和1、SumSum 函数可以在工作表中求，同样也可以对 VBA 数组求和，如：Sub t2()arr=Array(1,35,4,13)MsgBox Application.Sum(arr)对数组进行求和End Sub2、Count 和 CountaCount 和 Counta 可以统计数组中数字的个数和数字+文本的个数。Sub t3()arr=Array(1,35,a,4,13,b)MsgBox Application.Count(arr)返回数字的个数 4MsgBox

15、Application.CountA(arr),返回数组文本和数字的总个数End Sub 三、数组的查询和拆分1、Mach 查询数组Match 函数可以查询一个指定值在一组数中的位置，它也可以用于 VBA 数组的查询。如：Sub t4()arr=Array(1,35,4,13)MsgBox Application.Match(4,arr,0)查询数值 4 在数组 Arr中的位置End Sub2、Index 拆分数组数组的拆分在 VBA 中是一个难题，如果是按行拆分数组，除了用循环外也只能借用API 函数完成了。幸好我们可以借用工作表函数index 达到按列拆分数组，即多列构成的数组，你可以任意

16、拆分出一列构成新的数组。方法是：Application.Index(数组,列数)，例：Sub t2()arr2=Range(A1:B4),把单元格区域 A1：B4 的值装入数组 arr2arr3=Application.Index(arr2,2)把数组第 2 列拆分出来装入新数组 arr3 中，新数组为二维数组MsgBox arr3(2,1)取出新数组第 2 行的值End Sub 四、数组维数的转换Transpose 转置数组在工作表中可以把行列转换。在VBA 中同样也可以做到转换的效果。1、一维转二维。Sub t9()arr=Array(1,35,a,4,13,b)arr1=Applicat

17、ion.Transpose(arr)MsgBox arr1(2,1),转换后的数组是 1 列多行的二维数组End Sub2、二维数组转一维。Sub t2()arr2=Range(A1:B4)arr3=Application.transpose(Application.Index(arr2,2)取得 arr2 第 2 列数据并转置成 1 维数组MsgBox arr3(2,)End Sub 注：在转置时只有1 列 N 行的数组才能直接转置成一维数组思考题：我要把 a1:c1 中的内容用“-”连接起来，下面代码中为什么用了两次 transpose Sub t10()arr=Range(A1:C1)MsgBox Join(Application.Transpose(Application.Transpose(arr),-)End Sub用于 VBA 数组的工作表函数我只是列出了一部分，其实象 vlookup，Lookup 等等函数也可以用于处理VBA 数组，大家有空了就去尝试下吧。