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:
dev-unsubscribe@...
For additional commands, e-mail:
dev-help@...
