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
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).
- 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.