A recent post at Roy Maclean’s VBA Blog suggested that the difference between using a worksheetfunction call in VBA and writing a User Defined Function (UDF) to do the same job was not enough to be worth worrying about.  He tested this with a routine that:

• read a range of data from a worksheet as a variant array
• transposed the array (using either worksheetfunction.transpose or a UDF)
• wrote the transposed array back to the spreadsheet
• increased the dimensions of the range by 1 row and 1 column
• repeated until reaching a 255 x 255 range size

Times for running this routine on my machine were:

• worksheetfunction: 11.2 sec
• UDF: 9.9 sec

so a small advantage with the UDF, but hardly worth the trouble; but cutting out some of the extraneous operations gives a different picture:

If the same routine is run with the line to write the transposed array back to the spreadsheet commented out the times are:

• worksheetfunction: 4.8 sec
• UDF: 3.6 sec

If the array data is read from the spreadsheet once, and the varying sized arrays created with a loop in VBA the times are:

• worksheetfunction: 3.8 sec
• UDF: 2.6 sec

And if the full size array is read from the spreadsheet once, and transposed 255 times, the times are:

• worksheetfunction: 7.7 sec
• UDF: 3.6 sec

 Finally if the size of the array is increased to 512×512 the times are:

• worksheetfunction: 34.2 sec
• UDF: 16.2 sec

So overall the results show that the actual transpose operation is a little better than twice as fast in the UDF, compared with using the worksheetfunction call.  Whether this is a worthwhile saving depends on the rest of the routine (and minimising the number of data transfers between VBA and the worksheet is probably the best place to look for time saving), but if a routine does a large number of transpose or other array operations on large arrays there may well be a significant saving by using a VBA UDF rather than using worksheetfunction calls.