Which is faster? VBA code or formula in spreadsheet

[SandPounder1]

As I work on a monster spreadsheet I'm putting together, I want to make sure that it does not get bogged down doing calculations and running VBA code.

Some (if not all) of what I am working on can be done either via excel formulas in the spreadsheet or VBA code associated with the worksheet.

Out of curiosity, do any of the excel gurus know which requires less processing time, i.e. which gets the result "faster" with less processing overhead?

Thanks!

[Ron Coderre]

As a general rule, native Excel formulas, because they are sourced in compiled code, are faster than VBA UDF solutions. However, there are techniques available in VBA that sometimes allow you to optimize the Excel workbook's properties during code execution to deliver faster solutions.

[Richard Buttrey]

Hi,

Without exception it will be faster the more you leave in the workbook environment for standard functions to handle.
Only resort to VBA as a last resort. And then if you have big ranges to handle read them into a VBA array(s) so that you handle as much as possible in the VBE. The big overhead occurs with things like loops which involve jumping backwards and forwards between the VBE and the Application in order to read/write data.

[SandPounder1]

Thanks to both of you!

"The big overhead occurs with things like loops which involve jumping backwards and forwards between the VBE and the Application in order to read/write data. "

That makes a lot of sense. I'll keep that in mind as I continue.