XLL Functions from Excel VBA

[LLT]

Hi,

I am trying to call a function in an installed XLL addin in excel. Unless I
declare the function, I cannot use it.

If I use this declaration:
Declare Function EssVDisconnect Lib "ESSEXCLN.XLL" (ByVal SheetName As
Variant) As Long
and call:
EssVDisconnect "SheetName"
it works.

But if I try to use it this way (without a declaration), it doesn't work:
Application.Run "ESSEXCLN.XLL!EssVDisconnect", "SheetName"

Can someone explain if it is required for calling XLL functions from VBA to
always Declare their functions?

Thanks,

Leo

 

[Peter T]

I don't see how either of your examples would work as posted. The argument
SheetName is received ByVal so even if changed in the function it would
return as originally passed. The function returns a Long but in neither
example have you assigned its result. Or does the function do something to
the sheet object.

Even if the purpose was to process SheetName and return it modified, the
app.run method would not work as variables passed as arguments are converted
to values before passing, iow they will not return modified.

Apart from the above, if anything I would have thought the application.run
method would be more likely to work than the API like Declare method. For
the Declare method to work I think, though not sure, it means the author has
included additional methods to enable the function to be called directly in
VBA. If Declare does work use it that way in preference to app.run as it
would be very significantly faster than app.run.

Regards,
Peter T

 

[LLT]

Hi Peter

The reason why I haven't assigned the result of the function to a variable
or what the function actually does to the objects passesd to it is
irrelevant to my question. The example using declarations works without
errors and as expected. The example using the Run method does not work. I
was trying to assess why in need to declare the functions in advance for
this particular XLL add-in, when for calling other functions I can use the
Run method directly....thanks for your input anyway.

Leo

 

[Peter T]

What do you mean by "The example using the Run method does not work", eg it
errors, it does not do anything, it does something incorrectly, etc

I don't understand the sentence beginning "I was trying to assess..."

However if you can use the Declare approach why not stick with that as it
will work very considerably faster than app.run.

Regards,
Peter T

 

[Simon Murphy]

Leo

Non of the V functions are registered with Excel because they are not
for use from a worksheet cell, they are the VBA versions that you call
via the declares.

Why dont you just import the EssVBA.txt module with all the declares in?

You could try the calling the XLM version from the spreadsheet toolkit
if you are determined to use app.run.

Cheers
Simon
Blog: www.smurfonspreadsheets.net
Website: www.codematic.net Excel development, support and training