Re: Array Declaration Problem ??

Hi Dana;

Sorry to bother you again, but problem solved at last!!
Here's a summary:

1) On the w/s:
cell B8::3
cell B9:: myTrue
cells B11:B14:: numerical values
cells C11:C14:: numerical values
cells I11:I13:: array formula {=Zroots2(B11:B14,C11:C14,B8,B9)}

Option Base 1
Option Explicit
Function Zroots2 (ar As Range, ai As Range, m As Integer, polish As String)
As String()
Dim EPS As Double
Dim i As Integer, jj As Integer
Dim b As String, c As String
Dim j As Integer, its As Integer
Dim x As String
ReDim Roots(m) As String
ReDim a(m+1) As String, ad(m+1) As String
'.........................................my code1
Zroots2 = Roots()
End Function

3) In Function Zroots2(), array Roots() is calculated correctly, say, [1+i],
[2+2i], [3+3i].
But the array function returned a single value [1+i] to the full range
I11:I13, instead of returning the array 3 results to the 3 vertical cells.

4) In Function Zroots2(), replacing the statement:
Zroots2 = Roots()
with
Zroots2 = WorksheetFunction.Transpose(Roots())
doesn't produce compile error but returns #VALUE! to cells I11:I13

5) Having instead the Transpose function in the array formula on the w/s:
cells I11:I13:: array formula {=Transpose(Zroots2(B11:B14,C11:C14,B8,B9))}
and leaving the Function Zroots2() statement unchanged:
Zroots2 = Roots()
S O L V E S the problem.

I've successfully tested the VBA procedure for 1D polys up to degree 20,
with real and complex coefficients.
If there's interest, I'd be glad to post a demo w/b with the VBA procedure.

Kind regards.


"monir" wrote:

Hi;

For clarification, the 3rd para. of my latest reply should read:
"The only remaining difficulty is as follows. The array results are
calculated correctly in the array Function Zroots2(), but only the 1st value
of the 1D array Roots results is returned to the col range of cells (instead
of returning the array of results)."

Regards.


"Dana DeLouis" wrote:

The only remaining difficulty is as follows. The results are calculated
correctly in the array Function Zroots2(), but only the 1st element of the
array is returned to the range of cells (instead of returning the array of
results).

Just guessing, but if the results to the work*** are a vertical array, then just transpose them.
For this simple demo, select 3 vertical cells, and Ctrl+shift+Enter the following:
{ =Demo() }

You should see all 3 results.

Function Demo()
Dim V
'Your Array of values
V = Array(2, 4, 6)
Demo = WorksheetFunction.Transpose(V)
End Function

I can't get the entire code from Numerical Recipes.
I'd love to get a copy, and check out your code. I'm curious on the theory behind that code.

--
HTH :>)
Dana DeLouis


"monir" <monir@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:FCC55C3E-6F2A-42AD-BF91-FEB83CD35AB8@xxxxxxxxxxxxxxxx
Hi Dana;

Thank you again and I would be glad to send you a copy of the w/b.

I've ironed out almost ALL the problems with the procedure, and I'm getting
very encouraging results (the expected results).
In addition to using the VBA atpvbaen.xls library to handle Complex
variables by making w/s Complex functions available in vba (your brilliant
idea!), I've changed the input array argument in the array Function Zroots2()
from a 2D array to two 1D arrays.

The only remaining difficulty is as follows. The results are calculated
correctly in the array Function Zroots2(), but only the 1st element of the
array is returned to the range of cells (instead of returning the array of
results).

Here's how the relevant sections of the array Function Zroots2() looks now.
Notice that the function is declared as String and the array variable Roots()
is also declared as String. I tried to declare the function Zroots2 and
variable Roots as Variant, but realized that a Variant can't include String
data type.

Option Base 1
Option Explicit
Function Zroots2(ar As Range, ai As Range, m As Integer, polish As String)
As String()

Dim EPS As Double
Dim i As Integer, jj As Integer
Dim b As String, c As String
Dim j As Integer, its As Integer
Dim x As String
Dim Roots(100) As String
Dim a(101) As String, ad(101) As String

EPS = 0.000001

For j = 1 To m + 1
a(j) = complex(ar(j), ai(j))
ad(j) = a(j)
Next j

For j = m To 1 Step -1
x = complex(0, 0)
Call Laguer2(ad, j, x, its)
'.........................................my code1
Next j
For j = 1 To m
Call Laguer2(a, m, Roots(j), its)
Next j
'.........................................my code2
For j = 2 To m
x = Roots(j)
For i = j - 1 To 1 Step -1
'.........................................my code3
Roots(i + 1) = Roots(i)
Next i
'.........................................my code4
Next j

Zroots2 = Roots()
End Function

Any suggestions ?? Thanks again.



"Dana DeLouis" wrote:

Feel free to send my your workbook.
I'd be glad to take a look at it.
- -
Dana DeLouis

1) The FORTRAN code of Zroots() and Laguer() comes from "Numerical
Recipes"
by Press et al, pages 366 & 367, as I correctly referenced in my earlier
posts.
The Fortran version works perfectly and as desired in all situations,
and I
have hade no problems with it whatsoever!

2) I posted the entire Fortran code of Zroots() and Laguer() here
earlier
(total ~ 60 lines), hoping some experts in VBA would help in its
conversion
to vba. I subsequently decided to convert the code myself to VBA and
have
since posted one of my latest attempts (not sure which one!), which
returns
#VALUE!

<snip>

.