excel - .Transpose 和 .Index 在 16.60 更新后失败

Question

在 Mac 的 Excel 16.60 更新后，我无法让 .transpose 和 .index 在任何宏中工作。错误是

"Run-time error '1004': Method 'Transpose' of object 'WorksheetFunction' failed".

这是我为一个简单的测试编写的代码:

Sub Test_Here()
    Dim testString() As String
    Dim xVar As Variant
    ReDim testString(3, 3)
    testString(0, 0) = "test11": testString(0, 1) = "test12": testString(0, 2) = "test13": testString(0, 3) = "test14"
    testString(1, 0) = "test21": testString(1, 1) = "test22": testString(1, 2) = "test23": testString(1, 3) = "test24"
    testString(2, 0) = "test31": testString(2, 1) = "test32": testString(2, 2) = "test33": testString(2, 3) = "test34"
    testString(3, 0) = "test41": testString(3, 1) = "test42": testString(3, 2) = "test43": testString(3, 3) = "test44"
    Worksheets("Sheet1").Range("A1").Resize(UBound(testString, 2) + 1, UBound(testString, 1) + 1).Value = WorksheetFunction.Transpose(testString)
End Sub

Answer 1

VBA 的 Transpose长期以来一直是 Windows 和 Mac 上间歇性问题的原因。当我尝试您的代码时，它呈现出韩语风格。 (我不确定它是否是真实的文字，但绝对是基于韩文的文字!)
我没有尝试用 VBA 来实现一些神奇的事情，而是提供了一个手动转置数组的函数。创建对象时 testString ，您必须将其创建为 Variant让这个代码工作。

Sub Test_Here()
    
    Dim testString() As Variant
    Dim xVar As Variant
    
    ReDim testString(3, 3)
    
    testString(0, 0) = "test11": testString(0, 1) = "test12": testString(0, 2) = "test13": testString(0, 3) = "test14"
    testString(1, 0) = "test21": testString(1, 1) = "test22": testString(1, 2) = "test23": testString(1, 3) = "test24"
    testString(2, 0) = "test31": testString(2, 1) = "test32": testString(2, 2) = "test33": testString(2, 3) = "test34"
    testString(3, 0) = "test41": testString(3, 1) = "test42": testString(3, 2) = "test43": testString(3, 3) = "test44"
    
    testString = TranspA(testString)                                      'transpose the array

    'add to worksheet
    Worksheets("Sheet1").Range("A1").Resize(UBound(testString, 2) + 1, UBound(testString, 1) + 1).Value = testString

End Sub
   

Public Function TranspA(arD As Variant) As Variant 'arD as an array of data
    Dim X As Long
    Dim Y As Long
    Dim Xu As Long
    Dim Yu As Long
    Dim tempA As Variant  'temporary array holder
    
    Xu = UBound(arD, 2)
    Yu = UBound(arD, 1)
    ReDim tempA(Xu, Yu)
    For X = 0 To Xu
        For Y = 0 To Yu
            tempA(X, Y) = arD(Y, X)
        Next Y
    Next X
    TranspA = tempA
    
End Function