Calling an XLL from VB?

[Maury Markowitz]

I have an ancient XLL plugin for XL that we use, and since the time we
purchased it we've moved most of our code to VB. I'd like to move this over
as well, but I'm not sure how to do it. Is there some way to load an XLL into
VB? And if so, how do we call it? The XLL is currently called use
"Application.Run", but I'm not entirely sure what this command does.

Maury

 

[Franck]

Wish this will help you
http://www.planatechsolutions.com/xlpfaq/Q0032.htm


Maury Markowitz a écrit :

 

[Maury Markowitz]

Wish this will help you
http://www.planatechsolutions.com/xlpfaq/Q0032.htm

Well it's not EXACTLY what I need, but it will likely help me get there.
Thanks!

Maury

 

[Tom Ogilvy]

An XLL is a special form of Dynamic Link Library (DLL). It is compiled from
C or C++ source code probably. So you would not have any way to view the
code in VB.

You would basically have to figure out what the functionality is, develop an
algorithmic approach to achieve that functionality, then program it from
scratch in VB.

Application.Run does just what you say, it causes the xll's code to execute.

 

[NickHK]

Maury,
As Tom says, an XLL is compiled, so forget about seeing the code.
I'm not sure how different an XLL is from a standard DLL, but with the only
XLL that I have on my system (ANALYS32.xll), calls to it seem routed through
XLCALL32.DLL's "Excel4V" function. So, assuming the actual call, arguments
and return type etc are correct (which I am not), this will not work:
Private Declare Function XLLFunc1 Lib "C:\ANALYS32.XLL" Alias "bin2dec"
(BinStr As String) As Long

There probably are ways of using the XLL directly, but not sure how straight
forward in VB:
http://www.delphi3000.com/articles/article_4104.asp?SK=

However, it does seem a rewrite would be easier if you understand the
concepts of the functions in the XLL and create an ActiveX DLL that's native
to VB or a standard DLL with some extra work.

NickHK

 

[Maury Markowitz]

An XLL is a special form of Dynamic Link Library (DLL). It is compiled from
C or C++ source code probably. So you would not have any way to view the
code in VB.

I don't care about seeing the code. The code I'm talking about moving is OUR
code, which was in Excel and is now in Access. The XLL is a black box, and
that's fine.

You would basically have to figure out what the functionality is, develop an
algorithmic approach to achieve that functionality, then program it from
scratch in VB.

DLL's are generally easy to call from VBA, as long as you know the method
sig. I do in this case. I need to know how to do that into an XLL.

Application.Run does just what you say, it causes the xll's code to execute.

But how? Exactly what does "Application.Run" mean? Does it call the DLL
loader? Does it hop into a known starting point? What _exactly_ does it do? I
can call Application.Run on methods in other VBA programs, so it's not simply
trivially simple.

Maury

 

[Tom Ogilvy]

You would call the XLL same as you would a DLL by putting in a declaration.

For application.run to work, I believe you have to use the register function
to register it.


As to run, you look at the vba help on the run command in Excel VBA. Also
several alternative including that are discussed here:

http://tinyurl.com/yfyrpr

At Laurent Longre's site
http://xcell05.free.fr/
select the english page and at the bottom are links to information on XLL's

 

[Maury Markowitz]

You would call the XLL same as you would a DLL by putting in a declaration.

I think this is the key. Using the Lib declare seems to work, as if the XLL
is nothing more than a DLL -- which I think is exactly the case, a DLL with
some "known extras" inside that Excel looks for. VB appears to be able to use
it without caring about these extras.

Ok, seeing as this is my first attempt at writing a wrapper from scratch,
would you mind a bit of help mapping the data types in the method sig? This
is the entry in the .h, names have been changed to protect the innocent. As
you can see, the API is basically a bunch of "FPSAFEARRAY", which I assume
stands for "floating point safe array".

__declspec(dllimport) double THING(
FPSAFEARRAY FAR* returnedStuff,
FPSAFEARRAY FAR* oneInput, FPSAFEARRAY FAR* anotherInput...,
LPSTR commands,
LPSTR errors);

My questions are fairly basic... most of the inputs are these arrays marked
"FAR*". Do I wrap these as ByRef? And is the array itself mapped onto a
Variant, or is there something more specific I should use? The LPSTR map onto
ByVal String? Errors is an output, so I think I'm actually wrong on that.

Maury