| View previous topic :: View next topic |
| Author |
Message |
tbruns
General User
Joined: 28 Jul 2006
Posts: 10
Location: Germany, Braunschweig
|
 Posted: Mon Oct 05, 2009 11:25 am Post subject: calc: user defined function working non-selected cells fails |
 |
|
Let's say I have a single selected Cell (say G9) in a calc-sheet and I would like to fill the column underneath (G9:G19) with data I have available .
I would like to do this with a user defined function which I would like use as a formula in the selected cell.
So the plan is: I write into G9 " =fillColumnWithData() "
and the user defined function fills in G10:G19 and returns a value for G9.
But, if I program such a function (c.f. below) and call it from a calc formula, it runs without error, but does not fill anything into the cells G10:G19, it only fills the return value of fillColumnWithData() into G9.
However, if I call the identical function from within the ooBasic-Gui (not from calc) from Main(), everything works fine, like expected.
Can anybody show me a solution and maybe give an explanation?
This is the testcase:
| Code: |
sub Main
fillColumnWithData()
end sub
function fillColumnWithData()
oDoc = ThisComponent
if isNull(oDoc) then Exit Function
oSels = oDoc.getCurrentSelection()
if Not(oSels.supportsService("com.sun.star.sheet.SheetCell")) then
msgbox("mark only one cell")
exit function
endif
oSheet = oDoc.getSheets().getByIndex(0)
for i = 0 to 20
oCell = oSheet.GetCellByPosition(8,9+i)
'## ThisComponent.CurrentController.Select(oCell)
oSheet.GetCellByPosition(8,9+i).setValue(3.5*i)
'## oCell.setString("Shit")
next
end function
|
|
|
| Back to top |
|
 |
