Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Interesting problem when I run a vba process

  Asked By: Lamberta    Date: Nov 02    Category: MS Office    Views: 1333
  

I have a fairly extensive set of processes that create graphs for data
ranges and other criteria specified in an excel worksheet. When I run
the code it works fine but afterward vba hangs up entirely, including
user defined functions, only to correct itself after a few minutes.
The code has clearly stopped running because the windows refresh (the
last line of my code) is reactivated.

This isn't a major issue but I thought someone might have an idea on
how to get everything to reset more quickly. (I tried reset and
calculate and it didn't work

Share: 

 

7 Answers Found

 
Answer #1    Answered By: Phyllis Foster     Answered On: Nov 02

I'm no expert, but I do know that NT is very hostile to some of the
VBA things like DoEvents. Perhaps it's an OS thing...

 
Answer #2    Answered By: Levi Wilson     Answered On: Nov 02

. I know maybe I shouldn't but I use DoEvents a lot. What do
you mean by hostile please?

 
Answer #3    Answered By: Brock Smith     Answered On: Nov 02

From what I have experienced, NT prevents DoEvents from "Doing" so to
speak. It as if NT doesn't want to let you have the ability to trap
events __when a Sub is running__. Like "You have a Sub executing and
I don't want another one started until it finishes due to its own
code."
I have a sheet which does serial I/O and I had problems
recognizing events with DoEvents. I would be running  a Sub which was
polling the serial port and collecting data, if it was present. This
Sub was entered from a CheckBox on the sheet.
When the data stopped (or I just wanted to stop polling) I needed
to halt this Sub with an 'abort' button push on the sheet...let it
collect data, then stop when the user wanted to. The 'abort button'
fired a Sub which reset  the value of the CheckBox that started the
polling Sub and I think I had a WHILE LOOP watching the condition of
the CheckBox once the Sub was running.
With a DoEvents in the polling loop in that sub, I couldn't
recognize my 'abort' button push except by continuing to click the
button, then after MANY ckicks, it would jump out of the polling
Sub. The 'abort' button reset the value of the CheckBox which
entered that Sub.
This same sheet on an XP machine worked flawlesly. Come to think
of it, trying to "Un-Check" the CheckBox to exit the polling Sub
operated the same (Except, if you kept clicking, you'd overshoot and
re-start the polling...high argggg factor). I sprinkled DoEvents
all over the polling Sub to little avail and it looked pretty silly,
but I was desperate.

On the NT machine I wound up putting this to get out of the Sub with
only ocassionally needing to push more than once.
For ii= 1 to 300
DoEvents
NEXT
The bigger I made the 300, the better. I may be imagining, but it
sometimes seems to capture the abort button better if I hold it down
for a little before releasing... ya get punchy after a while, ya
know.
It also doesn't seem to slow things down with large values for the
300 implying that NT inhibits the "Do" in DoEvents!.

It's as if NT inhibits the event trapping ability of DoEvents. Kind
of a DontDoEvents override... DoEventsEnable = False.

 
Answer #4    Answered By: Sheri Porter     Answered On: Nov 02

You may be misinterpretting what DoEvents is supposed to do. DoEvents is a
release from the application to the operating system. The behavior you note
is characteristic of a pre-emptively multitasking operating system. Saying
DoEvents was you telling the operating system, "I'll hold what I'm doing so
you can catch up with whatever else is going on." DoEvents is also available
in standard VB for the same purpose.

There's an explanation much better than I can provide at
support.microsoft.com/default.aspx

Interestingly enough, they use an I/O function as an example! But if you
read through the whole thing you'll see lots of opportunities for
deadlocking, etc. In the end, they used an approach similar to your
solutions to cleanly handle events.

That would explain why your button wasn't responsive and your serial port
was still being held open for the stream because your code  was also calling
out to the OS to do its thing with serial I/O. The OS had the port and was
watching interrupts everytime you did the DoEvents.

And, as you peppered more DoEvents calls into your code, you may have
experienced some of the following:
"• Using too many nested DoEvents statements may deplete the stack space
and therefore generate an "Out of Stack Space" error message. This error is
referring to the application stack space allocated to the Microsoft Excel
application.
• Make sure the procedure that has given up control with DoEvents is not
executed again from a different part of your code before the first DoEvents
call returns; this can cause unpredictable results.
• Once DoEvents relinquishes control to the operating system, it is not
possible to determine when Microsoft Excel will resume the control. After
the operating system obtains control of the processor, it will process  all
pending events that are currently in the message queue (such as mouse clicks
and keystrokes). This may be unsuitable for some real- time data acquisition
applications."

I'm sure that behavior got better as the OS matured into 2k and XP and
processors got faster(it has on my systems). And your explanation is a good
one for explaining why DoEvents can have unexpected results, though!

Also note that Office apps have a particular hindrance in place as you work
with them. Their rendering engines are a little taxed from having to also
redraw using elements of the printing subsystem. Complex charting, tables in
Word, etc., can bring even the most robust machines to an apparent halt. For
example (and Excel doesn't seem to have this issue, thank goodness), Word
tables of more than a few thousand rows can take an hour to redraw after
adding a single row...and this is on a 1.1 Gig system!

In the orginal problem, I was curious as to whether the
Application.ScreenUpdating toggle has been used. With this, you can greatly
improve performance of the VBA app and only update the screen when you're
ready.

 
Answer #5    Answered By: Ibadah Younis     Answered On: Nov 02

> You may be misinterpretting what DoEvents is supposed to do.
DoEvents is a
> release from the application to the operating system.

This is in line  with my understanding. I'll read and digest, but
a quick scan of your explanation suggests that perhaps closing the
port within the polling loop will free up the OS to catch the mouse
event. I don't remember if I do this or not....stay tuned.

> The behavior you note
> is characteristic of a pre-emptively multitasking operating system.

Whatever...


> ...telling the operating system, "I'll hold what I'm doing so
> you can catch up with whatever else is going on."

This alligns with my understanding.



> There's an explanation ...support.microsoft...118468

Copied and will read.


> ...In the end, they used an approach similar to your
> solutions to cleanly handle events.

Ready and waiting to see that...


> experienced some of the following:
> "• Using too many nested DoEvents ...

No errors, just behavior as described.


> • Make sure the procedure that has given up control with
DoEvents is not executed again from a different part of your code
before the first DoEvents call returns; this can cause unpredictable
results.

Will examine, but don't think this is possible w/my code. THe port
open/close sounds like a good clue.


> • Once DoEvents relinquishes control to the operating system,
it is not possible to determine when Microsoft Excel will resume the
control. After the operating system obtains control of the processor,
it will process  all pending events

AS far as I know there should be only the mouse event (from my
code) , but if the system has other stuff running, that could mess
thins up.


> In the orginal problem, I was curious as to whether the
> Application.ScreenUpdating toggle has been used. With this, you can
greatly improve performance of the VBA app and only update the screen
when you're ready.

I am aware of the ScreenUpdating switch, however, the purpose of
this sub is specifically to place this data on the screen for the
user to see. It is data from a short wave (Ham) transceiver. As the
radio is tuned, it outputs the operating frequency and I catch it to
display on the sheet.

I'll digest the usoft support page. I frequent both this and the
MSDN.

 
Answer #6    Answered By: Raymond Fischer     Answered On: Nov 02

The usoft.support info didn't shed any light on my situation.

Attempt #1:
Put the port OPEN and CLOSE inside the polling loop, so that the OS
isn't hampered in its "Doing" by my holding open teh port port.
Well... it still won't service the mouse click to abort the polling
and now... I have a problem  because closing the port will no longer
capture serial data and when re-opening it, I get partial (a.k.a.
missed) messages.
The 300 DoEvents in a tight FOR:NEXT loop looks pretty servicable
right now.

 
Answer #7    Answered By: Sebastian Anderson     Answered On: Nov 02

I'm running  this on an XP machine and the process  has been started using
both a control box object and inside the VBA Editor and it always hangs
up after the process is run.

 
Didn't find what you were looking for? Find more on Interesting problem when I run a vba process Or get search suggestion and latest updates.




Tagged: