Solved

Interrupt an executing VBA macro, Excel 2010?

Posted on 2015-01-12
7
233 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-SSA
  • 4
  • 3
7 Comments
 
LVL 11

Accepted Solution

by:
jkpieterse earned 500 total points
Comment Utility
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
 

Author Comment

by:Ed-SSA
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Ed-SSA
Comment Utility
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
Comment Utility
See my first post in the thread...
0
 

Author Comment

by:Ed-SSA
Comment Utility
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
Comment Utility
You're welcome! Apologies for confusing you :-)
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
My experience with Windows 10 over a one year period and suggestions for smooth operation
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 …
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now