Coercing a variant into an array
 

Folks

I have the following problem

I need to call a function stored into a (third part) DLL that take as
parameter an array of doubles, i.e. it's declaration reads as

Function someFancyFunction(Todays_Date As Date, Data_Array() As
Double) as double

I new to write a "wrapper" VBA function that would be called as a
worksheet function with some parameters being named ranges and that
would eventually call my external, i.e. I'd like to write a formula
like

=SOME.FANCY.WRAPPER(TODAY(),MyNamedRange)

So my question is… how do I do this in an efficient way (an NO, I
can't call directly the DLL in this specific instance) ?

It seems that I have to declare my wrapper as

Public function SOME.FANCY.WRAPPER( prmDate as Date, prmRange as
variant) as double

And as such I will need to parse my variant and build the array by
code…

Is this the way to go or am I missing something obvious ?

Any pointer welcome

Regards

--alexT

Coercing a variant into an array
 

Alex,

I dont think you'll get it to work unless you create and fill
a true double array

Function MyWrapper(NamedRange As Range) As Double
With NamedRange
Dim r&, c&, dArr#()

ReDim dArr(1 To .Rows.Count, 1 To .Columns.Count)
On Error Resume Next
For r = 1 To .Rows.Count
For c = 1 To .Columns.Count
dArr(r, c) = .Cells(r, c)
Next
Next
End With
MyWrapper = MyFancy(Now, dArr)
End Function


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Alex T wrote :

Folks

I have the following problem

I need to call a function stored into a (third part) DLL that take as
parameter an array of doubles, i.e. it's declaration reads as

Function someFancyFunction(Todays_Date As Date, Data_Array() As
Double) as double

I new to write a "wrapper" VBA function that would be called as a
worksheet function with some parameters being named ranges and that
would eventually call my external, i.e. I'd like to write a formula
like

=SOME.FANCY.WRAPPER(TODAY(),MyNamedRange)

So my question is… how do I do this in an efficient way (an NO, I
can't call directly the DLL in this specific instance) ?

It seems that I have to declare my wrapper as

Public function SOME.FANCY.WRAPPER( prmDate as Date, prmRange as
variant) as double

And as such I will need to parse my variant and build the array by
code…

Is this the way to go or am I missing something obvious ?

Any pointer welcome

Regards

--alexT