[Solved] Macro to recalculate and display single cell

[gazelle]

I use several spreadsheets with dozens of sheets and sensitivity tables(multiple operations). For useful speed after cell updates I switch "automatic" recalculate off because otherwise I would wait minutes after each single entry. As I insert new data via macros I know exactly which dependant cells need updating and could include it in my macros. However, th eonly aoptions I saw so far are recalculate all or nothing. When I used to do this in EXCEL there was a "recaluate all exept tables", which solved my performance problem, but what can I do in open office?

With version 3.0 this has become areal nuisance. Let's say my macro inserts the formula "=ROWS(Sheet5!A23)" in Sheet1!A3, which then initially displays as BLANK. Next I search column Sheet1!A via VLOOKUP and find nothing whereas in previous versions it saw the value 23. So, a recalculate single cell would be an acceptable workaround.

[gazelle]

Meanwhile I discovered a solution and can now recalulate a single cell. Below I shall outline how to do that.

First I wanted to explain a bit more how what the problem looked like. For instance, I used uCell.Formula = "ROUND(-MYFUNCTION();2)" in my macro to enter it in a cell. Before recalulating this would either display as a blank cell or as "#NAME?". In other cells I used uCell.Formula=ROW($Sheet3.N58)" which always displayed as a blank cell before recalulating. In the past I used a manual method to force the update by going to the cell and editing the cell formula through appending a blank character which did not change the formula but forced reevaluation of the formula.

As a solution I found a call to a UNO routine which does what my manual method did before. I included that in a small macro to update recalculate a single cell and tested it successfully under windoes for calc versions 2.4.1 and 3.0. For those of you interested here is the lsiting of that macro:

sub UpdateZelle ( uCell as object )
' ----------------------------------------------------------------------
' Macro by gazelle
' V1 started: 14.Jan.2009 finnished: 15.Jan.2009
' read formula in target cell and update the displayed result
' ----------------------------------------------------------------------
dim oCell as object, oSheet as object ' current active cell
' uCell as object ' cell to be updated

oSheet = ThisComponent.CurrentController.ActiveSheet
oCell = ThisComponent.getCurrentSelection
ThisComponent.CurrentController.select(uCell) ' activate cell concerned

rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "StringName"
args2(0).Value = uCell.Formula ' get formula to be updated
dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args2())

' memo:
' uCell.Formula = „string as formla“ does not update the displayed result

ThisComponent.CurrentController.select(oCell) ' back to previously active cell

end sub