OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

calc: user defined function working non-selected cells fails

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
tbruns
General User
General User


Joined: 28 Jul 2006
Posts: 10
Location: Germany, Braunschweig

PostPosted: Mon Oct 05, 2009 11:25 am    Post subject: calc: user defined function working non-selected cells fails Reply with quote

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
View user's profile Send private message
JohnV
Administrator
Administrator


Joined: 07 Mar 2003
Posts: 9183
Location: Lexinton, Kentucky, USA

PostPosted: Mon Oct 05, 2009 2:39 pm    Post subject: Reply with quote

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
View user's profile Send private message
tbruns
General User
General User


Joined: 28 Jul 2006
Posts: 10
Location: Germany, Braunschweig

PostPosted: Tue Oct 06, 2009 12:18 pm    Post subject: Reply with quote

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
View user's profile Send private message
thom314
OOo Enthusiast
OOo Enthusiast


Joined: 03 Aug 2005
Posts: 186
Location: Denver, Colorado

PostPosted: Tue Oct 06, 2009 6:25 pm    Post subject: Reply with quote

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
View user's profile Send private message
tbruns
General User
General User


Joined: 28 Jul 2006
Posts: 10
Location: Germany, Braunschweig

PostPosted: Wed Oct 07, 2009 8:11 am    Post subject: Reply with quote

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
View user's profile Send private message
JohnV
Administrator
Administrator


Joined: 07 Mar 2003
Posts: 9183
Location: Lexinton, Kentucky, USA

PostPosted: Wed Oct 07, 2009 10:13 am    Post subject: Reply with quote

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
View user's profile Send private message
tbruns
General User
General User


Joined: 28 Jul 2006
Posts: 10
Location: Germany, Braunschweig

PostPosted: Wed Oct 07, 2009 11:08 am    Post subject: Reply with quote

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
View user's profile Send private message
pitonyak
Administrator
Administrator


Joined: 09 Mar 2004
Posts: 3655
Location: Columbus, Ohio, USA

PostPosted: Thu Oct 08, 2009 8:45 am    Post subject: Reply with quote

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
View user's profile Send private message Send e-mail Visit poster's website AIM Address
JohnV
Administrator
Administrator


Joined: 07 Mar 2003
Posts: 9183
Location: Lexinton, Kentucky, USA

PostPosted: Thu Oct 08, 2009 10:34 am    Post subject: Reply with quote

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
View user's profile Send private message
thom314
OOo Enthusiast
OOo Enthusiast


Joined: 03 Aug 2005
Posts: 186
Location: Denver, Colorado

PostPosted: Thu Oct 08, 2009 7:26 pm    Post subject: Reply with quote

It is with trepidation that I quibble with The Erudite Pitonyak Shocked (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
View user's profile Send private message
tbruns
General User
General User


Joined: 28 Jul 2006
Posts: 10
Location: Germany, Braunschweig

PostPosted: Fri Oct 09, 2009 12:08 am    Post subject: Reply with quote

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
View user's profile Send private message
JohnV
Administrator
Administrator


Joined: 07 Mar 2003
Posts: 9183
Location: Lexinton, Kentucky, USA

PostPosted: Fri Oct 09, 2009 5:25 am    Post subject: Reply with quote

thom314,

I learn yet more!

tbruns,

FWIW, you can force a user defined function to recalculate with Ctrl+Shift+F9.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group