Excel/writing formulas using code
I have a workbook laden with formulas. I believe that there are so many of them it is slowing down the calculation speed of the workbook. I have two questions:
1. would converting the formulas to VBA code speed up the overall calculation time of the file
2. if so would each formula have to be, individually,rewritten in code?
thanks in advance
It depends on the specifics. If you mean you will removed many formulas and then put their functionality in a big macro/VBA code that will be run manually when the data needs to be updated, then having less work that needs to be done on each calculate will speed things up. If the formulas removed were not the source of the slowdown (all formulas are not created equally in terms of time to recalculate), then you would see little benefit. But if they were big formulas with a lot of recalculation time, then you would see a big payoff.
If the formulas need to be recalculated whenever a value is changed, then they probably should be formulas. But if they only need to be updated occasionally, then in a problem workbook, they probably would benefit by being converted to VBA code which is manually run only when needed.
If you are talking about building formulas in VBA that are used in worksheets as user defined functions, e.g. =Myfunction(argument) , then I doubt those would help and probably would take more time.
These are general answers with no knowledge of your workbook.
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here