Normally, Excel will recalculate a cell or a range of cells when that cell's or range's precedents have changed.This may cause your workbook to recalculate too often, which will slow down performance.
If it tries to calculate a cell and finds that it is dependent on some other cell lower down the chain, it rearranges the chain to move that cell downwards.
As an exception to the above rule though, and if you are using version prior to Excel 2007, if the number of dependencies go beyond 65536, Excel will shift onto the ‘brute’ mode where it will force recalculate all cells.
Depending on the structure of your VBA project, you may be given the option to debug the code, (see below).
In this case, clicking on the Debug button on the debug message box, causes the line of code that generated the VBA error to be highlighted in your vba editor.
If you're going to work with another application, such as Word, declare your OLE object directly, rather than as an Object type variable.
By doing so, a great deal of overhead is done at compile time ("Early Binding") rather than at run time ("Late Binding").
You can then link all those cells which need to use the formula, to that single cell.
So look at your spreadsheet, if you have a vlookup() that gets repeated over a 1000 cells and essentially all of them return the same value, take it out and put it in a single cell.
You can prevent Excel from recalculating the workbook by using the statement: An individual item of a collection object may be accessed by either its name or by its index into the collection.