MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

How to stop the VBA execution?

  Asked By: Ayden    Date: Sep 03    Category: MS Office    Views: 14180

I have a simple macro (or is that an application?) that reads data
from the RS-232 port and puts it in a spreadsheet, so we can see it.
This is a real-time program, so we can see the numbers change, as our
machine moves.

The problem: In order to stop execution, I have to press <esc>, then
3 times the tab <key> and then <enter>.

Is there a way, where the program can read the keyboard and get input
data from there ? I tried functions like inkey$ key$ kbinp and so on:
no luck.

The mouse seems to be locked up during this program's execution. Is
there a way to prevent that, and use the mouse to click a stop button?



8 Answers Found

Answer #1    Answered By: Dalpat Student     Answered On: Sep 03

Maybe something like a command button
with just one statement .. "End"

Answer #2    Answered By: Priscilla Lewis     Answered On: Sep 03

You cant use the keyboard  when running Excel VBA. It will screw
things up.

You could use sendkey functions, but there are tons of ways of
stopping execution  anyway.

Why do what to stop  it? What is it doing when you want to stop it?
Is it running a BOF EOF? A loop? Does it go into error mode at the

Answer #3    Answered By: Delbert Cooper     Answered On: Sep 03

press  a start button  to run the program  (a macro). Then it runs
fine. After 5 minutes, I might decide to stop  this 'display data
program'. I cannot click  on anything since my mouse  pointer appears
busy. The only way out is escape and wait or ctrl-alt-del and wait.
The program is able to listen to COM1. When I send a shut-down command
through the RS-232 port, I can shut the program down, and work with
this Excel sheet like normal, perfect. But I want to be able to shut
it down without something plugged into COM1.

Answer #4    Answered By: Myron James     Answered On: Sep 03

Having a little trouble getting my head around this. First, reading through
the thread, if using the keyboard  while running a macro  'screws things up',
there's something else awry.

The standard (and cleanest) way to interrupt VBA macro execution  is hit Ctrl
& Break, repeatedly if necessary. A macro in this condition indicates that
it isn't releasing to the system often enough for other tasks to be polled.
DoEvents statements are supposed to allow your keystrokes to be serviced by
the OS. I think a key indicator of what you're facing, though, has to do
with the fact that you're talking to a serial port. These are tricky and I
note that a common issue with VB(A) and serial ports is that the COM port
controls hardly ever have decent handling of keyboard interrupts for any
purpose beyond injecting data in the stream to the port.

Take a look at getting a better control for handling your serial sessions.

Answer #5    Answered By: Vidisha Pathak     Answered On: Sep 03

That doesn't work: during execution  of the program, the mouse  pointer
looks busy, and I cannot click  on anything.

Answer #6    Answered By: Barney Smith     Answered On: Sep 03

I am not a true VBA expert (and may explain poorly), but I do some
pretty sophisticated serial I/O and I believe I have seen three
methods for setting up VBA serial I/O. Which method do you use?

1- MScomm - Haven't tried as it may only be part of VB (not A)

2- Structs used by Win32 comm api kernel API file i/o functions  -
such as:
CreateFile, CloseHandle, WriteFile, ReadFile Libs all calls to, I
believe, the "kernel32" DLL and calls like: OpenComPort, ReadComPort,
WriteComPort, CloseComPort.

3- Shell (Environ("comspec") + " /c mode " + ComP$ + ":" + baud$
+ ",n,8,2"). This uses standard Basic file handling commands such
Open ComP$ For Output As 1
Print #1, c$ + ";";
Close 1

4- I suspect there are other .DLLs as I know there are some NT
parallel port  workaround .DLLs

Some of this depends on which OS you are running under and I can not
answer questions except that I know that NT prevents DoEvents from
doing what it is supposed to "Do" and I was unable to use any of the
methods I could find/understand with complete success (under NT).
There is a Key press  event ( I forget the name) which can fire on a
specified or random keyboard  event, but I was unable to get it to
work (possibily out of ignorance).

I use method #2. Then I have a timer in the read  routine. It times
out should no data arrive in a specified time (this comes with the
Kernal call. I use this in two ways.
1- Set with a long time (3 sec), it will give up if nothing is
received and show a Message Box with the reason and location in the
code (for debugging). This is when a specified amount of data is
expected, in response to a aerial transmission data request that
Excel sends. FYI, I can also detect if the remote device is connected
(a hardware thing) or if is connected, but not responding (a command
equivalent to a 'ping' to the remote device).
When I wish to monitor the port for randomly occuring data (from the
serial device), I set it to a short time and manage this in the code
without any user notification. I basicly receive one byte at a time,
then allow other activity (read on). My bites are all in a
relatively short, defined length packet, however these strings can
occur often or never. In this routine, the time out does not create
any user invention, but only allows other routines as well as the
interruption of the process---which, in NT, is still seriously
crippled by the NT DoEvents restrictions.
On XP all is well using the same code/sheet/macros. I simply have a
buttom on the spreadsheet  which is pressed to halt looking for data -
its status is checkes each time the timer times out. On NT it can
tak MANY clicks to affect an interrupt of the Sub.

Answer #7    Answered By: Bu Nguyen     Answered On: Sep 03

Here is a suggestion. You can make a UserForm the floats over Excel
while the application  is running. You must set the form's ShowModal
property to False at design time. Add CommandButton1 to the form and
lable it "Stop". Place a button  on the worksheet labled "Start" and
have it run the Demo macro  below. I hope this method is compatible
with your RS-232 driver.

Answer #8    Answered By: Alonzo Roberts     Answered On: Sep 03

Now I know how to make non-modals (hadn't tried
and forgot about this idea). Perhaps this will help with my NT
restrictions problem, but I st that ineffective DoEvents in your

Didn't find what you were looking for? Find more on How to stop the VBA execution? Or get search suggestion and latest updates.