EXCEL VBA: 重新排列数组的行和列

将通过ADO查询得到的RecordSet数据集，保存到数组：

Function RecordsetToArray(rs As Object) As Variant

    Dim results() As Variant
    Dim headers() As Variant
    Dim data As Variant
    Dim i As Long, j As Long

    ' 检查记录集是否为空
    If rs Is Nothing Then
        RecordsetToArray = Empty
        Exit Function
    End If

    ' 检查记录集是否有记录
    If rs.BOF And rs.EOF Then
        RecordsetToArray = Empty
        Exit Function
    End If

    ' 获取字段名称作为标题行
    ReDim headers(0 To rs.Fields.Count - 1)
    For i = 0 To rs.Fields.Count - 1
        headers(i) = rs.Fields(i).Name
    Next i

    ' 使用GetRows方法将数据保存到数组中
    data = rs.GetRows()

    ' 将标题和数据合并到一个数组中
    ReDim results(LBound(data, 2) To UBound(data, 2) + 1, LBound(headers) To UBound(headers))
    For i = LBound(headers) To UBound(headers)
        results(0, i) = headers(i)
    Next i

    For i = LBound(data, 2) To UBound(data, 2)
        For j = LBound(data, 1) To UBound(data, 1)
            results(i + 1, j) = data(j, i)
        Next j
    Next i

    ' 返回结果数组
    RecordsetToArray = results

End Function