.Transpose and .Index failing after 16.60 update-CodePudding

After the Excel 16.60 update for mac I cannot get .transpose and .index to work in any macros. The error is

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

Here's code that I wrote for a simple test:

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

CodePudding user response：

VBA's Transpose has been a cause of intermittent issues on Windows and Mac for a very long time. It rendered Korean-ish when I tried your code. (I'm not sure if it was real words, but definitely based on the Korean written language!)

Instead of trying to get something magical to happen with VBA, I've provided a function that manually transposes an array. When you create the object testString, you have to create it as a Variant for this code to work.

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