Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing a variable from a macro in a separate workbook to the call
I have a macro in book A that opens a workbook(book B) and runs a macro in
that workbook. I would like to return a variable set in the macro in book B to the calling macro in Book A. Is this possible. I know i could use a workaround by the macro in book B settiing a cell value in book A with the required value -- with kind regards Spike |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing a variable from a macro in a separate workbook to the call
yourMacro = "myMacro" ' the name of the macro you wish to run in the
new/second workbook ' Open your new/second workbook yourNewBook = "myFile.xls" ' the name of the new/second workbook that you have opened (presumably programmatically) application.run ("'"+yourNewBook+"'!"&yourMacro) "Spike" wrote: I have a macro in book A that opens a workbook(book B) and runs a macro in that workbook. I would like to return a variable set in the macro in book B to the calling macro in Book A. Is this possible. I know i could use a workaround by the macro in book B settiing a cell value in book A with the required value -- with kind regards Spike |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing a variable from a macro in a separate workbook to the call
If the macro in workbookB is a function, you could do it pretty easily:
This was saved from a post for a similar question. You'll have to change the workbook name from personal.xls to the real name. Dim pWkbk as workbook set pwkbk = workbooks("Personal.xls") application.run "'" & pwkb.name & "'!macronamehere", "parm1", "parm2" or if you're returning a value from a function: dim res as string 'or variant or long or ... res = application.run("'" & pwkb.name & "'!macronamehere", "parm1", "parm2") You could also create a reference to this personal.xls workbook and call it just like it was built into excel. Tools|references (but give the personal.xls's project a nice unique name (not VBAProject). ps. If you're using a function living in personal.xls inside a cell: =personal.xls!functionnamehere(a1,b1,c1) or save the file as an addin (*.xla) and use it in the cell like it's built into excel: =functionnamehere(a1,b1,c1) Spike wrote: I have a macro in book A that opens a workbook(book B) and runs a macro in that workbook. I would like to return a variable set in the macro in book B to the calling macro in Book A. Is this possible. I know i could use a workaround by the macro in book B settiing a cell value in book A with the required value -- with kind regards Spike -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing a variable from a macro in a separate workbook to the
Thank you that is very helpful
Many thanks -- with kind regards Spike "Dave Peterson" wrote: If the macro in workbookB is a function, you could do it pretty easily: This was saved from a post for a similar question. You'll have to change the workbook name from personal.xls to the real name. Dim pWkbk as workbook set pwkbk = workbooks("Personal.xls") application.run "'" & pwkb.name & "'!macronamehere", "parm1", "parm2" or if you're returning a value from a function: dim res as string 'or variant or long or ... res = application.run("'" & pwkb.name & "'!macronamehere", "parm1", "parm2") You could also create a reference to this personal.xls workbook and call it just like it was built into excel. Tools|references (but give the personal.xls's project a nice unique name (not VBAProject). ps. If you're using a function living in personal.xls inside a cell: =personal.xls!functionnamehere(a1,b1,c1) or save the file as an addin (*.xla) and use it in the cell like it's built into excel: =functionnamehere(a1,b1,c1) Spike wrote: I have a macro in book A that opens a workbook(book B) and runs a macro in that workbook. I would like to return a variable set in the macro in book B to the calling macro in Book A. Is this possible. I know i could use a workaround by the macro in book B settiing a cell value in book A with the required value -- with kind regards Spike -- Dave Peterson . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Passing a variable into a subroutine in a different open workbook | Excel Programming | |||
Passing a Variable to a Macro | Excel Programming | |||
call macro with variable | Excel Programming | |||
Passing a range in a macro call | Excel Programming | |||
Passing a Variable from Worksheet to Workbook | Excel Programming |