• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 422
  • Last Modified:

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?
0
oyekomova
Asked:
oyekomova
  • 3
  • 2
1 Solution
 
LoNeRaVeR9Commented:
oyekomova:

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,

Jaes
0
 
mvidasCommented:
Hi,

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:
  Workbooks("A.xls").Sheets("Sheet1").Range("A1")

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.

Matt
0
 
mvidasCommented:
Oops, sorry Jaes, didn't refresh (again)
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LoNeRaVeR9Commented:
Matt:

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

Jaes
0
 
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...

myActiveworkbook.activate

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.
0
 
mvidasCommented:
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.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now