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?
Array function result not updating in sheet
Array function result not updating in sheet
- Attachments
-
- testfunctions.ods
- This file contains two-custom defined functions. It illustrates the comment above.
- (8.35 KiB) Downloaded 463 times
Re: Array function result not updating in sheet
'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.
=testthreex()*(5;3;1) should do the job in array context if your function would return a 2-dimensional array.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Array function result not updating in sheet
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!
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!
-
- Volunteer
- Posts: 1160
- Joined: Mon Oct 08, 2007 1:26 am
- Location: France, Paris area
Re: Array function result not updating in sheet
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
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)
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
- 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
Re: Array function result not updating in sheet
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).
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).