Array function result not updating in sheet

[rmcd]

I must be missing something obvious. I have defined this function:

function testthreex(a,b,c) as array
x=array(a^2,b^2,c^2)
testthreex=x()
end function

I call the function as an array function. Here is the behavior:

If I edit the function code in the basic editor, the next time it is called from the spreadsheet, it properly calculates and displays the correct output in the worksheet.

If I do not edit the function in the basic editor, but change the inputs, it does not update in the spreadsheet.

Specifically, if I edit the function in basic and call testthreex(a1;a2;a3), where a1=5, a2=3, a3=1, it displays 25 9 1 as the output in the worksheet. If I then change cell a1 to 6, it continues to display 25 9 1. I can confirm by setting a breakpoint in the basic code that a) the function is evaluated and b) it is evaluated correctly. The result is just not passed to the spreadsheet. I am running OO in Windows XP.

The other odd thing is that sometimes when I enter the function in a cell, OO parses it like " 'testthreex'*(5;3;1) ", so it does not recognize that it is a function.

A sample spreadsheet is attached. Any suggestions?

[Villeroy]

'testthreex'*(5;3;1) tries to multiply a labeled range 'testthreex'. The single quoting is added because of option Tools>Options>Calc>Calculate:"Automatically find labels".
=testthreex()*(5;3;1) should do the job in array context if your function would return a 2-dimensional array.

[rmcd]

Hello and thank you once again for the help.

I modified the function as follows (is this what you mean by returning a 2-d array?), but it still does not update in the worksheet:

function testthreex(a,b,c) as array
dim x(2,2) as double
x(0,0)=a^2
x(1,0)=b^2
x(2,0)=c^2
x(0,1)=x(1,0)
x(0,2)=x(2,0)
x(1,1)=0
x(1,2)=0
x(2,2)=0
x(2,1)=0
testthreex=x()
end function

And thank you for explaining the automatic insertion of single quotes! That was driving me crazy!

[B Marcelly]

Hi,
First remark, your function definition is not syntactically correct (although ignored by Basic)

Code: Select all

function testthreex(a,b,c) as array
' the term array is not significant here
' you could as well write
function testthreex(a,b,c) as hello123

' a correct way is to write
function testthreex(a,b,c) as variant
' or, even, because variant is implicit
function testthreex(a,b,c)

Now for your function. I suppose you want to create an array function for matrix computation.
The general case is when you have a rectangular matrix.
Here is an array function that adds 100 to each matrix element.

Code: Select all

function add100(a as variant) as variant
dim x as long, y as long
dim ym as long, xm as long
ym = UBound(a,1)
xm = UBound(a,2)
' Lower bounds are always 1
dim r(1 to ym, 1 to xm) as variant
for y = 1 to ym
  for x = 1 to xm
    r(y,x) = a(y,x) +100
  next
next
add100 = r()
end function

Example of call :
- into cell F18 : =ADD100(A18:C19) and validate by Ctrl-Shift-Enter
To modify the formula, select the whole matrix result before editing, then validate by Ctrl-Shift-Enter

______
Bernard

[rmcd]

Thank you! Deleting "as array" in the function definition was the solution.

This is what confused me (from the staroffice 8 programming manual, p. 40):

*********
"The return value of a function can be any type. The type is declared in the same way
as a variable declaration:

Function Test As Integer
’ ... here is the actual code of the function
End Function

If the specification of an explicit value is stopped, the type of the return value is
assigned as variant."
**********

I had tried to declare "as array of double" and this generated an error so I used "as array". Not correct, as you point out.

As for what I am doing, I am creating a function that takes a few inputs, does a lot of computation, and returns multiple results at once (to avoid duplicating the computation).