Solved

Interrupt an executing VBA macro, Excel 2010?

Posted on 2015-01-12
7
240 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
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
 

Author Comment

by:Ed-SSA
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:Ed-SSA
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
 

Author Comment

by:Ed-SSA
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Copy worksheets to new workbook without formulas referencing the old workbook 8 74
MS Word Formatting 3 47
TT Auto Dashboard 13 80
In search of x17-22375.exe 2 10
Today companies are subjected to more-and-more data, and it won't stop any time soon.  But there are obvious opportunities for reducing data, particularly data duplicated among companies.
A high-level exploration of how our ever-increasing access to information has changed the way we do our jobs.
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

919 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

12 Experts available now in Live!

Get 1:1 Help Now