I’m have a program (GUI) that interfaces with excel to execute macros. We’re using Microsoft.Office.Interop.Excel to call/run the macros and this works great.
What I can’t figure out is a good way to cancel the macros from the GUI.
One idea we had was to use the excel.application variable that runs the macros to write a “stop” value to a specific cell in the workbook, and in the macros (they are all mostly loops), check for the “stop” value in that cell.
This crashes my GUI with this
The program '2188 BVLReports.vshost.exe: Managed' has exited with code -336589910 (0xebf00baa).
And excel gets tied up, and won’t respond. I know how to
Exit Sub from within the macro if the “stop” value exists, so I don’t need answers on how to check/cancel from inside the macro. Any ideas on a better way to write this “stop” value or a better way to cancel the macros externally?
This might be a case for the mysterious Excel Application.CheckAbort feature?
According to Help Application.Checkabort is supposed to stop recalculation except for a specified range. It does not do this. Apparently it throws a runtime error if there are pending abort messages in the app message queue (mouse clicks, esc key down etc). The parameter specifies whether to eat the message or leave it in the queue. The objective is to allow a a long-running VBA calculation to be interrupted in the same way as an Excel calculation.
Maybe you can use the SendKeys function to send keyboard output to excel and kill it that way.
Hope this helps.