Timing Issue? (Or Error - DoEvents -Resume loop)

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Timing Issue? (Or Error - DoEvents -Resume loop)

Post by Rudi »

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.
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Timing Issue? (Or Error - DoEvents -Resume loop)

Post by HansV »

Near the top of the procedure, insert the line

Code: Select all

    On Error Goto ErrorHandler
Just above the End Sub line, insert:

Code: Select all

    Exit Sub

ErrorHandler:
    DoEvents
    Resume
Should this cause the code to loop endlessly (I hope it won't!), you can get out of it by pressing Ctrl+Break.
Best wishes,
Hans

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Timing Issue? (Or Error - DoEvents -Resume loop)

Post by Rudi »

TX.

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.

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Timing Issue? (Or Error - DoEvents -Resume loop)

Post by Rudi »

THX Hans.

That did the job.
The macro is running well again and not looping endlessly (thank goodness :smile: )

Appreciate your input.
Cheers :cheers:
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
Jay Freedman
Microsoft MVP
Posts: 1313
Joined: 24 May 2013, 15:33
Location: Warminster, PA

Re: Timing Issue? (Or Error - DoEvents -Resume loop)

Post by Jay Freedman »

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.

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Timing Issue? (Or Error - DoEvents -Resume loop)

Post by Rudi »

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.