Solved

Interrupt an executing VBA macro, Excel 2010?

Posted on 2015-01-12
7
295 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 3

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
Manage your data center from practically anywhere

The KN8164V features HD resolution of 1920 x 1200, FIPS 140-2 with level 1 security standards and virtual media transmissions at twice the speed. Built for reliability, the KN series provides local console and remote over IP access, ensuring 24/7 availability to all servers.

 
LVL 3

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 3

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

730 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