In a workbook name RetArray.xls I put in the following function

Public Function ReturnArray(varr())
Dim varr1() As Long
ReDim varr1(LBound(varr) To UBound(varr))
For i = LBound(varr) To UBound(varr)
If IsNumeric(varr(i)) Then
varr1(i) = varr(i) * varr(i)
End If
Next
ReturnArray = varr1

End Function


In another workbook, I ran this code:

Sub Tester1()
Dim va(1 To 3)
va(1) = 10
va(2) = 100
va(3) = 1000
a = Application.Run("RetArray.xls!ReturnArray", va)
For i = LBound(a) To UBound(a)
Debug.Print i, a(i)
Next

End Sub

That worked fine for me. The variable (a above) must be a variant variable.

--
Regards,
Tom Ogilvy

"levent" wrote in message
...
hi

with regard to accessing excel functions from VBA,
i have learned (at this forum) that you can do the
following:

a = Application.Run("ATPVBAEN.XLA!IMPRODUCT", A, b)

(when the excel function returns a single value)

however, when i try to do this for an excel function that
returns an ARRAY, it does not work. (there is an add-in
that does matrix operations e.g. matrix inversion, so both
inputs and output is a two-dimensional array)

is there a way to do this without having to dump things
into excel and input from excel again?