Re: Array Declaration Problem ??

Hi Dana;

1) If you re-examine my recently posted entire code (with sample w/s input
and expected returned results, 6/23/2008 8:20 AM PST), you will notice that
the declaration ReDim a(m+1,2) was commented out in Function Zroots().

2) Keep in mind that I'm trying to (simply) pass the range B11:C14 to
Function Zroots() and return the results by the array function to cells
I11:J13. The main procedure is the Function Zroots() which calls Sub
Laguer().

3) I've also tried the following with no avail, the array function Zroots()
still returns #VALUE!: (see items 4, 5, 6 below)
Function Zroots(a As Range, m As Integer, polish As String) As Variant()
Dim j As Integer, its As Integer
Dim ad As Variant
Dim x(2) As Double
' ReDim a(m + 1, 2)
Dim Roots() As Variant
ReDim ad(m + 1, 2)
............................my code1...........
For j = 1 To m + 1
ad(j, 1) = a(j, 1)
ad(j, 2) = a(j, 2)
Next j

For j = m To 1 Step -1
x(1) = 0#
x(2) = 0#
Call Laguer (ad, j, x, its)
' ............................my code2...........
Next j
' ............................my code3...........
Zroots = Roots
End Function

4) Could it be that the concept of calling a subroutine from within a
function is the problem here ??
I don't recall ever using such procedure, but I could be wrong. It's always
the other way around, where the subroutine is the primary procedure calling
the UDF.

5) Interestingly enough, the MS ref. book: "Microsoft Excel 2000 VBA
Fundamentals", Microsoft Press, page 187, reads:
"NOTE: A function used in a work*** cell can include only those actions
that can be executed while Excel is recalculating a work***. Actions such
as opening files or displaying message boxes can be included in functions
that are called from macros, if you include them (or similar) in a function
that's called from a work***, the function simply returns the #VALUE! error
value."

6) One may interpret the above MS Note to mean for example that calling Sub
Laguer() from Function Zroots() "used in a w/s cell" IS NOT an "action" that
can be "executed while Excel is recalculating a work***" and "the function
simply returns the #VALUE! error value.".
Or, is it and I'm misinterpreting the MS Note ??

Any thoughts ??

Kind regards.


"Dana DeLouis" wrote:

It seems to me that by declaring the incoming array "a" using:
ReDim a(m+1,2)
it sets all elements of matrix "a" to 0.0. The Immediate Window for
MyRoots() confirms that. I even tried: ReDim Preserve a(m+1,2) with no
change.

Hi. I'm looking at ReDim a(m+1,2)

I may be wrong, but I think you could skip this procedure because it looks
like you are loading it into ad()

Anyway, just for discussion, you can only "ReDim Preserve" the last index.
Here is a workaround to show you one idea.
Enter on a work*** the function "MyRoots() and select a 4 Row*2 Column
as the input range.
Then step thru this code.
The Input is a range, and the first thing we do is clean it up and only
use the values.

Function MyRoots(Rng)
Dim A
'Just use values

'A is 4 * 2
A = Rng.Value

With WorksheetFunction
'A is now 2 * 4
A = .Transpose(A)
'Redim last index to 5
ReDim Preserve A(1 To 2, 1 To 5)
'put back to 5 * 2
A = .Transpose(A)
End With
End Function

Anyway, hope this helps a little.
--
Dana DeLouis


"monir" <monir@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F360D4A4-8217-49E8-A7BA-508B83FBBB24@xxxxxxxxxxxxxxxx
Hi Bob;

Thanks again. I've done the correction:

Function MyRoots (a As Variant, m As Integer, polish As String)
Dim Roots As Variant
Dim ad As Variant
Dim j As Integer, its As Integer
Dim x(2) As Double

ReDim a(m + 1, 2)
ReDim Roots(m, 2)
ReDim ad(m + 1, 2)
' ..............................my code............
For j = 1 To m + 1
ad(j, 1) = a(j, 1)
ad(j, 2) = a(j, 2)
Next j
' ..............................my code............
Call Laguer (ad, j, x, its)
' ..............................my code............
Roots(j, 1) = x(1)
Roots(j, 2) = x(2)
MyRoots = Roots
End Function

The array function MyRoots() returns all zeros (same as was posted
originally).
It seems to me that by declaring the incoming array "a" using:
ReDim a(m+1,2)
it sets all elements of matrix "a" to 0.0. The Immediate Window for
MyRoots() confirms that. I even tried: ReDim Preserve a(m+1,2) with no
change.
(If you prefer, I would be glad to post or send you the function
MyRoots()
and its sub Laguer() code. Total ~ 40 lines.)

