VBA functions - faster than worksheet formulas?
 

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


--
TheRobsterUK


------------------------------------------------------------------------
TheRobsterUK's Profile: http://www.excelforum.com/member.php...fo&userid=9924
View this thread: http://www.excelforum.com/showthread...hreadid=472454

VBA functions - faster than worksheet formulas?
 

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"
wrote in message
news:TheRobsterUK.1wajic_1128283506.0935@excelforu m-nospam.com...

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


--
TheRobsterUK


------------------------------------------------------------------------
TheRobsterUK's Profile:
http://www.excelforum.com/member.php...fo&userid=9924
View this thread: http://www.excelforum.com/showthread...hreadid=472454

VBA functions - faster than worksheet formulas?
 

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

--
MrShort
-----------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php...fo&userid=2218
View this thread: http://www.excelforum.com/showthread.php?threadid=47245

VBA functions - faster than worksheet formulas?
 

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

"MrShorty" wrote in
message ...

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

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

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


--
MrShorty
------------------------------------------------------------------------
MrShorty's Profile:
http://www.excelforum.com/member.php...o&userid=22181
View this thread: http://www.excelforum.com/showthread...hreadid=472454