Jan Holst Jensen wrote:
Hi.
I am trying to figure out how to evaluate a formula string. If I have
selected a single cell I can get to the formula via
ThisComponent.GetCurrentSelection.Formula but I haven't been able to
find a way to evaluate that formula.
Excel VBA has the Evaluate() function - what is the equivalent in the
OpenOffice API ? I did look at com.sun.star.sheet.FunctionAccess but
that works only for a single function name and requires you to feed in
all parameters manually.
I am using this to build a function that can return more descriptive
error messages to the user when a formula returns "#VALUE!" - at least
when the formula involves code in my add-in.
Aha. A workaround is to use SetFormula which forces a re-evaluation of
the formula even when the new formula is identical to the old one.
Please, don't add a check for this to optimize performance of
SetFormula() :-).
Note that the code below only works when the offending function is
implemented in Basic. A formula of say "= 3 / 0" won't raise an
exception and be caught by the FormulaError handler.
Cheers
-- Jan
Dim SelectedCell as Variant
SelectedCell = ThisComponent.GetCurrentSelection
if not (SelectedCell.Type =
com.sun.star.table.CellContentType.FORMULA) then
MsgBox "Not a formula"
Exit Sub
end if
Dim FormulaString as String
FormulaString = SelectedCell.Formula
' Force re-calculation of formula.
On Error GoTo FormulaError
SelectedCell.SetFormula (FormulaString)
On Error GoTo 0
Exit Sub
GeneralError:
MsgBox Error$, MB_ICONSTOP
On Error GoTo 0
Exit Sub
FormulaError:
MsgBox "The following error occurred during formula evaluation: " &
Error$, MB_ICONEXCLAMATION
On Error GoTo 0
Exit Sub
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
