In a post some months ago, there was a discussion of the relative performance of equivalent functions: built-in worksheet versus custom VBA. Dougaj4 reckoned that the VBA function for Transpose is about twice as fast as the WS function. However, in my benchmark, the difference was swamped by Range Access. So I repeated the experiment with an in-memory array, comparing

WorksheetFunction.Transpose(arrin)

with Doug’s double-For-loop implementation. I tried it for a square array of size 1000 to 3000 (so 1 to 9 million elements), with Long values (=i/j).

Results:

- 1000^2: WS = 0.72, VBA = 0.31
- 2000^2: WS = 4.08, VBA = 1.27
- 3000^2: WS = 9.42, VBA = 2.84

Here’s the graph:

So in fact the difference is greater: the custom VBA function is about 3 times faster. I wonder why? The function is pretty simple algorithmically, so it’s not as if there could be wildly different implementations. And there’s no significant initial overhead for the Worksheet function call: its line would pretty much go through (0,0).

I don’t know whether one could expect a similar difference in performance for other functions – say a really meaty statistical function.

### Like this:

Like Loading...

*Related*

Roy, pure speculation on my part but I reckon that the performance hit from using WorksheetFunction may come from the time lag where VBA communicates with Excel and then waits for an answer to come back, as opposed to just transferring data to and from Range objects. In the first case VBA has to call the Excel application, pass it an array, tell it to use the Transpose function on it, then wait for it to return the result. In the second VBA is simply reading from and writing to arrays. Of course that’s all Excel is doing also with Transpose (and I suspect that it would be a fairly fast implementation when it’s executed from within Excel), but the inter-process calls to and from can really slow things down.

Of course I may be completely wrong, please shoot me down if so…

Geoff,

What’s odd is that the benchmark is calling each function *only once*, not X million times; it’s the size of the array that changes. So the difference in times is due to *one instance* of the call overhead.

So whatever is making the WS function call slower is directly proportional to the size of the array. Doesn’t this have to be the array copy between VBA and Excel? If the two algorithms are equivalent in performance, this implies that the per-cell copy overhead is twice the time needed to transpose one cell within the respective functions. Somehow this seems unlikely: I’d guess that the array-copy is done by some low-level memory copy – not that I know anything about this level of programming 🙂

/Roy

One is optimised, one is general.

But the important point is not the why, (although I guess that’s the more interesting point), but its to point out that VBA functions can be faster than worksheet one’s.

Ross,

You mean the Worksheet function’s optimized? Strange that it’s the slower one then. What would such optimization entail?

Maybe I’ll experiment with a different function.

Does 2010 perform any faster than 2007? 2010 outperforms 2007 when charting large amounts of data, and i wondered if this carried across to worksheet functions.