Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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] |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA arrays | Excel Discussion (Misc queries) | |||
Arrays | Excel Discussion (Misc queries) | |||
macro to close excel application other than application.quit | Excel Programming | |||
help with arrays | Excel Programming | |||
application.quit will not shut off application | Excel Programming |