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


"Peter T" <peter_t@discussions wrote in message
...
"Maury Markowitz" wrote
On Apr 8, 12:31 pm, "Peter T" <peter_t@discussions wrote:
the msg). I suspect Application Run may be the only way to call xll
functions in VBA.

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