Solved

Interrupt an executing VBA macro, Excel 2010?

Posted on 2015-01-12
7
278 Views
Last Modified: 2015-01-16
Is there a good, better or best strategy for pausing or stopping an Excel VBA macro?

I currently (in order):
(1) create a Bernoulli binary distribution table for any reasonable size N
(2) I can use keyboard macros to adjust p (probability) up or down in [0..1] in small increments.
     I can also execute the macros with buttons.
(3) for a given N, vary p which then updates the charts.

As the macro is executed, two (or three) charts are continuously updated, creating a sort of "Bernoulli - the movie" distribution sequence as p, the probability increments or decrements.

I'd like to stay with buttons to [STOP], [PAUSE/PLAY], [FAST FORWARD] and REVERSE direction. I can execute the KB macro from a button, but how can I interrupt (pause) "play" and then either reverse motion or continue in fast forward mode? The play is continuous and cyclic, and I use my hands to record screen shots of the charts activity.

I prefer to stay with buttons as they can mimic controls familiar to most users. On the other hand, any hands-free suggestion to achieve the desired result will be greatly appreciated.

TIA, Ed
0
Comment
Question by:Ed Covney
  • 4
  • 3
7 Comments
 
LVL 11

Accepted Solution

by:
jkpieterse earned 500 total points
ID: 40546134
What I did with a progress bar form is to put up a Stop button on the form.
The Stop button sets a vaiable to True which I test against in the main loops of the routine that displays the progress form:

DoEvents
If gbSTop Then
'User pressed stop; act accordingly
End If

The DoEvents is there to enable the click on the stop button.
0
 
LVL 2

Author Comment

by:Ed Covney
ID: 40549426
JK -

I didn't know VBA had a progress bar. Are you using 3rd party tools or am I going blind?

Thanks -

Ed
0
 
LVL 11

Expert Comment

by:jkpieterse
ID: 40550768
It doesn't but you can very easily create one.
- Place two label controls on top of each other
- One gets a border, the other doesn't
- One gets a white fill, the other one (on top of the white one) a dark fill, like blue
- The dark label starts with a length of zero. This length is increased to mimick the progress.
- Both labels have the same Caption.
0
Create the perfect environment for any meeting

You might have a modern environment with all sorts of high-tech equipment, but what makes it worthwhile is how you seamlessly bring together the presentation with audio, video and lighting. The ATEN Control System provides integrated control and system automation.

 
LVL 2

Author Comment

by:Ed Covney
ID: 40551819
OK - Now I see what you're doing but I don't see how it can pause or halt an ongoing macro execution. I see that the progress can be reported, but if I want to stop the macro when only 35% complete, I can't see how this would help.

Thanks in any event,

Ed
0
 
LVL 11

Expert Comment

by:jkpieterse
ID: 40551830
See my first post in the thread...
0
 
LVL 2

Author Comment

by:Ed Covney
ID: 40552379
You sort of confused me with the progress bar reference, but now that I tested your:

DoEvents
If gbSTop Then
'User pressed stop; act accordingly
End If

All is great. Thank you so very, very much !!!
- Ed
0
 
LVL 11

Expert Comment

by:jkpieterse
ID: 40553052
You're welcome! Apologies for confusing you :-)
0

Featured Post

Create the perfect environment for any meeting

You might have a modern environment with all sorts of high-tech equipment, but what makes it worthwhile is how you seamlessly bring together the presentation with audio, video and lighting. The ATEN Control System provides integrated control and system automation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question