How do I determine whether calculation was completed, or detect interrupted calculation?

Question

I have a rather large workbook that takes a really long time to calculate. It used to be quite a challenge to get it to calculate all the way, since Excel is so eager to silently abort calculation if you so much as look at it.

To help alleviate the problem, I created some VBA code to initiate the the calculation, which is initiated by a form, and the result is that it is not quite as easy to interrupt the calculation process, but it is still possible. (I can easily do this by clicking the close X on the form, but I imagine there are other ways)

Rather than taking more steps to try and make it harder to interrupt calculation, I'd like to have the code detect whether calculation is complete, so it can notify the user rather than just blindly forging on into the rest of the steps in my code. So far, I can't find any way to do that.

I've seen references to Application.CalculationState, but the value is xlDone after I interrupt calculation, even if I interrupt the calculation after a few seconds (it normally takes around an hour).

I can't think of a way to do this by checking the value of cells, since I don't know which one is calculated last. I see that there is a way to mark cells as "dirty" but I haven't been able to find a way to check the dirtiness of a cell. And I don't know if that's even the right path to take, since I'd likely have to check every cell in every sheet.

The act of interrupting calculation does not raise an error, so my ON ERROR doesn't get triggered.

Is there anything I'm missing? Any ideas?

Any ideas?

Answer 1

I think the trick you need to implement (if you're application runs in Excel 2007 or later) is to handle this with the Application.AfterCalculate event, which is raised after both calculation is complete and there are no outstanding queries.

If you've never worked with events in VBA before, there is a good overview from cpearson.com.