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

[MrShorty]

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?

[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

"MrShorty" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>