General VBA question

[phor89]

I am an intermediate/advanced user of Excel but have never tried to learn VBA. All the work I do is very calculation and chart heavy, working with time-series and statistics etc.

A typical worksheet might include several columns of price data, from which there are several other columns of formulas deriving further values from those original prices. Formulas are often logic based e.g. IF, LOOKUPS, INDEX, MATCH and calculation based e.g. AVERAGE, SUMIF etc...

Obviously with tens or even hundreds of thousands of these cells, calculations can take a long time. My question is, would using VBA speed up the calculation time? E.g. coding the same calculations through VBA to output the same values in the same cells?

Please correct me if I am barking up the wrong tree here. Perhaps there are other ways to speed up the calculations, or I am missing the point of using VBA entirely.

 

[sykes]

Hi
I'm not a VBA guru, by any means, and hopefully, some of the other Top Neddies will give you their thoughts on this, but...

I find that by using VBA, you have a lot more flexibility over what you can do with your data, rather than by using fairly fixed formulae. For example, when you run some code, you can be selective over which cells, upon which worksheets, calculations are made, so - in your case- being selective about calculations, COULD save quite a bit of time.
Also, with code, you can temporarily switch calculation mode off, then on again - meaning that tasks can be carried out without the processor and/or memory getting bogged down with unnecessary calculations.
By the same token, you can also temporarily switch off screen updating - once again, saving machine time, and with it, allowing more machine time for making your calculations, rather than unnecessarily displaying a lot of changes to the user.

I'm guessing that it would definitely be worth a trial, at least - to test a coded approach versus a formulae-based one, on perhaps just part of your data - to see how you get on with it.
I'll bet that once you use code, you'll see some real benefits - and not just with regard to speed...