I have a function in an addin that is loaded. I seem unable to come up with
the syntax in VBA code to get it to function properly.

As a workbook function it is used as
=topleft(r)
where r is a range

If I copy the code into the module, it is used as

rtl=topleft(r)

How do I use it without cloning the code into the module?

Thanks.

Hi Marvin
Use Application.run "YourAddin.xla",Argument

HTH
Cordially
Pascal

"Marvin" <(E-Mail Removed)> a écrit dans le message de news:
05F1B491-B825-4172-A943-(E-Mail Removed)...
>I have a function in an addin that is loaded. I seem unable to come up
>with
> the syntax in VBA code to get it to function properly.
>
> As a workbook function it is used as
> =topleft(r)
> where r is a range
>
> If I copy the code into the module, it is used as
>
> rtl=topleft(r)
>
> How do I use it without cloning the code into the module?
>
> Thanks.

This syntax allows execution, but does not allow for a return value from the
function in ther ADDIN.

"papou" wrote:

> Hi Marvin
> Use Application.run "YourAddin.xla",Argument
>
> HTH
> Cordially
> Pascal
>
> "Marvin" <(E-Mail Removed)> a écrit dans le message de news:
> 05F1B491-B825-4172-A943-(E-Mail Removed)...
> >I have a function in an addin that is loaded. I seem unable to come up
> >with
> > the syntax in VBA code to get it to function properly.
> >
> > As a workbook function it is used as
> > =topleft(r)
> > where r is a range
> >
> > If I copy the code into the module, it is used as
> >
> > rtl=topleft(r)
> >
> > How do I use it without cloning the code into the module?
> >
> > Thanks.
>
>
>

Hi Marvin
Yes you can return the value with :
MyValue = Application.run "YourAddin.xla",Argument

HTH
Cordially
Pascal

"Marvin" <(E-Mail Removed)> a écrit dans le message de news:
54F31A74-838A-4F37-BC8C-(E-Mail Removed)...
> This syntax allows execution, but does not allow for a return value from
> the
> function in ther ADDIN.
>
> "papou" wrote:
>
>> Hi Marvin
>> Use Application.run "YourAddin.xla",Argument
>>
>> HTH
>> Cordially
>> Pascal
>>
>> "Marvin" <(E-Mail Removed)> a écrit dans le message de
>> news:
>> 05F1B491-B825-4172-A943-(E-Mail Removed)...
>> >I have a function in an addin that is loaded. I seem unable to come up
>> >with
>> > the syntax in VBA code to get it to function properly.
>> >
>> > As a workbook function it is used as
>> > =topleft(r)
>> > where r is a range
>> >
>> > If I copy the code into the module, it is used as
>> >
>> > rtl=topleft(r)
>> >
>> > How do I use it without cloning the code into the module?
>> >
>> > Thanks.
>>
>>
>>

Unfortunately, the syntax you suggest doesn't work. My code is:

rtl=application.run "Marvin's Private Functions.xla!topleft",r

It produces a compiler error indicating

Expected: end of statement

"papou" wrote:

> Hi Marvin
> Yes you can return the value with :
> MyValue = Application.run "YourAddin.xla",Argument
>
> HTH
> Cordially
> Pascal
>
> "Marvin" <(E-Mail Removed)> a écrit dans le message de news:
> 54F31A74-838A-4F37-BC8C-(E-Mail Removed)...
> > This syntax allows execution, but does not allow for a return value from
> > the
> > function in ther ADDIN.
> >
> > "papou" wrote:
> >
> >> Hi Marvin
> >> Use Application.run "YourAddin.xla",Argument
> >>
> >> HTH
> >> Cordially
> >> Pascal
> >>
> >> "Marvin" <(E-Mail Removed)> a écrit dans le message de
> >> news:
> >> 05F1B491-B825-4172-A943-(E-Mail Removed)...
> >> >I have a function in an addin that is loaded. I seem unable to come up
> >> >with
> >> > the syntax in VBA code to get it to function properly.
> >> >
> >> > As a workbook function it is used as
> >> > =topleft(r)
> >> > where r is a range
> >> >
> >> > If I copy the code into the module, it is used as
> >> >
> >> > rtl=topleft(r)
> >> >
> >> > How do I use it without cloning the code into the module?
> >> >
> >> > Thanks.
> >>
> >>
> >>
>
>
>

