时间戳

首页 > 解决方案 > 转置多维数组

excel - 转置多维数组

问题描述

我写了一些代码，应该用值填充一个多维数组。初始数组的大小为 1000x1000，但最后它应该重新调整为所需的（实际使用的）大小。为此，我使用代码：

ReDim Preserve MultiArrPaerchen(LBound(MultiArrPaerchen, 1) To UBound(MultiArrPaerchen, 1), LBound(MultiArrPaerchen, 2) To n)
Application.WorksheetFunction.Transpose (MultiArrPaerchen)
ReDim Preserve MultiArrPaerchen(LBound(MultiArrPaerchen, 1) To UBound(MultiArrPaerchen, 1), LBound(MultiArrPaerchen, 2) To m)
Application.WorksheetFunction.Transpose (MultiArrPaerchen)

发生的情况是数组的大小从 1000x1000 变为 1000x120（正确值）和 1000x18。因此，如果我正确地看到这一点，那么第一个转置不会做任何事情，因为数组的维度没有切换。但是代码看起来正确不是吗？

标签： excelvbamultidimensional-arraytranspose

解决方案

我相信我可能在对上一个问题的评论中提供了错误的信息。Application.Transpose像任何函数一样返回转置后的数组。它不像 ByRef 参数那样“就地”处理它。

'NOT this was
Application.WorksheetFunction.Transpose (MultiArrPaerchen)
'Do it THIS WAY
MultiArrPaerchen = Application.WorksheetFunction.Transpose(MultiArrPaerchen)

FWIW，自动添加的空间应该被视为语法有问题的迹象。

第一个 ReDim 调整第二个等级；第二个等级是在使用 ReDim 唱 Preserve 参数时唯一可以调整的等级。

第一个 Transpose 翻转第一和第二等级。现在您可以重新调整不是最后一个等级的原始第一等级。

第二个 ReDim 调整最后一个等级（最初是第一个）。

第二个 Transpose 采用翻转后的数组并将其返回到其原始方向，并在两个等级中使用新维度。

Sub test()

    Dim MultiArrPaerchen As Variant, m As Long, n As Long

    m = 1000
    n = 1000

    ReDim MultiArrPaerchen(1 To m, 1 To n)
    Debug.Print LBound(MultiArrPaerchen, 1) & " to " & UBound(MultiArrPaerchen, 1) & ", " & _
                LBound(MultiArrPaerchen, 2) & " to " & UBound(MultiArrPaerchen, 2)
    'results: 1 to 1000, 1 to 1000

    'UBounbds for 1 to 18, 1 to 120
    m = 18
    n = 120

    ReDim Preserve MultiArrPaerchen(LBound(MultiArrPaerchen, 1) To UBound(MultiArrPaerchen, 1), _
                                    LBound(MultiArrPaerchen, 2) To n)
    Debug.Print LBound(MultiArrPaerchen, 1) & " to " & UBound(MultiArrPaerchen, 1) & ", " & _
                LBound(MultiArrPaerchen, 2) & " to " & UBound(MultiArrPaerchen, 2)
    'results: 1 to 1000, 1 to 120

    MultiArrPaerchen = Application.Transpose(MultiArrPaerchen)
    Debug.Print LBound(MultiArrPaerchen, 1) & " to " & UBound(MultiArrPaerchen, 1) & ", " & _
                LBound(MultiArrPaerchen, 2) & " to " & UBound(MultiArrPaerchen, 2)
    'results: 120, 1 to 1000

    ReDim Preserve MultiArrPaerchen(LBound(MultiArrPaerchen, 1) To UBound(MultiArrPaerchen, 1), LBound(MultiArrPaerchen, 2) To m)
    Debug.Print LBound(MultiArrPaerchen, 1) & " to " & UBound(MultiArrPaerchen, 1) & ", " & _
                LBound(MultiArrPaerchen, 2) & " to " & UBound(MultiArrPaerchen, 2)
    'results: 1 to 120, 1 to 18

    MultiArrPaerchen = Application.Transpose(MultiArrPaerchen)
    Debug.Print LBound(MultiArrPaerchen, 1) & " to " & UBound(MultiArrPaerchen, 1) & ", " & _
                LBound(MultiArrPaerchen, 2) & " to " & UBound(MultiArrPaerchen, 2)
    'results: 1 to 18, 1 to 120
End Sub

推荐阅读