Call Macro from PERSONAL.XLS

[jutlaux]

I have a macro that is stored in my personal.xls that I would like to be able
to call from another workbook. I have the macro stored in a standard code
module and have it defined as a public type, but when i go to call it from
another workbook I get:

"Compile error:

Sub or Function not defined"

Aside from coping the entire script into the new workbook is there a way to
call a script stored in personal.xls from another workbook?

Thanks!

 

[Dave Peterson]

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)

 

[jutlaux]

I used your application.run suggestion and this did work as needed. Thanks!

I failed to mention that the script being called has a variable that returns
some information. When I use your suggestion the public variable that has
the information to be returned is empty. Thoughts?

Thanks again.

 

[Dave Peterson]

The public variable is in personal.xls and you're trying to return that?

If yes, then create a function in personal.xls and call that function:

Function ReturnPublicVar1Val() as string 'variant, long, ...
returnpublicvar1val = MyPublicVariableNameGoesHere
end function

 

[Dave Peterson]

Or add a reference to that personal.xls workbook and just use the variable like
it's local to your project.