maybe
rtl=application.run("'Marvin's Private Functions.xla'!topleft", r)

Notice the ()'s and the apostrophes.


Marvin wrote:
>
> Unfortunately, the syntax you suggest doesn't work. My code is:
>
> rtl=application.run "Marvin's Private Functions.xla!topleft",r
>
> It produces a compiler error indicating
>
> Expected: end of statement
>
> "papou" wrote:
>
> > Hi Marvin
> > Yes you can return the value with :
> > MyValue = Application.run "YourAddin.xla",Argument
> >
> > HTH
> > Cordially
> > Pascal
> >
> > "Marvin" <(E-Mail Removed)> a écrit dans le message de news:
> > 54F31A74-838A-4F37-BC8C-(E-Mail Removed)...
> > > This syntax allows execution, but does not allow for a return value from
> > > the
> > > function in ther ADDIN.
> > >
> > > "papou" wrote:
> > >
> > >> Hi Marvin
> > >> Use Application.run "YourAddin.xla",Argument
> > >>
> > >> HTH
> > >> Cordially
> > >> Pascal
> > >>
> > >> "Marvin" <(E-Mail Removed)> a écrit dans le message de
> > >> news:
> > >> 05F1B491-B825-4172-A943-(E-Mail Removed)...
> > >> >I have a function in an addin that is loaded. I seem unable to come up
> > >> >with
> > >> > the syntax in VBA code to get it to function properly.
> > >> >
> > >> > As a workbook function it is used as
> > >> > =topleft(r)
> > >> > where r is a range
> > >> >
> > >> > If I copy the code into the module, it is used as
> > >> >
> > >> > rtl=topleft(r)
> > >> >
> > >> > How do I use it without cloning the code into the module?
> > >> >
> > >> > Thanks.
> > >>
> > >>
> > >>
> >
> >
> >

--

Dave Peterson

Dave-

Thanks. Syntactically it is now correct, but the ADDIN is not found even if
I use the complete path in the file name.

"Dave Peterson" wrote:

> maybe
> rtl=application.run("'Marvin's Private Functions.xla'!topleft", r)
>
> Notice the ()'s and the apostrophes.
>
>
> Marvin wrote:
> >
> > Unfortunately, the syntax you suggest doesn't work. My code is:
> >
> > rtl=application.run "Marvin's Private Functions.xla!topleft",r
> >
> > It produces a compiler error indicating
> >
> > Expected: end of statement
> >
> > "papou" wrote:
> >
> > > Hi Marvin
> > > Yes you can return the value with :
> > > MyValue = Application.run "YourAddin.xla",Argument
> > >
> > > HTH
> > > Cordially
> > > Pascal
> > >
> > > "Marvin" <(E-Mail Removed)> a écrit dans le message de news:
> > > 54F31A74-838A-4F37-BC8C-(E-Mail Removed)...
> > > > This syntax allows execution, but does not allow for a return value from
> > > > the
> > > > function in ther ADDIN.
> > > >
> > > > "papou" wrote:
> > > >
> > > >> Hi Marvin
> > > >> Use Application.run "YourAddin.xla",Argument
> > > >>
> > > >> HTH
> > > >> Cordially
> > > >> Pascal
> > > >>
> > > >> "Marvin" <(E-Mail Removed)> a écrit dans le message de
> > > >> news:
> > > >> 05F1B491-B825-4172-A943-(E-Mail Removed)...
> > > >> >I have a function in an addin that is loaded. I seem unable to come up
> > > >> >with
> > > >> > the syntax in VBA code to get it to function properly.
> > > >> >
> > > >> > As a workbook function it is used as
> > > >> > =topleft(r)
> > > >> > where r is a range
> > > >> >
> > > >> > If I copy the code into the module, it is used as
> > > >> >
> > > >> > rtl=topleft(r)
> > > >> >
> > > >> > How do I use it without cloning the code into the module?
> > > >> >
> > > >> > Thanks.
> > > >>
> > > >>
> > > >>
> > >
> > >
> > >
>
> --
>
> Dave Peterson
>

In order to make the functions of the add-in available like normal VBA
functions, you need to use the menu option > Tools > References and
add a reference to the add-in project to the VBE project. Then, you
should be able to just do something like this in your VBA code:

x = topleft(Range(r))