Regards.

"Bob Phillips" wrote:

Sorry, missed the parameter

Function MyRoots(a As Variant, m As Integer, polish As String)
Dim Roots As Variant
Dim ad As Variant
Dim j As Integer, its As Integer
Dim x(2) As Double

ReDim a(m + 1, 2)
ReDim Roots(m, 2)
ReDim ad(m + 1, 2)
'> ..............................my code............
For j = 1 To m + 1
ad(j, 1) = a(j, 1)
ad(j, 2) = a(j, 2)
Next j
'> ..............................my code............
Call Laguer(ad, j, x, its)
'> ..............................my code............
Roots(j, 1) = x(1)
Roots(j, 2) = x(2)
MyRoots = Roots
End Function



--
__________________________________
HTH

Bob

"monir" <monir@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F36BF0A8-CAFE-4D7F-9FE6-AA2F93D0B099@xxxxxxxxxxxxxxxx
Hi Bob;

Thank you kindly for your prompt reply.
I've tried your suggestion.

Function MyRoots(a, m As Integer, polish As String)
Dim a As Variant, Roots As Variant
Dim ad As Variant
Dim j As Integer, its As Integer
Dim x(2) As Double

ReDim a(m + 1, 2)
ReDim Roots(m, 2)
ReDim ad(m + 1, 2)
..............................my code............
For j = 1 To m + 1
ad(j, 1) = a(j, 1)
ad(j, 2) = a(j, 2)
Next j
..............................my code............
Call Laguer (ad, j, x, its)
..............................my code............
Roots(j, 1) = x(1)
Roots(j, 2) = x(2)
MyRoots = Roots
End Function

It produces the compile error: "Duplicate declaration in current
scope"
with
reference to the "a" declaration in:
Dim a As Variant, Roots As Variant

Any suggestion ??

Regards.

"Bob Phillips" wrote:

I am not clear what your code is doing, especially the ,,, my code
...
bits,
but your array declaration is definitely wrong.

Try this

Function MyRoots(a, m As Integer, polish As String)
Dim a As Variant, Roots As Variant
Dim ad As Variant
Dim j As Integer, its As Integer
Dim x(2) As Double

ReDim a(m + 1, 2)
ReDim Roots(m, 2)
ReDim ad(m + 1, 2)
For j = 1 To m + 1
ad(j, 1) = a(j, 1)
ad(j, 2) = a(j, 2)
Next j
Call Laguer(ad, j, x, its)
Roots(j, 1) = x(1)
Roots(j, 2) = x(2)
MyRoots = Roots
End Function


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in
my
addy)

"monir" <monir@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8636FAA1-9C87-4924-AFE6-E4453F25E894@xxxxxxxxxxxxxxxx
(This is a cross-post)

Hello;

I'm trying to correctly pass the range B11:C14 to the Function
MyRoots()
and
return the results by the array function to cells I11:J13.

Notice the use of ReDim. Declaring Dim a(m+1,2) As Double in
Function
MyRoots() would produce a compile error: "Constant expresion
required",
and
also I couldn't declare "a" as a 2D dynamic array!

I suspect the array variables declaration in the following example
is
the
problem.
The array Function MyRoots() incorrectly returns 0.0 results to
cells
I11:J13.

cell B8::3
cell B9: myTrue
cells B11:C14 numerical values
cells I11:J13:: array function {=MyRoots(B11:C14, B8, B9)}

Function MyRoots (a, m As Integer, polish As String)
ReDim a(m + 1, 2) As Double
ReDim roots(m, 2) As Double
Dim j As Integer, its As Integer
Dim x(2) As Double
ReDim ad(m + 1, 2) As Double
......................................my code............
For j = 1 To m + 1
ad(j, 1) = a(j, 1)
ad(j, 2) = a(j, 2)
Next j
......................................my code............
Call Laguer (ad, j, x, its)
......................................my code............
roots(j, 1) = x(1)
roots(j, 2) = x(2)
......................................my code............
MyRoots = roots
End Function

Sub Laguer (a, m, x, its)
Dim x1(2) As Double
......................................my code............
x(1) = x1(1)
x(2) = x1(2)
......................................my code............
Exit Sub
End Sub

Your expert help would be greatly appreciated.

Regards.







.