Public function in Personal.xls

Ardus Petus · 06-10-2006, 04:15 AM

I have a series of utility functions in Personal.xls

I know how to call them from a worksheet cell.

What is the syntax to call them from VBA code?

TIA,
--
AP

Bob Phillips · 06-10-2006, 04:45 AM

Look at Application.Run in help, or set a reference to Personal.xls in your
workbook, then you could call them as if they were locakl to your workbook.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Ardus Petus" <[email protected]> wrote in message
news:[email protected]...
> I have a series of utility functions in Personal.xls
>
> I know how to call them from a worksheet cell.
>
> What is the syntax to call them from VBA code?
>
> TIA,
> --
> AP
>
>

Ardus Petus · 06-10-2006, 05:20 AM

Application.Run is OK for a Sub, not a Function.

Setting a reference to Personal.xls fails: "Module name, project name or
library name already in use"

Cheers
--
AP

"Bob Phillips" <[email protected]> a écrit dans le message de news:
[email protected]...
> Look at Application.Run in help, or set a reference to Personal.xls in
> your
> workbook, then you could call them as if they were locakl to your
> workbook.
>
> --
>
> HTH
>
> Bob Phillips
>
> (replace xxxx in the email address with gmail if mailing direct)
>
> "Ardus Petus" <[email protected]> wrote in message
> news:[email protected]...
>> I have a series of utility functions in Personal.xls
>>
>> I know how to call them from a worksheet cell.
>>
>> What is the syntax to call them from VBA code?
>>
>> TIA,
>> --
>> AP
>>
>>
>
>

Joergen Bondesen · 06-10-2006, 05:45 AM

Hi Bob

> set a reference to Personal.xls in your workbook
Are you sure about this, because I can not find Personal in Excel 2003
reference.
What must I look for?

--
Best regards
Joergen Bondesen

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> Look at Application.Run in help, or set a reference to Personal.xls in
> your
> workbook, then you could call them as if they were locakl to your
> workbook.
>
> --
>
> HTH
>
> Bob Phillips
>
> (replace xxxx in the email address with gmail if mailing direct)
>
> "Ardus Petus" <[email protected]> wrote in message
> news:[email protected]...
>> I have a series of utility functions in Personal.xls
>>
>> I know how to call them from a worksheet cell.
>>
>> What is the syntax to call them from VBA code?
>>
>> TIA,
>> --
>> AP

Bob Phillips · 06-10-2006, 05:55 AM

"Ardus Petus" <[email protected]> wrote in message
news:[email protected]...
> Application.Run is OK for a Sub, not a Function.

Not correct, it works just as well on a function

MsgBox Application.Run("Personal.xls!testrun", "aok")

I

> Setting a reference to Personal.xls fails: "Module name, project name or
> library name already in use"

Not on my system it doesn't, works fine. You might need to name the
Personal.xls project something meaningful so that you can find it in the
list.

Bob Phillips · 06-10-2006, 05:55 AM

Yes I am sure, it is just another workbook and it can be referenced as such.
As long as you have a Personal.xls (and that is not a given, it has to be
created), you should see it in the list of references. As I mentioned to
Ardus, you may need to change the project name in Properties, so that it is
unique, and identifiable in the list.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Joergen Bondesen" <[email protected]> wrote in message
news:[email protected]...
> Hi Bob
>
> > set a reference to Personal.xls in your workbook
> Are you sure about this, because I can not find Personal in Excel 2003
> reference.
> What must I look for?
>
> --
> Best regards
> Joergen Bondesen
>
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > Look at Application.Run in help, or set a reference to Personal.xls in
> > your
> > workbook, then you could call them as if they were locakl to your
> > workbook.
> >
> > --
> >
> > HTH
> >
> > Bob Phillips
> >
> > (replace xxxx in the email address with gmail if mailing direct)
> >
> > "Ardus Petus" <[email protected]> wrote in message
> > news:[email protected]...
> >> I have a series of utility functions in Personal.xls
> >>
> >> I know how to call them from a worksheet cell.
> >>
> >> What is the syntax to call them from VBA code?
> >>
> >> TIA,
> >> --
> >> AP
>
>

Ardus Petus · 06-10-2006, 06:15 AM

That was it: I renamed VBA Project into Perso, and I could set a reference
to it.

Thanks for all,
--
AP

"Bob Phillips" <[email protected]> a écrit dans le message de news:
[email protected]...
>
> "Ardus Petus" <[email protected]> wrote in message
> news:[email protected]...
>> Application.Run is OK for a Sub, not a Function.
>
> Not correct, it works just as well on a function
>
> MsgBox Application.Run("Personal.xls!testrun", "aok")
>
> I
>
>> Setting a reference to Personal.xls fails: "Module name, project name or
>> library name already in use"
>
> Not on my system it doesn't, works fine. You might need to name the
> Personal.xls project something meaningful so that you can find it in the
> list.
>
>

Joergen Bondesen · 06-10-2006, 06:25 AM

Hi Bob

It works perfect now.
Thanks for your patience.

--
Best regards
Joergen Bondesen

"Bob Phillips" <[email protected]> wrote in message
news:%[email protected]...
> Yes I am sure, it is just another workbook and it can be referenced as
> such.
> As long as you have a Personal.xls (and that is not a given, it has to be
> created), you should see it in the list of references. As I mentioned to
> Ardus, you may need to change the project name in Properties, so that it
> is
> unique, and identifiable in the list.
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Joergen Bondesen" <[email protected]> wrote in message
> news:[email protected]...
>> Hi Bob
>>
>> > set a reference to Personal.xls in your workbook
>> Are you sure about this, because I can not find Personal in Excel 2003
>> reference.
>> What must I look for?
>>
>> --
>> Best regards
>> Joergen Bondesen
>>
>>
>> "Bob Phillips" <[email protected]> wrote in message
>> news:[email protected]...
>> > Look at Application.Run in help, or set a reference to Personal.xls in
>> > your
>> > workbook, then you could call them as if they were locakl to your
>> > workbook.
>> >
>> > --
>> >
>> > HTH
>> >
>> > Bob Phillips
>> >
>> > (replace xxxx in the email address with gmail if mailing direct)
>> >
>> > "Ardus Petus" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> I have a series of utility functions in Personal.xls
>> >>
>> >> I know how to call them from a worksheet cell.
>> >>
>> >> What is the syntax to call them from VBA code?
>> >>
>> >> TIA,
>> >> --
>> >> AP
>>
>>
>
>

Dave Peterson · 06-10-2006, 07:30 AM

I think you should try application.run using a function.

I bet it works.

Ardus Petus wrote:
>
> Application.Run is OK for a Sub, not a Function.
>
> Setting a reference to Personal.xls fails: "Module name, project name or
> library name already in use"
>
> Cheers
> --
> AP
>
> "Bob Phillips" <[email protected]> a écrit dans le message de news:
> [email protected]...
> > Look at Application.Run in help, or set a reference to Personal.xls in
> > your
> > workbook, then you could call them as if they were locakl to your
> > workbook.
> >
> > --
> >
> > HTH
> >
> > Bob Phillips
> >
> > (replace xxxx in the email address with gmail if mailing direct)
> >
> > "Ardus Petus" <[email protected]> wrote in message
> > news:[email protected]...
> >> I have a series of utility functions in Personal.xls
> >>
> >> I know how to call them from a worksheet cell.
> >>
> >> What is the syntax to call them from VBA code?
> >>
> >> TIA,
> >> --
> >> AP
> >>
> >>
> >
> >

--

Dave Peterson