Excel functions in VBA?

[21TonyK]

I know how to do it, just not sure if I should.

Currently pumping functions into .formula rather than the result of the function in VBA into .value

Is there any real advantage? Possibly in the speed of the process (bearing in mind its several hundreds at a time).

[nickd01]

If I understand what you're saying - then for readability / maintanence / debugging etc I'd not be using VBA to write formulas on to the worksheet.

I'd encapsulate all the logic into a self-contained VBA function and if needed leave any formulas in cells on the sheet.

Any VBA functions will be slower as it's only single-threaded, whereas the in-built Excel functions are multi-threaded.

[21TonyK]

nickd01 said:

If I understand what you're saying - then for readability / maintanence / debugging etc I'd not be using VBA to write formulas on to the worksheet.

I'd encapsulate all the logic into a self-contained VBA function and if needed leave any formulas in cells on the sheet.

Any VBA functions will be slower as it's only single-threaded, whereas the in-built Excel functions are multi-threaded.

Thanks, I'll stick with them on the sheet although this does get deleted once the values are calculated. More concerned about speed as this could end up running over MS Onedrive and that can be a bit painful at best!