On Jun 22, 6:46 am, Marvin <Mar...@discussions.microsoft.com> wrote:
> I have a function in an addin that is loaded. I seem unable to come up with
> the syntax in VBA code to get it to function properly.
>
> As a workbook function it is used as
> =topleft(r)
> where r is a range
>
> If I copy the code into the module, it is used as
>
> rtl=topleft(r)
>
> How do I use it without cloning the code into the module?
>
> Thanks.

Make sure that the addin is open.

If it is, then try this manually:

rtl=application.run("'Marvin''s Private Functions.xla'!topleft", r)
(notice the '' in Marvin''s.)

If it works there, then you need to do the same in your code.

If the addin may be closed:

dim testwkbk as workbook
dim MarvFilename as string
marvfilename = "marvin's private functions.xla"
set testwkbk = nothing
on error resume next
set testwkbk = workbooks(marvfilename)
on error goto 0

if testwkbk is nothing then
set testwkbk = workbooks.open("C:\somepath\" & marvfilename)
end if

'and either this:
rtl=application.run("'" & testwkbk.name & "'!topleft", r)
or
fix up that appostrophe:
rtl=application.run("'" & application.substitute(testwkbk.name, "'", "''") _
& "'!topleft", r)

xl2k added Replace instead of using application.substitute.

======
Untested...


Marvin wrote:
>
> Dave-
>
> Thanks. Syntactically it is now correct, but the ADDIN is not found even if
> I use the complete path in the file name.
>
> "Dave Peterson" wrote:
>
> > maybe
> > rtl=application.run("'Marvin's Private Functions.xla'!topleft", r)
> >
> > Notice the ()'s and the apostrophes.
> >
> >
> > Marvin wrote:
> > >
> > > Unfortunately, the syntax you suggest doesn't work. My code is:
> > >
> > > rtl=application.run "Marvin's Private Functions.xla!topleft",r
> > >
> > > It produces a compiler error indicating
> > >
> > > Expected: end of statement
> > >
> > > "papou" wrote:
> > >
> > > > Hi Marvin
> > > > Yes you can return the value with :
> > > > MyValue = Application.run "YourAddin.xla",Argument
> > > >
> > > > HTH
> > > > Cordially
> > > > Pascal
> > > >
> > > > "Marvin" <(E-Mail Removed)> a écrit dans le message de news:
> > > > 54F31A74-838A-4F37-BC8C-(E-Mail Removed)...
> > > > > This syntax allows execution, but does not allow for a return value from
> > > > > the
> > > > > function in ther ADDIN.
> > > > >
> > > > > "papou" wrote:
> > > > >
> > > > >> Hi Marvin
> > > > >> Use Application.run "YourAddin.xla",Argument
> > > > >>
> > > > >> HTH
> > > > >> Cordially
> > > > >> Pascal
> > > > >>
> > > > >> "Marvin" <(E-Mail Removed)> a écrit dans le message de
> > > > >> news:
> > > > >> 05F1B491-B825-4172-A943-(E-Mail Removed)...
> > > > >> >I have a function in an addin that is loaded. I seem unable to come up
> > > > >> >with
> > > > >> > the syntax in VBA code to get it to function properly.
> > > > >> >
> > > > >> > As a workbook function it is used as
> > > > >> > =topleft(r)
> > > > >> > where r is a range
> > > > >> >
> > > > >> > If I copy the code into the module, it is used as
> > > > >> >
> > > > >> > rtl=topleft(r)
> > > > >> >
> > > > >> > How do I use it without cloning the code into the module?
> > > > >> >
> > > > >> > Thanks.
> > > > >>
> > > > >>
> > > > >>
> > > >
> > > >
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

That certainly is an option. But you don't _need_ to do it that way.

Randy Harmelink wrote:
>
> In order to make the functions of the add-in available like normal VBA
> functions, you need to use the menu option > Tools > References and
> add a reference to the add-in project to the VBE project. Then, you
> should be able to just do something like this in your VBA code:
>
> x = topleft(Range(r))
>
> On Jun 22, 6:46 am, Marvin <Mar...@discussions.microsoft.com> wrote:
> > I have a function in an addin that is loaded. I seem unable to come up with
> > the syntax in VBA code to get it to function properly.
> >
> > As a workbook function it is used as
> > =topleft(r)
> > where r is a range
> >
> > If I copy the code into the module, it is used as
> >
> > rtl=topleft(r)
> >
> > How do I use it without cloning the code into the module?
> >
> > Thanks.

--

Dave Peterson