Transpose bug in 2013 and 2016

Many of us will be used to using Application.Transpose (or WorksheetFunction.Transpose) when manipulating ranges or arrays in VBA. And, up to and including Excel 2010, we are probably all aware that you will get a run-time error if you pass an array (not range) that is over 65536 ‘rows’.

 

The good news is that this error doesn’t occur in 2013 or 2016. The bad news is that the resulting array is not the size it should be and it gets truncated with no warning at all!

 

Essentially, what happens with an array that is over 65536 rows, the last whole multiple of 65536 rows are simply removed – i.e. you lose (n \ 65000) * 65000 rows.

So if your array is 65537 rows by 1 column, the resulting array will have 1 item. If it’s 85000 rows by 1 column, the result will have 19464 items (85000-65536*1). If you have 147000 rows, the result will have 15928  (being 147000-65536*2) and so on.

 

I’ve reported this but had no feedback as yet.

6 thoughts on “Transpose bug in 2013 and 2016

  1. Alan says:

    Thanks for that interesting warning!.
    Sounds like another reason to shy away if possible form the ( “Worksheet Function” ) Transpose when dealing with Arrays. (So rather do the transpose with a simple loop, ( or in the UserForm way you also have suggested previously ) )

    Maybe the general rule could be that these Functions are optimized for Ranges. Some extra “conversion” is then done by VBA for use with Arrays, and then both speed disadvantage and the size limits come in. And as you brought to our attention, it appears this conversion process is doing something a bit Weird in later XL versions )

    When one uses such “Worksheet Functions”, then if possible one should, i guess, try to avoid using an Array as an Argument.
    Often one sees a used range “captured” to an Array and then used in something like a .Transpose, Match ,.Index.
    I recently found it was better to use the Cells ( Whole Spreadsheet Range) in place of the “Capture” Array. This did away with the size restriction to some extent, as well as being faster.

    I think this simple demo shows what I mean. The code Fills up the first Row of a ( XL 2007 + ) Spreadsheet from a Transpose on a column.
    Line 10 only works on values and returns values, just having value information.
    Line 20 returns the Transposed Range as range Objects, having more info, but appears never the less to be a bit faster.
    The extra time needed for Lne10 comes from VBA doing the “conversion”. Maybe it “pseudo” puts the Array values in a Spreadsheet of the earlier Excel Versions ( up to XL 2003 ). Then transposes that.

    Sub Testspose()
    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets.Item(1) ‘First Tab from Left
    Dim rngT As range: Set rngT = ws.range(“A1”, ws.Cells(1, 16384)) ‘Entire First row, XL 2007 +

    ‘Transpose Range Values through Array
    Dim arrIn() As Variant
    Dim arrT() As Variant

    Let arrIn() = ws.range(“A2”, ws.Cells(16385, 1)).Value ‘ Typical One liner “Capture”
    ‘ Or
    Let arrIn() = (ws.range(“A2”, ws.Cells(16385, 1))) ‘Extra () “Parems” evaluating by / to implicit default value

    Let arrT() = Application.Transpose(arrIn())
    Let rngT.Value = arrT()
    ‘ Or
    10 Let rngT.Value = Application.Transpose((ws.range(“A2”, ws.Cells(16385, 1))))
    ‘ Or
    Let rngT = Application.Transpose((ws.range(“A2”, ws.Cells(16385, 1)))) ‘Relying on Implicit Default usually give same results
    ‘……………

    ‘Transpose Range

    20 Let rngT = Application.Transpose(ws.range(“A2”, ws.Cells(16385, 1)))

    End Sub

  2. Pingback: Anonymous

  3. Deep Dave says:

    That is a very interesting observation! Please share any feedback you receive from Microsoft.. That’s of course if you receive any! 🙂

    Cheers!
    Deep Dave
    NeedForExcel

    • Alan says:

      Maybe They would argue that it is not a Bug just a different way of handling the same Thing, a “changed behaviour”
      For example. The .Find Function, if you look for the first cell , searching forwards from the last cell you will get there with a valid result. But is this a Bug. We might consider it so as I might think I should not be able to find something at the start of the sheet looking forward from the end. We usually find this good to allow us to search using the same code in XL 2003 or XL 2007 +
      It would suggest possibly that using Arrays are still limited in worksheet Functions to the XL 2003 Worksheet size ? I do not know if other Functions are, such as the .Index ? . I mean here, (I do not have above XL 2010 to check ),. …. if I try to transpose 65537 rows, I get 1 column,- Correct? If I have understood correctly I will have here the values in the column that are those from Row number 1, not that from row number 65537

      If Microsoft ever do “repair” it, it would be interesting to have some idea of how they fixed it. But that might give an indication of how Excel works that might be proprietor I guess.

  4. Alan says:

    Having thought about this a little further, I suggest the behaviour is possibly easy to explain. It is not a Bug but exactly the behaviour expected: _..

    _.. Considering the poorer speed performance that I often see when using an Array in a Worksheets Function compared with using a Range, I would suggest that possibly Excel “pseudo” places the Array Member Items into some sort of “pseudo Range Object”, does the operation on that , and then reconverts to an Array of values .
    Outside my knowledge bounds , but suggested to me by advanced programmers is something along the lines of this….” … Excel’s normal “domain” is a set of addresses represented in a rectangle 256 by 65536 …..if they had to handle addresses outside those ranges it would be gruesome….”… This results in the bounds of Worksheet Functions when used for Array Value matrixes still being set to the old pre XL2007 worksheet Limits of 65536 rows. ( I am not sure if the columns maybe possible to be increased in later XL versions). The “pseudo Range Object” could be set to these bounds, or at least to the 65536 row bound.
    I am suggesting that the Array Member Items will be referred to ( for read and write ) through the Range Object Item Property. In general , ( or in most cases) , the upper bound of the Range Object Item Property is above the total number of cells in the Range Object to which it is applied. My experiments have shown, that when the item Number exceeds the total number of cells, then the range “filled” through referral to the Range Item Property, continues to be filled following the rows, then columns convention. Effectively, the final built Range Object will extend “down” effectively stacking “downwards” Range Objects of the original Range size. Eventually we end up with a Range Object of the same column size as the original, but with extended rows. When, following the row , then column convention, we attempt to go down further then the maximum row limit, ( that is to say we reach “bottom right limit” ), then for all worksheet functions, I think they are “refreshed “and operations begin again from “top left”.
    I think the above could explain the “truncation effects” reported here.
    Possibly the earlier XL versions errored as the maximum Range Item Property Index ( Item number ) was limited to the 256 x 65536. This maximum Range Item Property Index ( Item number ) may have been increased for the later versions.
    Alan

Leave a Reply

Your email address will not be published. Required fields are marked *