Call an ADDIN function from VBA code

[Guest]

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.

 

[papou]

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

HTH
Cordially
Pascal

 

[Guest]

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

 

[papou]

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

HTH
Cordially
Pascal

 

[Guest]

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

 

[Dave Peterson]

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

Notice the ()'s and the apostrophes.

 

[Guest]

Dave-

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

 

[Randy Harmelink]

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

 

[Dave Peterson]

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...

 

[Dave Peterson]

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

 

[Guest]

Success!! Thanks very much. It was the quotes.

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...

 

[Dave Peterson]

Sometimes, it's easy to miss those apostrophes.

(I do my best to keep them out of the file's name--And I don't like spaces in my
file names either <bg>.)

Success!! Thanks very much. It was the quotes.