VBA: 将数组输出到工作表

文章背景：工作中，有时需要获取数据源的信息，临时存储在数组中，然后再将数组中的数据传递到目标工作表中。其中主要用到Resize函数，WorksheetFunction.transpose函数和WorksheetFunction.Index函数。

1 函数介绍

1.1 Resize函数

1.2 WorksheetFunction.Transpose函数

1.3 WorksheetFunction.Index函数

2 一维数组输出数据到工作表

2.1 输出到工作表的一行

2.2 输出到工作表的一列

2.3 Transpose函数的限制

3 二维数组输出到工作表的对应列

1 函数介绍

1.1 Resize函数

Resizes the specified range. Returns a Range object that represents the resized range.

1.2 WorksheetFunction.Transpose函数

Returns a vertical range of cells as a horizontal range, or vice versa.

1.3 WorksheetFunction.Index函数

Returns a value or the reference to a value from within a table or range. There are two forms of the Index function: the array form and the reference form.

（1）数组形式：INDEX(array，row_num，column_num)返回数组中指定的单元格或单元格数组的数值。

Use the array form if the first argument to Index is an array constant.

If you set row_num or column_num to 0 (zero), Index returns the array of values for the entire column or row, respectively.

（2）引用形式：INDEX(reference，row_num，column_num，area_num)返回引用中指定单元格或单元格区域的引用。

If you set row_num or column_num to 0 (zero), Index returns the reference for the entire column or row, respectively.

2 一维数组输出数据到工作表

2.1 输出到工作表的一行

Option Explicit
Option Base 1

Sub oneArrayToRow()
    
    '一维数组输出到工作表的一行
    
    Dim arr(1 To 10), ii As Integer
    
    Dim tar_sheet As Worksheet
    
    Set tar_sheet = ThisWorkbook.Sheets("Sheet1")
    
    For ii = 1 To 10
    
        arr(ii) = ii
    
    Next ii
    
    tar_sheet.Range("A1").Resize(1, 10) = arr
    
End Sub

2.2 输出到工作表的一列

Option Explicit
Option Base 1

Sub oneArrayToCol()
    
    '一维数组输出到工作表的一列
    
    Dim arr(1 To 10), ii As Integer
    
    Dim tar_sheet As Worksheet
    
    Set tar_sheet = ThisWorkbook.Sheets("Sheet1")
    
    For ii = 1 To 10
    
        arr(ii) = ii
    
    Next ii
    
    tar_sheet.Range("A1").Resize(10, 1) = Application.Transpose(arr)
    
End Sub

2.3 Transpose函数的限制

数据超过65536条时，Transpose函数会转置失败。因此，当数据超过65536条时，不要使用Transpose函数。

转置失败的例子：

Sub FalseCase()

    '数据超过65536条时，Transpose函数会转置失败。

    Dim arr(1 To 65538), ii As Long
    
    Dim tar_sheet As Worksheet
    
    Set tar_sheet = ThisWorkbook.Sheets("Sheet1")
    
    For ii = 1 To 65538
    
        arr(ii) = ii
    
    Next ii
    
    tar_sheet.Range("A1").Resize(65538, 1) = Application.Transpose(arr)
    
End Sub

3 二维数组输出到工作表的对应列

Option Explicit
Option Base 1

Sub twoArrayToCol()
    
    '二维数组，输出到工作表的对应列
    
    Dim arr(10, 2), ii As Integer, row_data As Integer
    
    Dim tar_sheet As Worksheet
    
    Set tar_sheet = ThisWorkbook.Sheets("Sheet1")
    
    For ii = 1 To 10
         
        arr(ii, 1) = ii         '第一列：1到10
        arr(ii, 2) = 11 - ii    '第二列：10到1
    
    Next ii
    
    tar_sheet.Cells(1, 1).Resize(10, 1) = Application.Index(arr, , 1)
    tar_sheet.Cells(1, 2).Resize(10, 1) = Application.Index(arr, , 2)
    
End Sub

参考资料：

[1] 数组转置Transpose、数组Resize输出到工作表(http://www.360doc.com/content/18/0707/06/28740943_768444928.shtml)

[2] Range.Resize(https://docs.microsoft.com/en-us/office/vba/api/excel.range.resize)

[3] WorksheetFunction.Transpose(Object) Method(https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel.worksheetfunction.transpose?view=excel-pia)

[4] WorksheetFunction.Index(https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel.worksheetfunction.index?view=excel-pia)

[5] INDEX的两类应用说明(https://zhuanlan.zhihu.com/p/43052922)

[6] VBA 用application.index() 取2维数组里的某1列，某1行(https://blog.csdn.net/xuemanqianshan/article/details/103689255)