Hi,
I'm supporting someone who runs a Word macro that loops through many Excel workbooks, opening, copying content, pasting into Word and closing the Excel file, repeating a few dozen times.
The macro has worked well for months, but now the person has received a new laptop (with Win10; she was on Win7 before) and the macro runs for a few iterations and then pops up an error: "The method or property is not available because the clipboard is empty of not valid."
I Googled around and this thread seems to fit my issue. Doug Robbins replied with advice that resolved the error for the user. Doug mentioned to use an "On Error - DoEvents - Resume" loop?
How would I do this to test if it will resolve my mentioned issue?
-- Do I write this small loop into the code somewhere?
-- How do I write it?
-- Where must it be placed in the code?
TX in advance.
Timing Issue? (Or Error - DoEvents -Resume loop)
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Timing Issue? (Or Error - DoEvents -Resume loop)
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Timing Issue? (Or Error - DoEvents -Resume loop)
Near the top of the procedure, insert the line
Just above the End Sub line, insert:
Should this cause the code to loop endlessly (I hope it won't!), you can get out of it by pressing Ctrl+Break.
Code: Select all
On Error Goto ErrorHandler
Code: Select all
Exit Sub
ErrorHandler:
DoEvents
Resume
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Timing Issue? (Or Error - DoEvents -Resume loop)
TX.
Will give it a try and provide feedback.
Appreciate the advice.
Cheers
Will give it a try and provide feedback.
Appreciate the advice.
Cheers
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Timing Issue? (Or Error - DoEvents -Resume loop)
THX Hans.
That did the job.
The macro is running well again and not looping endlessly (thank goodness )
Appreciate your input.
Cheers
That did the job.
The macro is running well again and not looping endlessly (thank goodness )
Appreciate your input.
Cheers
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Microsoft MVP
- Posts: 1320
- Joined: 24 May 2013, 15:33
- Location: Warminster, PA
Re: Timing Issue? (Or Error - DoEvents -Resume loop)
With all respect for my colleague Doug Robbins, I would approach this slightly differently.
The DoEvents method causes VBA to yield control to Windows, allowing the OS to handle any events from other processes that have been delayed while VBA (which is single-threaded) has exclusive control of the CPU.
Instead of waiting for an error to occur in VBA before yielding, I prefer to insert a DoEvents call just before the Next, Wend, or Loop statement at the end of the main loop so it's called at each iteration. If there are no delayed events, the call adds a negligible time to each iteration. If there are events to be handled, Windows gets the chance to handle them as soon as possible. You can still use the On Error statement and an error-handler section, but then you can handle only "expected" errors there.
The DoEvents method causes VBA to yield control to Windows, allowing the OS to handle any events from other processes that have been delayed while VBA (which is single-threaded) has exclusive control of the CPU.
Instead of waiting for an error to occur in VBA before yielding, I prefer to insert a DoEvents call just before the Next, Wend, or Loop statement at the end of the main loop so it's called at each iteration. If there are no delayed events, the call adds a negligible time to each iteration. If there are events to be handled, Windows gets the chance to handle them as soon as possible. You can still use the On Error statement and an error-handler section, but then you can handle only "expected" errors there.
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Timing Issue? (Or Error - DoEvents -Resume loop)
I appreciate your valued input here Jay. I've since sent the revised code to the person who needed the help and they provided feedback that all is running well again. If for any reason the code fails again, I'll certainly take your recommendation into account.
Cheers
Cheers
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.