Ask Your Question
0

How do I convert Excel BASIC Solver API calls to be compatible with LibreOffice ?

asked 2019-08-05 00:55:23 +0100

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

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2019-10-03 16:30:28 +0100

BSNatal gravatar image

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)

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

2 followers

Stats

Asked: 2019-08-05 00:55:23 +0100

Seen: 46 times

Last updated: Oct 03