Excel VBA: Can an array of dates be transposed?

Question

I have two arrays that I need to paste into my spreadsheet. Since each destination is a column, I'm using the Transpose command, like so:

Selection = WorksheetFunction.Transpose(arr1)

This works for one of the arrays, but the other gives me an Invalid Procedure Call (Error 5). Both arrays are the same size: arr(1 to 79).

The only difference between the two that I can tell is that all the values in the "good" array are Variant/String, and most of the ones in the "bad" array are Variant/Date.

Should that actually make a difference? If so, is there a way of converting the values in the latter array?

Answer 1

Properly coded the following should work:

Sub DepositArrays()
    Dim ary1(1 To 2) As String, ary2(1 To 2) As Date

    ary1(1) = "James"
    ary1(2) = "Ravenswood"
    ary2(1) = DateSerial(1945, 1, 20)
    ary2(2) = Date

    With Application.WorksheetFunction
        Range("A1:A2") = .Transpose(ary1)
        Range("B1:B2") = .Transpose(ary2)
    End With
End Sub

Yields:

Without the TRANSPOSE(), the code runs, but will not produce correct results.

Answer 2

why not to use a range:

Private Sub Test()

Dim arr(79) As Variant
For i = o To UBound(arr)
    arr(i) = Now
Next

Dim rng As Range
Set rng = Range(Cells(2, 2), Cells(81, 2))
rng = WorksheetFunction.Transpose(arr)

End Sub

also another option is a simple loop to iterate trough the array and populate data to the worksheet:

Private Sub Test2()

Dim arr(79) As Variant
For i = o To UBound(arr)
    arr(i) = Now
Next

For i = o To UBound(arr)
    Cells(i + 1, 1) = arr(i)
Next

End Sub

Answer 3

Both methods work for me, whether I use Transpose as a member of the Application class or the WorksheetFunction class. If any element of the array contains a string >= 255 characters in length, I get a Type Mismatch error, but I can't replicate the Invalid Procedure Call error.

Can you test this in a blank workbook?

Sub foo()
Dim arr1(79), vals, rng As Range
Dim i As Long

For i = LBound(arr1) To UBound(arr1)
    arr1(i) = DateAdd("d", i, #1/1/2016#)
Next

Set rng = Range("A1:A80")
rng = Application.Transpose(arr1)
vals = arr1

Set rng = rng.Offset(, 1)
rng = Application.WorksheetFunction.Transpose(arr1)

'Both methods work even if the destination range is the wrong size:
Set rng = rng.Offset(, 1).Resize(100)
rng = Application.Transpose(arr1)

Set rng = rng.Offset(, 1).Resize(50)
rng = Application.WorksheetFunction.Transpose(arr1)


End Sub