Find out if a cell is dirty/needs calculation

Hello all,

does anybody know if there is a way to check if a Range needs to be recalculated?
The image attached is a simplified version of the workbook I have. Cell D7, D8 contains a matrix function I wrote in VBA. It is dependent on the cells B7, B8, B9, ..., B8198. These cells however depend on cell B3.
When I change the cell B3 the following happens. Cell B7 is recalculated. Because of that my function in D7 is recalculated. Then B8, my function in D7 again, B9, D7, B10, D7, ...
My problem is, that my function in D7 needs about ~1s of calculation time...
It would be much faster if the calculation in D7 started after all cells in column B are updated. If there was a way to find out if the cells in column B aren't updated it would be easy to cancel calculation of D7 in that case.

dependent cells.jpg

With kind regards,
Eric

Thanks for your replies.

Doing the calculations of column B in VBA is not as comfortable as I originally wanted. But it definitely works.
When there is no way to check the dirtiness of a cell I think I can live with that. Thanks.

Sorry, ~1 s for the function call was just my feeling. After measuring it seems to be around 50 ms. But for about 8000 cells that still makes >5 min. It's a FFT implementation and I don't think I can get it much faster.

you could do a trick, Read the range that you want to check for dirty cells into an array (don't perform this on the whole sheet or you might run out of time!)
then calculate the sheet, and then compare the re-calculated sheet against the values in the array. The changed values were dirty before the calculation.

Try this

Please Login or Register  to view this content.