Remote Sub Program Execution

Mechanic

Board Regular
Joined
Mar 26, 2002
Messages
60
Please give me an example of the command I would use to execute a sub program in a separate open workbook.
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Ahnold

Well-known Member
Joined
Feb 20, 2004
Messages
636
I believe this is what you are looking for:

Code:
Sub test()
    Application.Run "Book5!test1"
End Sub
 

Mechanic

Board Regular
Joined
Mar 26, 2002
Messages
60
This worked, however it ran the macro stored in the child and put the results in the parent (calling workbook) workbook. I wanted the child workbook to run data stored in the child and place the results in the child leaving the parent untouched. How do I do that?

Mechanic
 

Ahnold

Well-known Member
Joined
Feb 20, 2004
Messages
636
Without knowing your code, I don't know specifically what to do, but I will give you an example.

If you are accessing the code from "Book2" use the same Application.Run method. If the code you want to run is in "Book1" and you want it to run in "Book1" and in "Sheet1" and as an example the first step in the code you are trying to run is Range("A1").select you could change it to Application.Workbooks("Book1").Worksheets("Sheet1").Range("A1").select
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473

ADVERTISEMENT

Mechanic said:
This worked, however it ran the macro stored in the child and put the results in the parent (calling workbook) workbook. I wanted the child workbook to run data stored in the child and place the results in the child leaving the parent untouched. How do I do that?

Mechanic

In the child workbook's macro, perhaps prefix your references with ThisWorkbook. or use --

With ThisWorkbook
' do stuff
End Wth
 

Mechanic

Board Regular
Joined
Mar 26, 2002
Messages
60
I tried Ahnold's suggestion and it works. Unfortunately, it will be code intensive for all the references I have. Just_jon's suggestions seem like what I need but unfortunately I couldn't get the first one to work. Could you please offer some code so I can see if I'm doing it right. The second suggestion:

With ThisWorkbook
'do stuff
End With

is the most applicable to my work but it still ran in the calling (parent) workbook. For 'do stuff, I ran:

Range("A1").value=Range("A1").value+1

I see what the intent is, but it ran in the parent workbook, perhaps I used it incorrectly. Is there something else I should do to make it work?

Please forgive my rookie skills but, I', basically self taught.

I appreciate everyones help. Thank you,
Mechanic
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473

ADVERTISEMENT

Mechanic said:
I tried Ahnold's suggestion and it works. Unfortunately, it will be code intensive for all the references I have. Just_jon's suggestions seem like what I need but unfortunately I couldn't get the first one to work. Could you please offer some code so I can see if I'm doing it right. The second suggestion:

With ThisWorkbook
'do stuff
End With

is the most applicable to my work but it still ran in the calling (parent) workbook. For 'do stuff, I ran:

Range("A1").value=Range("A1").value+1
.
I see what the intent is, but it ran in the parent workbook, perhaps I used it incorrectly. Is there something else I should do to make it work?

Please forgive my rookie skills but, I', basically self taught.

I appreciate everyones help. Thank you,
Mechanic

In a With block, reference ranges/cells prefixed with a "." as in --

With ThisWorkbook
.Range("A1")=.Range("A1")+1
End With

BTW, no need for the .value bit -- that's the default if nothing specified.

Does this help?
 

Mechanic

Board Regular
Joined
Mar 26, 2002
Messages
60
I tried:

With ThisWorkbook
.Range("A1")=.Range("A1") +1
End With

and got the following error:

Complie Error:
Method or data member not found

Did I miss something?
Mechanic
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
:oops:

Sorry --

With ThisWorkbook
.Worksheets("sheethere").Range("A1")=...

or

With ThisWorkbook
With Worksheets("sheethere")
.Range("A1")=...

or

With ThisWorkbook.Worksheets("sheethere")
.Range("A1")=...


Need to have a sheet name reference there, too. :oops:
 

Mechanic

Board Regular
Joined
Mar 26, 2002
Messages
60
That did the trick!! Thank you very much, I appreciate your continued help.

Mechanic
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,326
Members
414,053
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top