Passing Macro control between workbooks without interference

I have two Excel spreadsheet in two different workbooks A & B containing VBA macros that run independently. The two spreadsheets have the same set of stock symbols. Sheet 1 in A contains macros that determine a buy/sell trigger based on a data feed (DDE), while sheet 1 in B places the order based on the trigger from A. Sheet 1 in B has the worksheet_calculate macro to place the order upon entry of the trigger in a particular cell. After the macro from A copies the trigger to sheet 1 in B, what do I need to do to pass control back to the first sheet and then letting the second sheet operate independently? How can I prevent any interference by the macro from the first workbook on the second?
Who is Participating?
mvidasConnect With a Mentor Commented:
There shouldn't be any disruption. I have limited experience with DDE links but I'm almost positive they're tied into the worksheet itself, so you shouldn't have an issue.

You can pass variables/objects/etc. between the macros and have them work independent of the other, but the original macro will still be active because it doesn't stop until it reaches the End Sub.  The best thing I can suggest is that you use the workbook object to specify the specific workbook to work in.  The other option would be to control the second workbook with the macros in the first and that way you don't need any macros in the second unless they are event driven and required for the function of that workbook.

Kindest Regards,


You can qualify all range references accordingly in your macros.  Instead of just using something like      Range("A1")   or   Cells(1,1)   specify the workbook and worksheet to make sure the right cells are being referenced, like:

You can also use workbook and worksheet variables if needed to shorten the references, or even multiple With blocks. But as long as you qualify the ranges you'll make sure you're referring to the right place.

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Oops, sorry Jaes, didn't refresh (again)

No problems...  thanks for replying.  It's good to see that I was on the right track...  ;)

oyekomovaAuthor Commented:
Thanks. It turns out that I also needed the following in addition to your suggestion on absolute references: Workbooks("A.xls").Sheets("Sheet1").Range("A1").

Set myActiveworkbook  = activeworkbook

workbook("secondworkbook"). activate
   ....pass the trigger...


I needed this because the "secondworkbook" has Worksheet_calculate procedure and did not operate unitl I activated it.

The question I have now is that the first workbook has DDE links, and I am not sure if there would be any disruption as I am activating back and forth.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.