Passing a variable from a macro in a separate workbook to the call

  • Thread starter Thread starter Spike
  • Start date Start date
S

Spike

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
 
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)
 
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)
 
Back
Top