Excel VBA 编程基础 — 结构化数据类型(三)- 数组(二)-夜雨聆风

Excel VBA 编程基础 — 结构化数据类型(三)- 数组(二)

前面我们简单介绍了数组类型，今天继续讨论数组类型。

我们在前文中说“数组是一种由多个相同数据类型的元素组成的新类型，组成数组的元素类型可以是各种 VBA 的基本类型“，其实还不仅此，组成数组的元素类型还可以是用户定义类型 UDT。还是以例子来说明。

例1. 用数组读取 Excel 数据集

图1 Excel 数据集

对于上图所示的数据集，我们已经定义了一个 UDT ：

Type MathScore  id As Long  name As String  email As String  age As Integer  score As dDoubleEnd Type

下面我们来声明一个数组，用来读取该数据集的前10个记录：

Dim AMathScore(0 to 9) As MathScoreDim row As IntegerFor row = 2 To 11  With AMathScore(row - 2)    .id = CLng(Cells(row, 1).Value)    .name = Cells(row, 2).Value    .email = Cells(row, 3).Value    .age = CInt(Cells(row, 4).Value)    .score = CDbl(Cells(row, 5).Value)  End WithNext row

首先声明一个含有 10 个元素的数组 AMathScore，该数组的元素类型是 MathScore，是我们上面定义的 UDT。这样就可以读取 Excel 的数据集。

下图是完整的代码和测试结果：

图2 读取并打印 Excel 数据集

我们前面讨论的数组都是在声明时就确定大小的数组，这种数组称为静态数组（static array）。但有的时候在声明数组时并不能确定大小，需要根据运行情况动态调整数组的大小，为此，VBA 提供了动态数组（dynamic array）的概念。所谓动态数组，就是在代码运行过程中确定数组大小的数组类型。

下面看一个例子。

例2. 读取长度不确定的 Excel 数据集

Dim AMathScore() As MathScoreDim row As Integerrow = 2Do While Cells(row, 1).Value <> Empty  Redim Preserve AMathScore(row - 2)  With AMathScore(row - 2)    .id = CLng(Cells(row, 1).Value)    .name = Cells(row, 2).Value    .email = Cells(row, 3).Value    .age = CInt(Cells(row, 4).Value)    .score = CDbl(Cells(row, 5).Value)  End With  row = row + 1LoopDebug.Print "Number of elements: " & UBound(AMathScore) - LBound(AMathScore) + 1row = UBound(AMathScore)Debug.Print row & ": " & AMathScore(row).name & ", " & AMathScore(row).score

声明动态数组时，数组的长度为空，如 Dim AMathScore() As MathScore 所示。在代码运行期间，要使用 Redim 为数组分配空间。如果要保留数组已有的内容，需要加上 Preserve 关键字，如 Redim Preserve AMathScore(row – 2) 所示。

最后，打印出数组 AMathScore 的元素个数以及最后一个元素的相关信息，以检验代码是否能够正确工作。

上面的代码使用了 Do While … Loop 循环，根据 Cells(row, 1) 单元格的值是否为空来判断数据集是否结束。

为数组重新分配空间的语句 Redim 位于 Do While 的循环体，这意味着每循环一次都要执行 Redim 为数组 AMathScore 重新分配空间。为数组重新分配空间是一个非常昂贵的操作，尽量不要在循环中执行 Redim 语句。对于上面的例子，我们可以使用 Excel 的 CountA 函数来预先计算出数据集的大小，然后在进入循环之前使用 Redim 一次性为数组 AMathScore 分配重组的空间，以避免循环体内的空间分配。

优化过的代码如下图所示：

图3 使用动态数组读取并打印 Excel 数据集

在 VBA 中使用 Excel 函数，需要通过 WorksheetFunction 这个对象，Excel 函数以 WorksheetFunction 对象的方法的形式呈现给 VBA，如图中代码所示：WorksheetFunction.CountA()。

通过 CountA 获取到的数据集大小包括了第一行的表头，因此 size 要减去 1。Redim 重新定义数组 AMathScore 的上界，如果数组的下界为 0 的话，size 要再减去 1，才是 AMathScore 数组的上界。因此，我们有：

我们说过，通过 CountA 获取到的数据集行数（size）包括了第一行的表头，因此 size – 1 正是数据集所有数据的行数。从而，数组的元素个数等于数据集行数。这正是我们所需要的。

注：如果使用了 Option Base 1，这时数组的下界 LBound(AMathScore) = 1。则 ReDim 重新定义数组大小时的上界就应该是 size – 1，而不是 size – 2。此时，我们有：