LibreOffice Calc Input Box and mathematical expressions

Question

I'd like to use an input box to get a mathematical expression, (1+7, 6-2, 1+5-6, etc) and use a macro to evaluate that expression. I've tried to use Expression(), but that doesn't seem to do anything. I assume that's not part of LibreOffice BASIC...

Here's what I've tried.

Function GetUserInput() As Double
    Dim InputValue As Variant

    ' Prompt the user for input
    InputValue = InputBox("Enter a number or mathematical expression:", "Input Required")

    ' Treat empty input as 0
    If InputValue = "" Then
        GetUserInput = 0
    Else
        ' Attempt to evaluate the input as a mathematical expression
        On Error Resume Next
        GetUserInput = Evaluate(InputValue)
        On Error GoTo 0

        ' Check for errors in evaluation
        If Err.Number <> 0 Then
            MsgBox "Invalid expression. Assuming 0.", vbExclamation, "Invalid Input"
            GetUserInput = 0
        End If
    End If
End Function

Whenever I enter an expression it just says 'Invalid Input'.

edit--

I need to get the formula through an input box, not directly from a cell. This is all driven from a single button click, which askes for the user input, then uses that input with more predictable data, already in the spredsheet. In most cases, a single integer is entered, but on occasion, I need to be able to catch a simple formula of addition and/or subtraction.

Well, need isn't accurate. I just want to see if I can get it to work. It would just be a convenience.

crawling through the joke spreadsheet (I assume that's what you're pointing to) I'll need to rip apart the string to get the numbers and the operators? So there's no direct way of handling this?

Answer 1

The GetUserInput() function can be very simple - get a string from the user via InputBox() and try to calculate the value:

Function GetUserInput() As Variant
Dim InputValue As String
    InputValue = InputBox("Enter a number or mathematical expression:", "Input Required")
    GetUserInput = evalFormula(InputValue)
End Function

All the work of calculating a value from a text string can be done by the following function:

Function evalFormula(sTextFormula As String) As Variant 
Dim sFullFormula As String
Dim oTempDoc As Variant, oWrkCell As Variant 
    GlobalScope.BasicLibraries.isLibraryLoaded("Tools")
    oTempDoc = CreateNewDocument("scalc")
    oWrkCell = oTempDoc.getSheets().getByIndex(0).getCellByPosition(0, 0)
    sFullFormula = Trim(sTextFormula)
    If Left(sFullFormula, 1) <> "=" Then sFullFormula = "=" & sFullFormula
    oWrkCell.setFormula(sFullFormula)
    oTempDoc.calculate()
    If oWrkCell.getError = 0 Then
        evalFormula = oWrkCell.getDataArray()(0)(0)
    Else 
        evalFormula = oWrkCell.getString()
    EndIf 
    oTempDoc.close(True)
End Function

Test the code:

Sub testInputBox
    MsgBox "Result is " & GetUserInput()
End Sub