JohnV
Administrator
Joined: 07 Mar 2003
Posts: 8995
Location: Lexinton, Kentucky, USA
|
| Posted: Mon Oct 05, 2009 2:39 pm Post subject: |
|
|
| A user defined function can only affect the cell it is called from and I have not been able to get a function that calls a sub to work either. |
|
| Back to top |
|
|
tbruns
General User
Joined: 28 Jul 2006
Posts: 10
Location: Germany, Braunschweig
|
| Posted: Tue Oct 06, 2009 12:18 pm Post subject: |
|
|
Is that really the final statement ?
If I look at functions like "=transpose(a1:c4)" it works just like I want mine to work.
However, it seems like I need to use "menu->insert->function->Array->transpose ..."
Is there a way to get my own function in the group of "insertable" array functions? |
|
| Back to top |
|
|
thom314
OOo Enthusiast
Joined: 03 Aug 2005
Posts: 186
Location: Denver, Colorado
|
| Posted: Tue Oct 06, 2009 6:25 pm Post subject: |
|
|
This request make absolutely no sense to me as written. I have to believe that there is more to this than stated. Such a function would only trigger once, when entered from the keyboard, and then never again. Do you realize that if you want to fill a range with a number all at once, you can (1) select the range, (2) type the number and (3) hit Alt-Shift-Enter and the entire range will be filled?
Alternatively, to fill with a series, (1) select the range, (2) use menubar Edit -> Fill -> Series
Never the less.... you could create a function and pass it the sheet name and cell range as a string and then operate on that range by (1) getting the sheet, (2) getting a cell cursor for the range, (3) modifying the cells.
Cell function => =FNTEST("Sheet1";"A10:A26")
Macro is:
| Code: | Function fnTest( sSheetName As String, sRange As String )
MsgBox sSheetName
MsgBox sRange
fnTest = 3.1428
End Function |
_________________
- Tom on Linux Mint |
|
| Back to top |
|
|
tbruns
General User
Joined: 28 Jul 2006
Posts: 10
Location: Germany, Braunschweig
|
| Posted: Wed Oct 07, 2009 8:11 am Post subject: |
|
|
ok, so I fill in some background information:
I have a data logger installed connected via ethernet.
The logger, when 'asked' by oocalc delivers a series of measurement data (the log of one day, 144 values). This series is supposed to be put in one column (like a vector).
I would like to implement that operation as a generic user callable function, which can be put in a cell of the spreadsheet and which fills the range of cells "below" starting from the cell where it is put.
To me this sounds very similar to matrix functions like "transpose()" that fill a whole range with values of the form "{=transpose(range)}"
In short I'm looking for a way to implement a
Cell function => {=getDataVectorfromLogger(date_of_day)}
Does that sound more sensible, now?
--------------
Thomas |
|
| Back to top |
|
|
JohnV
Administrator
Joined: 07 Mar 2003
Posts: 8995
Location: Lexinton, Kentucky, USA
|
| Posted: Wed Oct 07, 2009 10:13 am Post subject: |
|
|
That you need to do with a macro instead of a user defined function. I don't know how to get your data but the following uses the currently active cell as the starting point and fills down with the row number as the data. | Code: | Sub FillCells
oCell = ThisComponent.CurrentSelection
row = oCell.getCellAddress.Row
col = oCell.getCellAddress.Column
sht = oCell.getCellAddress.Sheet
Sheet = ThisComponent.Sheets(sht)
For I = 1 to 144
oCell.Value = row + 1
row = row + 1
oCell = Sheet.getCellByPosition(col,row)
Next
End Sub |
|
|
| Back to top |
|
|
tbruns
General User
Joined: 28 Jul 2006
Posts: 10
Location: Germany, Braunschweig
|
| Posted: Wed Oct 07, 2009 11:08 am Post subject: |
|
|
ok, that's where I actually started from and what I ment with:
| Quote: |
But, if I program such a function (c.f. below) and call it from a calc formula, it runs without error, but does not fill anything into the cells G10:G19, it only fills the return value of fillColumnWithData() into G9.
However, if I call the identical function from within the ooBasic-Gui (not from calc) from Main(), everything works fine, like expected.
|
What I did was, put the call to the function into Main like:
| Code: |
sub Main
getDataVectorfromLogger(date_of_day)
end sub
|
Which worked over the cellrange, too!
But if I put it in a macro, it is not autocalculated e.g. in the case when I change the "date_of_day".
The macro would just fill in the values and not update on changes of the arguments. |
|
| Back to top |
|
|
pitonyak
Administrator
Joined: 09 Mar 2004
Posts: 3622
Location: Columbus, Ohio, USA
|
| Posted: Thu Oct 08, 2009 8:45 am Post subject: |
|
|
| JohnV wrote: | | A user defined function can only affect the cell it is called from and I have not been able to get a function that calls a sub to work either. |
I believe that it is more accurate to say that it cannot modify other cells in the same sheet from which the macro is called. So, if you have a sheet named "LoggerSheet", if you call a user defined function from another sheet, then you can fill cells in the LoggerSheet, but this feels like the wrong way to do this. Adding a button that is not called in the context of a user defined Calc function call from a cell feels like it makes more sense.
_________________
--
Andrew Pitonyak
http://www.pitonyak.org/oo.php |
|
| Back to top |
|
|
JohnV
Administrator
Joined: 07 Mar 2003
Posts: 8995
Location: Lexinton, Kentucky, USA
|
| Posted: Thu Oct 08, 2009 10:34 am Post subject: |
|
|
| pitonyak wrote: |
I believe that it is more accurate to say that it cannot modify other cells in the same sheet from which the macro is called. So, if you have a sheet named "LoggerSheet", if you call a user defined function from another sheet |
Interesting, I obviously didn't know that. |
|
| Back to top |
|
|
thom314
OOo Enthusiast
Joined: 03 Aug 2005
Posts: 186
Location: Denver, Colorado
|
| Posted: Thu Oct 08, 2009 7:26 pm Post subject: |
|
|
It is with trepidation that I quibble with The Erudite Pitonyak  (just yesterday I once again pulled his venerable book from the bookshelf for consultation). As he notes, and much to my surprise, you apparently can not have a function update some arbitrary range of cells within the same sheet as the user function. Very odd!
But if a user function returns an array of values, you can indeed update multiple cells. For example:
| Code: | Function fnTest
fnTest = Array( 3,2,1 )
End Function |
If you call the function in an array context (i.e. Control-Shift-Enter) then a set of contiguous cells will be updated.
if you enter: =fnTest() with Control-Shift-Enter, three horizontal cells will be set.
if you enter: =transpose( fnTest() ) with Control-Shift-Enter, then three vertical cells will be set
Returning an array of values may be the solution that tbruns is looking for.
_________________
- Tom on Linux Mint |
|
| Back to top |
|
|
tbruns
General User
Joined: 28 Jul 2006
Posts: 10
Location: Germany, Braunschweig
|
| Posted: Fri Oct 09, 2009 12:08 am Post subject: |
|
|
This last information sounds very promissing, indeed.
I will have a closer look at the impact it has on my problem tonight (German time)
so far,
thank you very much
Thomas |
|
| Back to top |
|
|
JohnV
Administrator
Joined: 07 Mar 2003
Posts: 8995
Location: Lexinton, Kentucky, USA
|
| Posted: Fri Oct 09, 2009 5:25 am Post subject: |
|
|
thom314,
I learn yet more!
tbruns,
FWIW, you can force a user defined function to recalculate with Ctrl+Shift+F9. |
|
| Back to top |
|
|
|
