本VBA数组入门系列为兰色幻想原创,教大家学习和使用VBA数组

 
 一、什么是VBA数组
 
      不要把VBA数组想的太神秘,它其实就是一组数字而已。把一组数按行、列排在一起,放在内存中。就是一个VBA数组。
 
二、数组的维数:
       数组的维数其实和文件管理的目录一样的,只有一层时就是一维。如果有两层则是二维数组(有行有列为二维),如果有三层,则是三维数组。。VBA中支持1~3维数组运算。
 
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)  '通过循环把单元格区域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数组是不能一次性装入单元格数据
 
       或: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 = Range("a1:c7")   '把单元格区域A1:C7的值装入数组arr1 
   MsgBox 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例 
  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 = Application.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
 
arr(-19 to 8)  这个数组的编号就是从-19开始的.那么它的最小编号就是-19,最大编号是8, 如果用语句返回就是:
   Sub 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) '返回第2维(列的)最小编号,结果为2
       End Sub
 
 Sub t3()
        Dim arr
       arr = Sheets(1).UsedRange 'Usedrange的行数和列数是未知的
       MsgBox UBound(arr, 1) '可以计算这个区域有多少行
       MsgBox UBound(arr, 2) '可以计算出这个区域有多少列
 
使用Array函数创建常量数组:
使用Array函数创建数组
 
        1维常量数组:Array("A",1,"C")
 
        2维常量数组: Array(Array("a", 10), Array("b", 20), Array("c", 30))
 
 也可以调用excel工作表内存数组:
 
        1维数量:  [{"A",1,"C"}]
 
         2维数量: [{"a",10;"b",20;"c",30}]
 
内存常量数组有什么作用呢?
 
       1、简化赋值
 
              比如:我需要给数组arr分别赋值10 ,20,30,40 ,一般就需要分别赋值,即:
 
                     arr(1)=10
 
                     arr(2)=20
 
                     arr(3)=30
 
                     arr(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):
多个字符的合并和字符串按规律的拆分是经常遇到的,如:
 
            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
               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函数是:
 
         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数组入门教程之10(大结局):他山之石):
他山之石,可以攻玉,VBA中除可以利用的VBA函数外,还可以调用众多的Excel工作表函数对数组进行分解、查询和分析等,调用工作表函数可以省去循环判断的麻烦,进而提高运行效率。
        一、数组的最值
 
             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, 2) '第2大值
                          MsgBox Application.Small(arr, 2)  '第2小值
                      End Sub
 
           二、数组的统计与求和
 
                 1、Sum
 
                      Sum函数可以在工作表中求,同样也可以对VBA数组求和,如:
 
                     
                      Sub t2()
                          arr = Array(1, 35, 4, 13)
                          MsgBox Application.Sum(arr)  '对数组进行求和
                      End Sub
 
                2、Count和Counta
 
                     Count和Counta可以统计数组中数字的个数和数字+文本的个数。
 
                    Sub t3()
                         arr = Array(1, 35, "a", 4, 13, "b")
                         MsgBox Application.Count(arr)  '返回数字的个数4
                         MsgBox 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 Sub
 
             2、Index拆分数组
 
                  数组的拆分在VBA中是一个难题,如果是按行拆分数组,除了用循环外也只能借用API函数完成了。幸好我们可以借用工作表函数index达到按列拆分数组,即多列构成的数组,你可以任意拆分出一列构成新的数组。方法是:Application.Index(数组, , 列数) ,例:
 
                     Sub t2()
                         arr2 = Range("A1:B4") ‘把单元格区域A1:B4的值装入数组arr2
                         arr3 = 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 = Application.Transpose(arr)
                      MsgBox arr1(2, 1) ‘转换后的数组是1列多行的二维数组
                 End Sub
 
            2、二维数组转一维。
 
                  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数组,大家有空了就去尝试下吧。