Apache OpenOffice Community Forum

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?

'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.

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!

Hi,
First remark, your function definition is not syntactically correct (although ignored by Basic) 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. 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

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).