兰版VBA数组入门10讲

兰版VBA数组入门10-1讲

 

Sub 数组入门(1)

 

  Dim x As Integer
  Dim arr(1 To 10) '创建一个可以盛下10个数的箱子，第一个位置是arr(1)，第二个位置用arr(2)..
 
  For x = 1 To 10   '通过循坏向这个箱子里1-10个位置放入10个数
 
     arr(x) = x  '箱子里依次放入数字1-10,即arr(1)=1,arr(2)=2....
    
  Next x
 
  '通过上面的循环,数组里装满了10个数字,下面就可以用arr(序数)得到相应位置的数了
 
  MsgBox arr(5) '显示数组内第5个数字
End Sub

 

兰版VBA数组入门10-3讲

VBA数组储存于内存中，单元格的数据，储存于工作表的单元格区域里。

    从单元格中取出数据放在内存。

          单元格是行列结构的数据表，把它搬到内存中也应该是二维的数组。怎么搬呢？

         为了更好的搬运，内存先批了一块土地。内存说了，你有多少个单元格我不问，反正你有多少数据我提供多大的地方。

  一、声明：

          Dim arr as Variant  '声明一个变量，这个变量应该是一个可以转化成数组的Variant类型（可以省略），不能声明其他数据类型

                             如： Dim arr as string   这种声明就是错误的，存放单元格的数据类型必须是 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

下期预告：搬入内存的目的是为了更快速的计算，计算结束后，怎么搬回到单元格中呢？且听下回分解。

 

 

转兰版VBA数组入门10-4讲

第3例我们学会了如何把单元格中的数据搬入内存，变身为VBA数组。那么，VBA数组怎么输回到单元格中呢？就象菜做好了，怎么更快的上桌呢？ 1、二维数组的导入：  VBA中常见的数组形式有一维数组和二维数组，相比一维数组，二维数组输入则较为简单，直接放在一个单元格区域中就可以，即：                 Range(单元格区域)=arr             '这里arr是一个二维数组，值得注意的是如果单元格区域小于二维数组，则显示二维数组的前面一部分数据，而如果单元格区域大于二维数组的数据储存量，则会显示错误值。  二维数组导入单元格示例： 如下图所示，要求在D列计算金额   此主题相关图片如下： 代码： 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 2、一维数组的导入：     一维数组只有一排数据，而导入到单元格中却有两种选择：导入到一行里 或 导入到一列中。    1）导入成为一行数据。          VBA 一维数组的排列方式 等同于 单元格中行方向排列，所以可以直接导入工作表的一行单元格里。而如果要放在一列中，则需要调用工作表函数transpose转置后再导入。例：  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

转兰版VBA数组入门10-5讲

1、什么是动态数组？         动态就是不固定的。       dIm arr(1 to 20)  '声明固定大小的数组空间       Dir arr()   '声明一个动态数组，数组有几维，有多少个元素，未知。 2、动态数组的使用       动态数组声明后，需要二次或多次声明      二次声明：所谓二次声明，就是这个动态数组开始不知道有多少行和多少列，后来经过计算，确定了它的大小，这时候就可以用Redim 再次声明它的大小了。例：把工作表A列的数据装入数组ARR 分析：因为经过计算才知道有多少个大于10的数字，所以这个数组要声明动态的 此主题相关图片如下：  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         如果数组需要随一个变量不断的扩充，数组就需要多次声明，每扩充一次就声明一次：Redim Preserve (1 to k)    如果数组是二维的，只能动态声明第二维的，有点麻烦，这里就不再详述

 

兰版VBA数组入门10-6讲

 

有时候我们需要返回一个最组的最大和最小下标，这里出现了一个新名词：下标     所谓下标就是数组内元素的编号。象在电影院看电影一样，数组内每一维数据有都独有的编号，比如arr(2)表示数组中编号为2数据。这里容易让人糊涂的是，数组的编号不一定是从1开始，而可以从负数开始，如：     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 上面例子中在声明时最大最小下标都是已知的,为什么还要再用再计算呢?  如果arr是一个动态数组,我们用得着计算最大和最小下标了.例:      Sub t3()         Dim arr        arr = Sheets(1).UsedRange 'Usedrange的行数和列数是未知的        MsgBox UBound(arr, 1) '可以计算这个区域有多少行        MsgBox UBound(arr, 2) '可以计算出这个区域有多少列 End Sub

兰版VBA数组入门10-7讲

 

在前面我们学习了创健一个固定大小的数组和动态数组，这些数组在创建时只是创建了储存空间还没有赋值，而本讲所说有常量数组则时提供的由一组常所构成的数组，如： 使用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

兰版VBA数组入门10-8讲

 

多个字符的合并和字符串按规律的拆分是经常遇到的，如：             A-REW-E-RWC-2-RWC 按分隔符-拆分成6个字符放在一个数组中            有一组数array(23,45,7,1,76)想用分隔符-连接成一个字符串    上面两种情况VBA提供了一对函数，即：               split（字符串,"分隔符"） 拆分字符串               join(数组,"分隔符")   用分隔连接数组的每个元成一个字符串    例1：            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

兰版VBA数组入门10-9讲

 

VBA数组教程第9例: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-10讲

 

前言： 时间过的真快，转眼间VBA数组入门已到第10例，虽然数组还有更多的深层用法，但本系列只是“入门”教程，所以也只能在第10例后划上句号了，希望本系列能给想学VBA数组的新手带来帮助。兰色如果有时间，会继续推出VBA数组的进阶教程系列，包括数组排序等。          他山之石，可以攻玉，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数组，大家有空了就去尝试下吧。