How to interrupt VBA execution through the ribbon UI?

[WernerGg]

I have a little Excel 2007 application with a standard ribbon UI. See the CustomUI.xml and the VBA-code below.

I have uploaded an Excel 2007 TestRibbonUI.xlsm to box.net from where you can get it with the link http://www.box.net/shared/8uznug7s3r

My new tab with name "My Tab" and id="tabCustom" has a group "grpCancel" with two buttons "btnWork" and "btnCancel". They shall be enabled /disabled at runtime, but this is not the issue here. See my other topic http://www.mrexcel.com/forum/showthread.php?t=518628 for that. Here both buttons stay always enabled.

Button "btnCancel" shall interrupt the processing startet from button "btnWork", but unfortunately Excel gives the ribbon UI no chance to process the button action "DoCancel" issued from button "btnCancel" as long as the btnWork-action "DoWork" has not finished.

Not even DoEvents or other calls like Application.Screenupdating interrupt DoWork sufficiently to handle the "btnCancel"-action. Note that when doing the same thing from a classical Cancel-Button on the spreadsheet or a form, there is no problem.

Does anybody know how to interrupt DoWork or execute DoCancel in another thread?

I am afraid that new ribbon UI has a very weak integration with the VBA architecture.

Sorry I am not able to enter the xml-code and not the VBA code within code-tags. The vBulletin software always corrupts the things. Stupid! Please download the workbook from the above link.<!-- / message --><!-- sig -->

 

[WernerGg]

See my other topic http://www.mrexcel.com/forum/showthread.php?t=518628 for that. <!-- / message --><!-- sig -->

This link is wrong. It is "How to preserve or regain the ID of my custom ribbon UI?: http://www.mrexcel.com/forum/showthread.php?t=518629

 

[xenou]

When I hit your Work button I see the status bar increment 1...2...3...4...
Then I hit the cancel button and the status bar stops incrementing immediately.

Is this not what happens on your end?

I am using XL2010 with Windows XP Pro SP3 on a 32 bit system, with two cores enabled.

 

[RoryA]

For 2007, you can use OnTime to run the actual code. This will allow cancelling of the running routine (since it allows the callback to finish).

 

[WernerGg]

When I hit your Work button I see the status bar increment 1...2...3...4...
Then I hit the cancel button and the status bar stops incrementing immediately.

Is this not what happens on your end?

I am using XL2010 with Windows XP Pro SP3 on a 32 bit system, with two cores enabled.

Great! This is exactly what should happen. But it does not happen under XL2007. There the DoCancel-code is not executed before DoWork has ended.

Good to know, that it will work under 2010 at least.

 

[WernerGg]

For 2007, you can use OnTime to run the actual code. This will allow cancelling of the running routine (since it allows the callback to finish).

Well, of course. But this is not the problem. My DoWork is just a realistic simulation for a long running action which shall be interruptable by the user (Complex statistical curve fitting in my case).

Application.onTime would be a different processing. Unlikely that a real work routine would act like that.

 

[RoryA]

I don't see your point? You simply use OnTime to run exactly the same code as you are running now. There is no difference to the processing code itself, merely the way it is started.

 

[WernerGg]

I don't see your point? You simply use OnTime to run exactly the same code as you are running now. There is no difference to the processing code itself, merely the way it is started.

Application.onTime just schedules another routine for a later time Tx. Then terminates immediatly. The caller continues and will normaly end soon after. My DoCancel would then execute of course. Then nothing happens anymore until Tx.

 

[RoryA]

Did you try it? If you use Ontime, you can cancel the routine using the other button. I thought that was the point of the question? (If not, what was?)

 

[WernerGg]

Did you try it? If you use Ontime, you can cancel the routine using the other button. I thought that was the point of the question? (If not, what was?)

Yes, I tried it. It works. But it is not my problem.

Can you write for me the code that uses onTime and does something like:

Sub test_work()
    Dim i As Long, x As Double
    Debug.Print "Start"
    CancelWork = False: IAmWorking = True
    For i = 1 To 20000 '00000
       If CancelWork Then Exit For
       x = Sin(i / 100#)
       Debug.Print i, x
       DoEvents
    Next i
    Debug.Print "Done"
    IAmWorking = False
End Sub