Wrapping a XLL as if it were a DLL

[Maury Markowitz]

I have a XLL mathematical library that I call though Application.Run
in Excel. This is a MAJOR performance bottleneck, and I am trying to
see if I can call it directly though a wrapper instead. I found a
discussion of how to call a XLL as if it were a DLL using xlcall32.dll
on the net, and have compiled this up and put the resulting dll into
the same directory as the XLL.

I then tried to wrap the XLL's function using Private Declare Function
inside Excel. However, this returns an error 53, File Not Found. I
tried adding the complete path to the XLL, but this had no effect.
This leads me to believe the error is misleading, and that it may mean
"dll not registered"?

Question1: Should I be able to use a complete path to find any dll no
matter if they are reged or not?

So then I tried registering the XLL, but this returns "LoadLibrary
failed. GetLastError returns 0x000036b1. According to the very few
hits on the 'net, this too is a completely generic message of dubious
value.

Question 2: Can an XLL be registered? As I understand it, XLL's are a
strict superset of DLLs.

Maury

 

[Maury Markowitz]

Literally only seconds after posting I realized that I had typed in
the path with ".dll" instead of ".xll". Changing the extension made
THAT part work at least. Now I'm getting this:

Error 49, Bad DLL calling convention.

I sort of expected I would get something like this, but just to be
sure, does this mean I have written down the Private Declare Function
parameter list incorrectly? Assuming this is the case...

The XLL in question is passed a series of inputs that are represented
as ranges on a sheet. We have adapted this to use 2D arrays of Double
instead, which seems to pass into Application.Run just fine. But
here's where I'm lost: should I declare the inputs like...

...., dates() as Double, conventions() as Double, ...

or

...., dates as Any, conventions as Any, ...

or even

...., dates as Variant, conventions as Variant, ...

or maybe...

...., dates() as Variant, conventions() as Variant, ...

I *sort of* understand the differences between these (well, not sure
about the last two) but I can't say I *really* understand the nuances.
Is something:

Dim dates() as Double

passed into calls as a Variant? Or Variant()? Or is there a difference
in calling conventions between these?

Thanks!

Maury

 

[Peter T]

I'm not sure you can declare xll functions as if a dll. In a quick test I
also get the same Error 49 message. Depending on the function, the error may
occur after the function has worked perfectly (on error resume next prevents
the msg). I suspect Application Run may be the only way to call xll
functions in VBA.

If you have the project files maybe you can rebuild as a dll and use Declare
Function etc.

Regards,
Peter T

 

[Maury Markowitz]

the msg). I suspect Application Run may be the only way to call xll
functions in VBA.

If this is the case, is there some "cost effective" way to do this in
VB or VBA/Access?

Maury

 

[Peter T]

If this is the case,

Don't take my report as definitive, only that it seems like that to me !

is there some "cost effective" way to do this in
VB or VBA/Access?

Presumably the xll functions were designed with use as cell formulas in
mind. Maybe you can take advantage of that if you have a lot to do, eg

Dim i as long
Dim arr, arrResult as Variant

qty = 10
ReDim arr(1 To qty, 1 To 1)

For i = 1 To qty
arr(i, 1) = i * 10 'populate arr
Next

Set ws = ActiveSheet

ws.Range("A1").Resize(qty).Value = arr
ws.Range("B1").Resize(qty).Formula = "=myXLL_foo(A1)"

ws.Calculate ' if calc not already automatic

arrResult = ws.Range("B1").Resize(UBound(arr)).Value

Regards,
Peter T

 

[Peter T]

Actually seems it is possible to declare an xll just like a dll after all

Sadly William Hooper's excellent site seems to have been terminated for
quite a while, but for anyone that has his demo functions (Anewxll) - I did
this:

Option Explicit
Declare Function Junk1 Lib "whooperX.xll" ( _
ByVal d1 As Double, _
ByVal d1 As Double) As Double

