application.run for returning arrays
 

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?

application.run for returning arrays
 

Hi levent,

levent wrote:
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)

It is definitely possible to return an array via Application.Run, so the
problem may stem from the way it's coded. Can you please provide the code
of the function and the calling procedure so we can try to help you get it
working?

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

application.run for returning arrays
 

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?

application.run for returning arrays
 

i have tried to use what you suggested with the functions
of Matrix.xla. And it worked for some of them. However,
for others, it goes into the matrix.xla programs and gives
some errors.

i don't know if one could tell why just by looking at
this, i could also send the relevant code for Matrix.xla.
but it is not mine and it is not very short. (so how
should i send it?)


Sub Main()
Dim A(1 To 5, 1 To 5)
Dim ws As Worksheet

Set ws = Worksheets("Markov ex")
For i = 1 To 5
For j = 1 To 5
A(i, j) = ws.Cells(i + 2, j)
'MsgBox A(i, j)
Next j
Next i

c = Application.Run("MATRIX.XLA!MatCharPoly", A) 'DOES
NOT WORK
'should return VECTOR
'object required ERROR
'in MATRIX.XLA,
'If Application.Caller.Rows.Count 1 Then

s = Application.Run("MATRIX.XLA!MatEigenValue_QR",
A) 'DOES WORK
'returns 5x2 works FINE


Dim s1 As Variant
'first row of s
s1 = Array(s(1, 1), s(1, 2))


ev1 = Application.Run("MATRIX.XLA!MatEigenVector_C",
A, s1) 'DOES NOT WORK
'should return 5x2
'subscript out of range ERROR
'Lr = Eigenvalue(1, 1)

End Sub
-----Original 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?

.