[libreoffice-users] Delayed Execution of a Formula in Calc

I am using LibreOffice 3.4.4 on Kubuntu 11.10.

I think my problem may be with an attempt by LO (and most likely with OOo as 
well) to optimize processing by immediately recalculating only those cells that 
are on the currently active sheet or those on other sheets that are _known_ to 
cause changes on the active sheet. Two examples of cells where immediate 
recalculation may be delayed are those in which a user written function or the 
INFO() function is involved. If this is the case, is there a way to set a 
preference to always recalculate everything? This should not hurt performance 
in a noticable way when dealing with a small document. I recognize that 
recalculating large complex spreadsheets can be a very intense operation so a 
warning associated with the selection of the option would be very appropriate.

In case I have misdiagnosed the cause of my grief, here is my problem and the 
circumstances surrounding it. In Sheet4.C5 I have a formula that is not getting 
executed when the cells it references get changed. Instead, it gets executed 
when the next automatic backup occurs after the change of status of the 
referenced cells, when F9 is pressed to recalculate the whole document, or when 
Sheet4 is manually selected for viewing. In no case that I have found so far is 
this action performed immediately. Since I change the cell and thus cause this 
formula to be activated just before I start driving, I end up closing the 
laptop cover before an automatic backup occurs. That puts the computer to sleep 
which makes the timestamp that is generated of little use for my purposes. I 
have tweaked the environment by setting the automatic backup cycle to one 
minute but that is only a kludge and in any case it still does not solve the 
problem in the case where I close the cover of the laptop computer and it goes 
to sleep.

This is the formula in Sheet4.C5. Note that it calls a function that I wrote in 
OOoBasic (aka StarBasic):

=IF(ISNUMBER(Sheet3.D5),IF(ISNUMBER(Sheet3.A5),"A",fnTStamp(2,0,ROW()-1)),"B")

Here is the code for my function fnTStamp():

Function fnTStamp(nSheet, nColumn, nRow)
' === Parameters nSheet, nColumn, and nRow specify the location of the cell
' ===   into which a static timestamp is to be stored.
' === The numbers are zero based.
' =========
' === Definitions for the local variables used within this Function
  Dim oDoc as object
  Dim oSheet as object
  Dim oCell as object
  Dim nFormat as integer
' === I don't know the proper type for the next variable so it is variant here
' === Neither string nor integer worked.
  Dim CharLocale
  Dim nFormatId as integer
' === Initialize variables oDoc, oSheet, and oCell for the target
  oDoc = thisComponent
  oSheet = oDoc.getSheets().getByIndex(nSheet)
  oCell = oSheet.getCellByPosition(nColumn, nRow)
' === Get the format id number for "HH:MM" for this document's locale
  CharLocale = oDoc.getPropertyValue("CharLocale")
  nFormatId = oDoc.getNumberFormats.queryKey("HH:MM", CharLocale, false)
  If nFormatId = -1 then
    nFormatId = oDoc.getNumberFormats.addNew("HH:MM", CharLocale)
  End If
' === Store the "HH:MM" formatted static timestamp into the target cell
' === Also set the format by which this timestamp is displayed
  oCell.Value = now
  oCell.setPropertyValue("NumberFormat", nFormatId)
' === Return the string "C" to the cell from which this function was called
  fnTStamp = "C"
End Function

Sheet3.D5 starts out with this formula that evaluates to either the value 0 or 
an empty string ("").

 =IF(C5="Reset",0,"")

I often overwrite that formula with a number. The normal content of Sheet3.D5 
is "". Sheet3.A5 starts out unformatted and totally empty. I key a number into 
Sheet3.D5 and tab out of that cell or cause its formula to evaluate to 0.0 by 
changing Sheet3.C5 to the string "Reset". Nothing appears to happen until the 
next automatic backup or until I tell LO to recalculate everything (F9). Then 
the time at that moment appears in Sheet3.A5.

The delayed processing of the formula does not make sense to me. Does anyone 
have a clue as to why this happens and, more to the point, how I can solve the 
problem.

In the production version of this document both Sheet3.D5:D98 and Sheet4.C5:C98 
are filled downward. It also contains similar formulas in Sheet4.C2:C93 
relating to cells in corresponding rows of Sheet2.

--
Jim



--
For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted