Excel【VBA教程】数组了解一下（vba数组通俗讲解）

本文于2023年9月26日首发于本人同名公众号：Excel活学活用，更多文章案例请搜索关注！

内容提要

• 数组详解

大家好，我是冷水泡茶，今天我们一起来了解一下【数组】它在内存中运算，速度比在单元格中快得多。

数组，英文名称为Array。Array也是VBA的一个函数。

数组，可以简单地理解为“一组数”，比如（1,2,3,4,5），当然在表达方式方面有规定的格式。下面我们就逐一了解。

一、数组的定义

我们在使用数组之前，我们首先要定义一个数组，定义数组有两种方式：

Dim arr1(5)
Dim arr2()

第一种在定义的时候就指定了数组的元素个数，arr1(5)表示一个一维数组，它有6个元素。这个数组称为静态数组，它的维度与元素个数是固定的，不能被改变。

第二种在定义的时候，不指定数组的维度与元素个数，在后续使用的时候，再重新定义。这种数组我们称为动态数组。并且，在接下来的讨论当中，除非特别说明，我们都说的是动态数组。

我们在定义动态数组的时候，后面一般要加上括号，表明这是一个数组。如果不加括号，在定义的时候，它就是一个变体变量，在后续给它赋值为数组，它也可以变成一个数组

这里顺便说一下变体变量类型（Variant），就是不指定变量类型，什么类型都可以，但请尽避免这么做，有时候可能会使代码不容易理解，过多的这种Variant变量，会影响到程序的效率。

二、数组的维度与下标

数组的维度，我们可以理解为有几组下标，就是几维数组。

数组的下标，就是数组元素的位置，我们通过指定下标来引用、操作数组的元素。

如果不特别处理，数组的下标一般都是从0开始，arr1(5)包含6个元素。如果不习惯的话，可以在模块上方添加语句：Option Base 1，强制下标从1开始，这样arr1(5)就包含5个元素。

在我们把工作表单元格区域整体赋值给数组的时候，下标是从1开始的。

获取数组的下标:

1、LBound(arr,1)，取得数组的最小行标，这里1可以省略直接写成LBound(arr）

2、LBound(arr,2)，取得二维数组的最小列标，通常情况下，最小列标与最小行标是相等的，即arr(0,0）表示数组的第一个元素。不过，它们可以不相等。

3、UBound(arr,1)，取得数组的最大行标，这里1可以省略直接写成UBound(arr）

4、UBound(arr,2)，取得二维数组的最大列标。

三、数组的赋值

1、通过指定下标给数组赋值：

Dim arr1(5)
For i = 0 To 5
    arr1(i) = i
Next

或者

arr1(0)=0
arr1(1)=1
arr1(2)=2
arr1(3)=3
......

2、把工作表的一个确定的数据区域整体赋值给数组

Dim ws As Worksheet
Dim arr()
Set ws = Sheets("Sheet1")
arr = ws.UsedRange

或者

Dim ws As Worksheet
Dim arr()
Set ws = Sheets("Sheet1")
arr = ws.Range("A1").CurrentRegion

或者

Dim ws As Worksheet
Dim arr()
Set ws = Sheets("Sheet1")
arr = ws.Range("A1:O15").value

或者

Dim ws As Worksheet
Dim lastRow As Long, lastCol As Long
Dim arr()
Set ws = Sheets("Sheet1")
With ws
    lastRow = ws.UsedRange.Rows.Count
    lastCol = ws.UsedRange.Columns.Count
    arr = ws.Range(.Cells(1, 1), .Cells(lastRow, lastCol))
End With

还有其他方式把工作表赋值给数组，我们就不一一列举了。

我最常用的是最后一种方法，虽然有点繁琐，但操作起来比较灵活。比如有lastRow、lastCol变量可以利用，开始行列可以根据需要调整，不一定是从第一行、第一列开始写入数组。

3、把数组赋值给数组

Dim arr()
arr = Array(1, 2, 3, 4, 5, 6)
arr = Array("A", "B", "C")
arr = Array(Array(1, 2, 3), Array("A", "B", "C"))

或者

Dim ws As Worksheet
Dim lastRow As Long, lastCol As Long
Dim arr(), arr1()
Set ws = Sheets("Sheet1")
With ws
    lastRow = ws.UsedRange.Rows.Count
    lastCol = ws.UsedRange.Columns.Count
    arr = ws.Range(.Cells(1, 1), .Cells(lastRow, lastCol))
End With
arr1 = arr

4、把数据库查询的记录集赋值给数组

Dim rs As Object
Dim cnn As Object
Dim arr()
Set cnn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
......
Set rs = cnn.Execute(Sql)
arr = rs.getrows

5、把字典的Keys、Items赋值给数组

arr = dic.keys
arr = dic.items

6、通过循环给数组赋值

（1）循环单元格给数组赋值

Dim ws As Worksheet
Dim arr()
Set ws = Sheets("Sheet1")
ReDim arr(1 To 15, 1 To 15)
For i = 1 To 15
    For j = 1 To 15
     arr(i, j) = ws.Cells(i, j)
    Next
Next

（2）循环数组给数组赋值

'相当于把arr1转置
For i = 1 To 10
    For j = 1 To 15
      arr(i, j) = arr1（j,i）
    Next
Next

7、数组动态扩展赋值

当我们要把一些符合条件的数据写入数组时，我们没办法确切地知道数组元素的多少，我们可以定义一个非常大的数组，比如，ReDim arr(1 to 10000)，这样可以从容写入数据，不至于出现“下标越界”的错误。

不过，我不太主张这种方式。有一种方法，叫动态扩展，在改变数组大小的同时，保留已有数据：redim preserve arr(...)，这是【物资管理：根据物资发出数量、归还数量，求未归还数量/未归还数量单元格区域自动更新】中使用的例子代码

For Each Key In dic.keys
   If dic(Key) <> 0 Then
       ReDim Preserve arr(0 To 1, 0 To k)
       arr(0, k) = Key
       arr(1, k) = dic(Key)
       k = k + 1
   End If
Next

但我们要注意，数组的扩展只能是扩展列，不能扩展行。如果我们必须要扩展行的话，我们可以来个转置，扩展列，数据处理完成后，再转置回去。就如同上面的代码，在数组中，数据是这样的，向箭头方向扩展，最终我们存到工作表，是经过转置后的数据：

四、数组数据写入工作表

Set rng = ws.Range("I3").Resize(UBound(arr, 2) + 1, 2)
rng = Application.WorksheetFunction.Transpose(arr)

数据在数组中经过处理以后，大多要回写到工作表。我们要指定一个与数组一般大小的区域，如果数据区域指定得不准确，要么会遗漏数据，要么在工作表中出现错误值。

这里用了一个工作表函数Transpose转置数组，这里要注意一下，这个Transpose函数转置的数组中，不能用Null值，有则报错。这种情况通常发生在从数据库里查询数据存入数组中，可能会有Null值。

如果使用Transpose报错，我们应该怎么办呢？如果数据不多，我们可以直接循环数组，把数组的每个元素的值直接写入单元格，只要把Cells的行列标与数组arr的行列对调就可以了，当然要加上一个数字，对应到实际单元格。

如果数据很多，我们可以重新定义一个数组，行列跟原来数组对调，通过循环把数据写入新的数组，

For i = 1 To 10
    For j = 1 To 15
      arr(i, j) = arr1（j,i）
    Next
Next

再把新的数组直接写入工作表，不用再转置。

五、数组在用户窗体控件中的应用，时间关系，不展开了，在前期文章中也多有提及。

1、把数组赋值给复合框的List

2、把数组赋值给ListView

3、把数组赋值给ListBox

4、把数组赋值给TreeView

其实，万变不离其宗，就是循环数组。

好，今天就到这吧。

~~~~~~End~~~~~~

喜欢就点个赞、点在看、留言评论、分享一下呗！感谢支持！