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
jutlaux wrote:
>
> 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" wrote:
>
> > 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 wrote:
> > >
> > > 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
> >
--
Dave Peterson
|
Similar Threads
