VBA functions - faster than worksheet formulas?

[TheRobsterUK]

Hi,

If I have a large formula in a worksheet and I create a VBA function
which replicates this (say I call it MyFunction), would this run faster
than the worksheet formula?

The function would still rely on numbers within the worksheet, but
rather than using a formula to carry out calculations it would use a
function which replicates what the formula is.

Would this improve the calculation time of the spreadsheet? If so would
it be a significant impovement?

Thanks
-Rob

 

[Charles Williams]

Almost certainly not.
VBA functions have overheads in:
- initialisation (overhead per function used, you can minimise this by
closing the vbe and calling calculate from VBA)
- transferring data from excel to the function and returning it. (sometimes
you can minimise this by referring tp objects rather than retrieveing an
entire range, or by getting the complete range into a vrainat in one go
rather than 'walking' across the cells)

Generally speaking VBA will always be slower than anything you can do in
native excel unless you implement a superior method in VBA.

Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

"TheRobsterUK" <[email protected]>
wrote in message
news:[email protected]...

 

[MrShorty]

General concensus is that UDF's are slower in terms of calculation tim
than native Excel functions. My observation (on a 450 MHz machine) i
that the longer calculation time is imperceptible. At 100 MHz, it wa
a perceptible difference, but was never significant.

If this is a one time thing, and you'ave already got the spreadshee
built and debugged, it's not to your advantage to re-do the formula a
a VBA function.

As an open question to the group: Does anyone have an estimate of ho
much longer it takes for Excel to calculate a UDF versus using nativ
functions for the same calculation

 

[Charles Williams]

Its very difficult to answer your question: the timing difference is highly
dependent on:

- what kind of function you are creating
- how you code the VBA UDF
- how calculation is initiated
- the state of the VBE environment

but in general a VBA UDF will be slower than a native excel function unless
the VBA UDF implements an algorithm/method which is superior to the native
function.

Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com