It is (fairly) well known that when using Worksheetfunction.Transpose on an array in VBA the array must be no more than 65,536 (2^16) rows long, or it will return an error.
Recently when using Excel 2016 (32 bit) I thought this limitation had been fixed; I was able to transpose arrays with up to the maximum number of rows allowed by Excel (1,048,576 0r 16 * 2^16), without generating any error. Then I checked the end of the returned arrays, and discovered that they had in fact been truncated at 65,536 rows. A closer look revealed that the truncation is in fact done in blocks of 2^16 rows, so if you transpose an array of 65,537 rows the operation will delete all but 1 of them, with no indication that anything unusual has happened.
To investigate what is happening I wrote 7 functions to split an array into single columns, then transpose them, the intent being to generate 1D arrays, which are much faster when transferring large data sets to other programs. The variations were:
- Splitarray: Split a 3 column array into 3 single columns, with no transpose.
- Splitarray-a: Split and transpose in a single operation.
- Splitarray-b: Redim the output arrays first, then split and transpose in a single operation.
- Splitarray-c: Redim the output arrays first, then split into three variant arrays, then transpose.
- Splitarray-d: Transpose without splitting, creating a 3 row array.
- Splitarray-e: Split into single columns, then copy to 1D arrays with a loop.
- Splitarray-f: Convert the input range to a variant array, then split and transpose with a loop.
The functions were modified to return the following information:
- Row 1: Run time
- Rows 2-4: The number of values in the returned arrays, or in the case of Splitarray-d the number of rows, then the number of columns.
In the case of Splitarray-c and Splitarray-f, which had a two stage operation, two columns were returned, with times and array sizes at the end of each step.
The results for Excel 2016 are shown below for four different array sizes:
65,536 rows; all functions return all the input data
1,048,576 rows: the first function (which did not transpose) and the last two (which used loops) return all the data, but all the others truncate the output to 65,536 values.
65,537 rows: The second to fifth functions have all truncated the data to just one value.
1,048577 rows: Now all the functions return #VALUE! errors (as in previous versions of Excel), because the specified range size exceeds the maximum number of rows. Note that VBA arrays can have a much greater number of rows (limited by available memory), but if you are reading data from the spreadsheet the range limit on number of rows applies.
Comparing with Excel 2010; results for 65536 rows are the same:
With 1 additional row up to 1,048, 576 rows, all the functions that use Transpose return #VALUE! errors:
And with 1 or more rows past the range limit all the functions return errors:
It was my recollection that Excel 2013 worked the same as 2010, but checking with 65,537 rows I found that the arrays were truncated to one value, when Transpose was used, as for Excel 2016.
So the conclusions are:
- In Excel 2013 and 2016 (32 bit) using Worksheetfunction.Transpose in any VBA routine will truncate the array without warning if it has more than 65536 rows, so if you must use this function be sure to check the size of the array first.
- But really, there is no good reason for using it at all. It can be replaced by a short VBA routine using loops, which will return the full array up to 1,048,576 rows (or larger if working entirely in VBA), and is actually faster than the Transpose version (at least for arrays of the size used in these tests).