Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Interrupt an executing VBA macro, Excel 2010?

Posted on 2015-01-12
7
Medium Priority
?
357 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 2000 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Gain an elementary understanding of Blockchain technology.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

618 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