I’d like to make an XLS file that contains BASIC code so that it works on LibreOffice Calc.
I’m getting a BASIC Runtime Error 35 SolverOk from the following line:
SolverOk SetCell:="$C$42", MaxMinVal:=1, ValueOf:=“0”, ByChange:= _
My guess is that the Solver API in LibreOffice does not contain the function SolverOk.
This won’t be the last problem of this nature that I run into as I attempt to get this file to work with LibreOffice.
Is there a conversion guide ???
Thanks
Solver does not appear to be well supported in LibreCalc Basic and the calls are totally different from Excel VB.
Attached code I got from another forum which works to maximise a value. Input cells are A1; B1 and C1 and D1 is = A1+B1-C1
sub solvermacro
rem ----------------------------------------------------------------------
rem define variables
dim document as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService(“com.sun.star.frame.DispatchHelper”)
rem ----------------------------------------------------------------------
rem dispatcher.executeDispatch(document, “.uno:SolverDialog”, “”, 0, Array())
Dim Variables(2) as Object
Dim Constraint_0 as New com.sun.star.sheet.SolverConstraint
Dim Constraint_1 as New com.sun.star.sheet.SolverConstraint
Dim Constraint_2 as New com.sun.star.sheet.SolverConstraint
Dim Constraints(2) as Object
smgr = GetProcessServiceManager()
solv = smgr.createInstance(“com.sun.star.sheet.Solver”)
ConstrOperator1 = com.sun.star.sheet.SolverConstraintOperator.LESS_EQUAL
ConstrOperator2 = com.sun.star.sheet.SolverConstraintOperator.GREATER_EQUAL
oDoc = ThisComponent
solv.Document = oDoc
Sheet = oDoc.Sheets(0)
ObjectiveCell = Sheet.getCellByPosition(3,0) 'This cell has the formula =A1 + B1 - C1
solv.Objective = ObjectiveCell.CellAddress
VariCell0 = Sheet.getCellByPosition(0,0)
Variables(0) = VariCell0.CellAddress
VariCell1 = Sheet.getCellByPosition(1,0)
Variables(1) = VariCell1.CellAddress
VariCell2 = Sheet.getCellByPosition(2,0)
Variables(2) = VariCell2.CellAddress
solv.Variables = Variables()
Constraint_0.Left = Sheet.getCellByPosition(0,0).CellAddress
Constraint_0.Operator = ConstrOperator1
Constraint_0.Right = 6
Constraints(0) = Constraint_0
Constraint_1.Left = Sheet.getCellByPosition(1,0).CellAddress
Constraint_1.Operator = ConstrOperator1
Constraint_1.Right = 8
Constraints(1) = Constraint_1
Constraint_2.Left = Sheet.getCellByPosition(2,0).CellAddress
Constraint_2.Operator = ConstrOperator2
Constraint_2.Right = 4
Constraints(2) = Constraint_2
solv.Constraints = Constraints()
solv.Maximize = True
solv.Solve()
Print solv.ResultValue
end sub
I cannot for the life of me find out how to solve for anything except maximise and minimise, i.e. solve for a value. (This would be the equivalent of the ValueOf=“0” in the SolverOK call)