In a previous post, I mentioned experimenting with the Transpose worksheet function. Dougaj4 clarified its usage, and mentioned that it was “really slow” compared with a custom VBA function (his comment included a link to his own blog, and a downloadable workbook containing such a function). So I thought I’d check this out.
I’ve written a sub that successively transposes square arrays: 2 x 2, 3 x 3, 4 x 4, …, 256 x 256. The reason for stopping at 256 is because I initially tried this in Excel 2000 (of which more shortly). Here it is:
Sub TestTranspose() Dim harr As Variant Dim varr As Variant 'value arrays Dim ro As Integer Dim co As Integer 'row, column offsets Dim hrng As Range 'source range Dim vrng As Range 'target range Debug.Print Time ro = 1 co = 1 Dim i As Integer For i = 1 To 255 Set hrng = Range(Range("Hstart"), _ Range("Hstart").Offset(ro, co)) Set vrng = Range(Range("Vstart"), _ Range("Vstart").Offset(ro, co)) harr = hrng.Value 'Comment out one of the following varr = DougTranspose(harr) varr = WorksheetFunction.Transpose(harr) vrng.Value = varr ro = ro + 1 co = co + 1 Next Debug.Print Time End Sub
The source ranges contain row numbers (h for horizontal), the target ranges transpose these (v for vertical).
In Excel 2000 this worked until iteration 74, when it threw Run Time Error 13: Type Mismatch. Seriously weird! Anyway it seems to work Ok in Excel 2007. And the results are … (sound of envelope being opened) …
- WorksheetFunction: 32, 33, 32, 32 seconds
- Doug’s VBA function: 25, 30, 31, 30 seconds
(times just to nearest second, but you get the overall idea).
So, remarkably similar. I thought the WS function would get it; Doug thought his would; end-result, pretty much the same. Now, it could be that the time taken for the other operations in the test procedure (Range and Offset) swamp the time taken for the transpose, but somehow I doubt it. Anyway, there doesn’t seem to be a prima facie case against using the worksheet function.