8

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.

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60

2 Answers2

6

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.
Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
  • So what's the workaround? Create the 2nd array manually and loop through to populate? – jamheadart Jun 25 '20 at 11:23
  • 2
    @jamheadart 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. – Ron Rosenfeld Jun 25 '20 at 11:34
  • 1
    If initial array is being populated from a date range the you can try `Range.Value2` instead of `Range.Value`. It preserves underlying value. – shrivallabha.redij Jun 25 '20 at 12:23
  • @shrivallabha.redij Yes, as I wrote, that would store the dates as doubles. – Ron Rosenfeld Jun 25 '20 at 12:43
  • @VBasic2008 did you see results that don't match up with what I posted? – Ron Rosenfeld Jun 25 '20 at 14:17
0

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
EvR
  • 3,418
  • 2
  • 13
  • 23
  • 2
    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). – Ron Rosenfeld Jun 25 '20 at 18:05