WorksheetFunction.Transpose changes data type

Question

I was using WorksheetFunction.Transpose in VBA to convert a 1D array of mixed dates/strings into a 2D array for writing to a worksheet.

With my windows regional settings set to DMY, the dates being written back were having months/day switched.

Answer 1

This has been mentioned in this forum with regard to Dates being converted to Strings by the WorksheetFunction.Transpose method.

I looked into this more deeply.

It seems that WorksheetFunction.Transpose converts many data types. The result, after transposition, will be either Boolean, Double, or String

This needs to be taken into account when using this method in VBA.

Here is some code demonstrating the issue:

Option Explicit
Option Base 1
Sub Tester()
    Dim v, w, i As Long
    
v = Array(CBool(-1), CBool(0), CByte(9), CDbl(1234), CDec(1234), _
            CInt(1234), CLng(1234), CLngPtr(1234), CSng(1234), _
            CCur(123456), #5/1/2015#, "1234")
            
w = WorksheetFunction.Transpose(v)

For i = 1 To UBound(v)
    Debug.Print v(i), TypeName(v(i)), w(i, 1), TypeName(w(i, 1))
Next i


End Sub

debug.print output

True          Boolean       True          Boolean
False         Boolean       False         Boolean
 9            Byte           9            Double
 1234         Double         1234         Double
 1234         Decimal        1234         Double
 1234         Integer        1234         Double
 1234         Long           1234         Double
 1234         Long           1234         Double
 1234         Single         1234         Double
 123456       Currency      $123,456.00   String
01-May-15     Date          01-05-2015    String
1234          String        1234          String

EDIT Another issue with WorksheetFunction.Transpose

• Given a 1D array
• The upper bound of the first dimension of the transposed array will be given by the formula Ubound(1D_array) mod 2^16
• Only the first uBound(1D_array) mod 2^16 elements will be returned to the transposed array.
  • Therefore if the 1D array has a ubound of 65537, the transposed array will only contain a single item (the first item in the original array)
  • No error message will be returned.
    • I believe the lack of an error message and this behavior started with Excel 2013. I recall earlier versions would return an error message in this situation.