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.

### Like this:

Like Loading...

*Related*

Roy – I’ll post more details later, but it is the other parts of the function that take all the time, specifically reading the data from the spreadsheet and writing it back. The actual transpose function is about twice as fast using the VBA as using worksheetfunction with Excel 2007. Worksheetfunction is quicker in earlier versions, but still generally significantly slower than VBA in my experience. Also the matrix functions pre 2007 are very limited in the maximum size they will handle, as you discovered.

This only applies to working in VBA by the way. On the spreadsheet a built in function will normally be way quicker than a UDF that does the same thing.

Roy – I have given details of my timings on the worksheetfunction vs UDF question on my blog:

http://newtonexcelbach.wordpress.com/2009/04/22/more-on-worksheetfunction-vs-udf/

In summary, the UDF is about twice as fast as the worksheetfunction at doing the actual transpose, but if there are a large number of operations reading and writing between the spreadsheet and VBA these take much longer than the transposing, so the saving on the transpose operation is not so significant.

Thanks, Doug. I have to admit that handling large arrays, and any attendant performance issues, is not something I deal with normally, although clearly there are people who do. As you say, it’s probably the worksheet–VBA transfer of data that’s significant. If people really do start using (in xl2007) 1 million row * 10K column worksheets then I can see this being relevant.