VBA数组和Excel工作表数据传递

本文介绍如何利用 VBA 的数组（Array) 来提高 Excel 单元格和外部数据传输的性能。如果数量比较大，通过 Array 来传输数据比直接操作单元格要快若干倍。

将 Range 的数据写入 VBA Array

将 Range 数据写入 VBA 的数组非常简单。下面的例子演示了用法：

Dim Arr() as Varint  ' Declare an unallocated array
Arr = Range("A1:C5") ' Arr is now an allocated array

当我们将数据从工作表的range 写入到 VBA 数组的时候，这个数组始终是二维的。第一个维度是行，第二个维度是列。所以，刚才定义的数组有 5 行，有 3 列，等同于 Arr(1 to 5, 1 to 3) 。需要注意的是，即使在 Excel 中只有一行或者一列，这个数组也是二维的，可以理解为 Arr(1 to 1, 1 to 1)。 另外，数组的下标 (LBound) 始终从 1 开始。比如：

Dim Arr() as Variant
Arr = Range("A1:A10")

Arr 相当于 Arr(1 to 10, 1 to 1)

下面的代码演示了如何遍历工作表中 Range 的值：

Public Sub PrintCells()
    Dim arr() As Variant
    arr = Range("A1:C5")
    
    Dim r As Long  ' r: row
    Dim c As Long  ' c: column
    For r = 1 To UBound(arr, 1)     '第一个维度是行数
        For c = 1 To UBound(arr, 2) '第二个维度是列数
            Debug.Print arr(r, c),
        Next
        Debug.Print
    Next
End Sub

有一个特例。如果单元格的数量只有一个，那么就不能作为二维数组。上面的代码在一个单元格的时候会出错，需要调整为下面的代码：

Public Sub PrintCells(rng as Range)
    Dim arr() As Variant
    If rng.Cells.Count = 1 Then
        ReDim arr(1 To 1, 1 To 1)
        arr(1, 1) = rng.Value
    Else
        arr = rng
    End If
    
    Dim r As Long  ' r: row
    Dim c As Long  ' c: column
    For r = 1 To UBound(arr, 1)     '第一个维度是行数
        For c = 1 To UBound(arr, 2) '第二个维度是列数
            Debug.Print arr(r, c),
        Next
        Debug.Print
    Next
End Sub

将一维数组写入单元格

一维数组可以按行输出，实现的要点：定义一个 Range，将 Range 的列扩充为数组的UBound。

Public Sub WriteOneDimensionArr()
    Dim arr() As Variant
    ReDim arr(1 To 5)
    arr(1) = 1
    arr(2) = 2
    arr(3) = 3
    arr(4) = 4
    arr(5) = 5
    
    Dim destination As Range
    Set destination = Range("A10")
    Set destination = destination.Resize(1, UBound(arr)) '对数组进行行扩充
    destination.Value = arr
End Sub

当然，一维数组也可以实现按列输出：

Public Sub WriteOneDimensionArr2()
    Dim arr() As Variant
    ReDim arr(1 To 5)
    arr(1) = 1
    arr(2) = 2
    arr(3) = 3
    arr(4) = 4
    arr(5) = 5
    
    Dim destination As Range
    Set destination = Range("A10")
    Set destination = destination.Resize(UBound(arr), 1) '对数组进行行扩充
    destination.Value = Application.Transpose(arr)
End Sub

二维数组写入单元格

实际上，将二维数组写入到单元格更加直观。数组的第 1 个维度是行数，第 2 个维度是列数。我们通过下面的示例，演示如果将 A1:C5 的数据拷贝到另外一个区域：

Public Sub CopyCells()
    Dim arr() As Variant
    arr = Range("A1:C5")
        
    Dim destination As Range
    Set destination = Range("A10")
    destination.Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
End Sub