PDA

View Full Version : Asynchronous or Synchronous Execution?



Cyberdude
07-25-2006, 03:27 PM
I have a macro in workbook ?A? that opens workbook ?B? just before it exits using the following code:
Workbooks.Open Filename:="C:\Excel Documents\WorkbookB.xls"
Application.Run "'WorkbookB.xls'!UpdateButtonMsgValue"
Workbooks(ActiveWorkbook.Name ).Activate Workbook ?B? is supposed to execute the ?Workbook_Open? macro when it is opened, and it does most if not all of the time. When it executes, it performs some data loading into workbook ?B?. I have an error checking mechanism in place to detect if that initialization does not occur. Once in awhile, the error message is triggered, but most of the time it works just fine. My question is, why are there occasions when the initialization doesn?t occur? Then I wondered if it has something to do with the ?Run? statement in my example above.

Actually I want to know if the ?Workbook_Open? macro runs to completion BEFORE the ?Run? statement is executed, or does ?Workbooks.Open Filename? statement just start the open process, then passes control to the next statement (the ?Run? statement in this case), so that at times the ?Run? statement starts executing before the file open process is completed. I?m skeptical that this happens, but I thought I?d better ask.

malik641
07-25-2006, 06:14 PM
Actually I want to know if the “Workbook_Open” macro runs to completion BEFORE the “Run” statement is executed, or does “Workbooks.Open Filename” statement just start the open process, then passes control to the next statement (the “Run” statement in this case), so that at times the “Run” statement starts executing before the file open process is completed. I’m skeptical that this happens, but I thought I’d better ask. Maybe it does...I would replace Application.Run with Application.OnTime and put like 1 second to wait for running your macro...and if it always works, then perhaps what you were thinking IS indeed accurate.

Or maybe you can't use that string for Application.OnTime....???

Then again, we could just wait for the gurus to help out on this one :)

malik641
07-25-2006, 06:24 PM
I'm sorry, rather use:

Application.Wait (Now + TimeValue("00:00:01"))
before executing the macro.

Cyberdude
07-25-2006, 08:18 PM
Thanx for the suggestion, malik. I'll put it in. The problem is that the failure to run is rather seldom, so it'll take a while before I have any confidence that this is THE workaround. Another quirk is that this is the only workbook I'm having this problem with. Actually I have a chain of about 10 to 12 workbooks that are each being updated, then each sucks in the next workbook and executes similar logic to the one shown above. They all seem vulnerable, but I can't remember having a failure on any of the others, and this has been executing for literally years. (sigh)

malik641
07-25-2006, 08:22 PM
Yeah...I know it's not the best answer....but I would rather tell you my sloppy workaround (if it works, that is) than not.

xld
07-26-2006, 12:47 AM
In most cases the workbook open will fully complete before control is passed back, but there are some things that could be done asynchronously and create timing issues. In these cases, I would use Ontime to handle it, let the OS take care of it (I had an add-in that wouldn't properly rebuild menus, and OnTime resolve this).

But the question that I have to ask is why are you doing it that way? Why not launch the macro from within Workbook B?