crazy macro idea? rick roth?

[MacroEcon1337]

If you are trying to optimize a heavy workbook, it is hard to know where to begin. This tool would solve that!

For each CELL in ENTIRE WORKBOOK

IF the CELL has a FORMULA in it...
1. Calculate the cell - record the time it takes to calculate
2. Conditionally format the cell based on how long it takes to calculate (Red = longer, green = shorter)
END IF

Next CELL

It seems like this COULD be possible with a few brilliant keystrokes.

MC

 

[PhilS2520]

Hi MacroEcon1337,

You may need to account for the time the code will take to go through all the cells. There are a LOT of cells in a worksheet and a LOT of worksheets in a workbook.

Plus you need to think about cells dependent on other cells for their values to be calculated.

 

[MacroEcon1337]

Hi MacroEcon1337,

You may need to account for the time the code will take to go through all the cells. There are a LOT of cells in a worksheet and a LOT of worksheets in a workbook.

Plus you need to think about cells dependent on other cells for their values to be calculated.

1. I can run application.calculate full in a few seconds. I doubt it would take more than 5 minutes (could be totally wrong)

2. The effect of dependent cells would be captured/embedded in the time it takes to calculate that cell (could be totally wrong)


It should be assumed the copy of the workbook tested will be trashed (as far as future use goes) following completion of the script. So it would be okay to load it up with crazy conditional formatting.

The point is more like forensic. If I want to outsource some calculation load up to VBA - where is the best place to begin?