' // First worksheet function example : Junk1
' // Adds two numbers passed as doubles and returns a double
' double __stdcall Junk1( double d1, double d2)
' {
' return d1+d2;
' }

Sub TestJunk()
Dim d As Double
' to avoid hardcoding the xll's path can
' chdir to path of the xll,
ChDir ThisWorkbook.Path

d = Junk1(1.2, 2.5)
Debug.Print d ' 3.7

End Sub

What I had failed to do last time was include As Double for the function's
return type.

Regards,
Peter T

 

[Maury Markowitz]

Actually seems it is possible to declare an xll just like a dll after all

Sadly William Hooper's excellent site seems to have been terminated for
quite a while, but for anyone that has his demo functions (Anewxll) - I did
this:

This is excellent news Peter, I can't thank you enough! I was trying
to test this myself, but after downloading the XL API from MS I found
that the version will build XLLs that will not run in XL2003, at least
not with modification. Very frustrating, as you might imagine.

I have a request, if I may be so bold. Do you have Access? If so,
could you try the exact same code in Access to see what happens? You
WILL need to use the xlcall32.dll and place it in the same directory
as the xll. You can find this on the 'net very easily. You will also
need to type in the full path to the XLL...

Declare Function Junk1 Lib "c:\myxlldir\whooperX.xll"...

You realize that if this works you've just disproven MS's own
documentation?

Maury

 

[Peter T]

but after downloading the XL API from MS I found

that the version will build XLLs that will not run in XL2003,

I didn't even know about the "XL API", maybe you can get hold of VisualC++

I have a request, if I may be so bold. Do you have Access?

It's bust! But I tried in both Word and VB6, can't get it to work in either
of these. This is what I did, let me know if I missed something

- I already have a number of copies of xlcall32.dll (32kb v5.0) and placed
one in the same folder as the xll.

- I included the full path to the xll in the declaration, to double check I
ran in Excel with the CurDir NOT that of the xll, and as before it worked
fine. FWIW the xll was not 'loaded' in Excel like an addin. As before worked
fine.

Ran exact same code in both Word & VB6; in both I get Error 48 File not
found C:\path\whooperX.xll
Yes I'm sure the path is correct and ChDir to that of the xll makes no
difference.

Maybe you have an explanation ?

You realize that if this works you've just disproven MS's own
documentation?

It wouldn't be the first time but I'm not sure. I couldn't get all the
functions in the xll to work. That may well be due to getting some parts of
the declaration wrong or due to the function itself. I couldn't for example
get this one to work -

//////////////////////////////////////////////////////////////
// Function by William Hooper www.whooper.co.uk
// 2d Lookup function
/////////////////////////////////////////////////////////////
LPXLOPER __stdcall Lookup2d( LPXLOPER xRange, LPXLOPER xRow, LPXLOPER xCol )

Also, William Whooper in his own demo file instructs to use Application.Run
in VBA

If the dll type declaration can be made to work I can well see the
attraction. Calling the simple xll function directly in a long loop vs
Application.Run was a fraction of a second vs a coffee break!

Regards,
Peter T

Actually seems it is possible to declare an xll just like a dll after all

Sadly William Hooper's excellent site seems to have been terminated for
quite a while, but for anyone that has his demo functions (Anewxll) - I did
this:

This is excellent news Peter, I can't thank you enough! I was trying
to test this myself, but after downloading the XL API from MS I found
that the version will build XLLs that will not run in XL2003, at least
not with modification. Very frustrating, as you might imagine.

I have a request, if I may be so bold. Do you have Access? If so,
could you try the exact same code in Access to see what happens? You
WILL need to use the xlcall32.dll and place it in the same directory
as the xll. You can find this on the 'net very easily. You will also
need to type in the full path to the XLL...

Declare Function Junk1 Lib "c:\myxlldir\whooperX.xll"...

You realize that if this works you've just disproven MS's own
documentation?

Maury