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.

Bucky

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.

Bucky

So what's the workaround? Create the 2nd array manually and loop through to populate? – Elstan

@Elstan That's the only solution of which I am aware if you are using a datatype other than string, double or Boolean in the primary array. You could store dates as doubles, though, and the values won't be changed on transposition. – Bucky

If initial array is being populated from a date range the you can try Range.Value2 instead of Range.Value. It preserves underlying value. – Beaded

@Beaded Yes, as I wrote, that would store the dates as doubles. – Bucky

@VBasic2008 did you see results that don't match up with what I posted? – Bucky

Answer 2

You could use a ListBox in memory for transposing without the usage of Transpose and the DataTypes stay intact:

Option Explicit
Option Base 1
Sub Tester2()
    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
   
    With CreateObject("New:{8BD21D20-EC42-11CE-9E0D-00AA006002F3}")
    'Listbox always Base 0
        .List = v
         w = .List 'Now 2-dim with correct typenames, use .column for transpose Row-column)
    End With

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

End Sub

Brownstone

Thanks, but my reason for posting this was to point out the problem I came across, so others would be aware of it. There are any number of workarounds -- but if you don't know it might happen, you won't employ them and wind up having to deal with seemingly inexplicable errors. I had not thought of the ListBox method, but it seems to work. However, on a data sample of 100,000 dates, the ListBox method takes 3-4x longer than the simple loop. (It also takes longer with the original data sample). – Bucky