"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