[Solved] Launching solver from macro and stagnation

Creating a macro - Writing a Script - Using the API

[Solved] Launching solver from macro and stagnation

Postby MZ_Bard » Fri May 01, 2020 1:14 pm

Hi all! I'm using a NLP DE-PS solver in calc of Libreoffice both on Windows 10 and Debian Linux to remotely execute some dynamically generated problems and I'm quite satisfied with the results so far. The only problem is that when I launch the solver from the dialog windows it correctly stops due to stagnation when satisfying the constraint of objective=0, while if I launch the solver via macro (solv = CreateUnoService("com.sun.star.comp.Calc.NLPSolver.DEPSSolverImpl") ) replicating the same exact setting and scenario the solver works but stagnation is never reached, it doesn't even seem to be processed at all (setting a very low stagnation limit and/or a very high tolerance has no effect whatsoever). It gets to the correct result, but the stagnation meter doesn't move and the solver doesn't stop due to stagnation. This also happens with com.sun.star.comp.Calc.NLPSolver.SCOSolverImpl, and simplifying the problem or removing all settings doesn't seem to affect it either. Am I missing some implementation or doing something wrong?

I tried looking for any discussion on this exact issue but I found none, really sorry if there was one.

Here's a snippet of my code:
Code: Select all   Expand viewCollapse view
REM  *****  BASIC  *****
Option VBASupport 1
Sub Main

dim document as object
dim dispatcher as object

Dim Doc As Object
Dim PathDoc As String
Dim Props(0) As New com.sun.star.beans.PropertyValue
PathDoc = ConvertToURL("C:\Users\bard\Desktop\testdata.ods")
Props(0).Name  = "Hidden"
Props(0).Value = True
oDoc = StarDesktop.loadComponentFromURL(PathDoc, "_blank", 0, Props())
rem oDoc = ThisComponent
svc = createUnoService( "com.sun.star.sheet.FunctionAccess" )

Dim Variables(1) as Object
Dim Constraint as New com.sun.star.sheet.SolverConstraint
Dim Constraints(1) as Object
Dim i as integer
Dim j as integer
Dim k as integer
Dim loc as integer
Dim rows as integer
Dim cols as integer
Dim counter as integer
Dim counter2 as integer
Dim colScore as integer

smgr = GetProcessServiceManager()

solv = CreateUnoService("com.sun.star.comp.Calc.NLPSolver.DEPSSolverImpl")
rem solv = CreateUnoService("com.sun.star.comp.Calc.NLPSolver.SCOSolverImpl")

solv.Document = oDoc

Sheet = oDoc.Sheets(0)

ConstrBin = com.sun.star.sheet.SolverConstraintOperator.BINARY
ConstrEqual = com.sun.star.sheet.SolverConstraintOperator.EQUAL

i = 0

rem reading the size of the problem
oCell = Sheet.getCellByPosition(0,0)
oCell = Sheet.getCellByPosition(0,loc+2)
oCell = Sheet.getCellByPosition(0,loc+4)

For i = 0 To rows
   For j = 0 To cols

       oCell = Sheet.getCellByPosition(j,i)
    rem dinamically assigning variables and contraints (binary)
   If InStr(oCell.String,"<") <> 0 Then
      ReDim Preserve Variables(counter) As Object
      Variables(counter) = oCell.CellAddress
      Constraint.Left = oCell.CellAddress
      Constraint.Operator = ConstrBin
      ReDim Preserve Constraints(counter2) As Object
      Constraints(counter2) = Constraint
   End If
      rem finding the score cell
   If InStr(oCell.String,"Score") <> 0 Then
   End If
   rem assigning the score cell
   If j=colScore AND i=0 Then
   solv.Objective = oCell.CellAddress
   Constraint.Left = oCell.CellAddress
   Constraint.Operator = ConstrEqual
   Constraint.Right = 0
   ReDim Preserve Constraints(counter2) As Object
   Constraints(counter2) = Constraint
   End If
    Next j
Next i

solv.Variables = Variables()
solv.Constraints = Constraints()
solv.Maximize = False
solv.SwarmSize = 2
If Round(counter/3) > 2 Then
   solv.SwarmSize = Round(counter)/3
End If

cURL = ConvertToURL("C:\Users\bard\Desktop\testresults.ods")
Dim oPropertyValue As New com.sun.star.beans.PropertyValue
oPropertyValue.Name = "FilterName"
oPropertyValue.Value = "calc8"
oDoc.storeToURL( cURL, Array(oPropertyValue))


End Sub
Last edited by MrProgrammer on Sun May 03, 2020 5:48 pm, edited 1 time in total.
Reason: Tagged ✓ [Solved]
Libreoffice on Windows 10 and Debian Linux
Posts: 2
Joined: Fri May 01, 2020 12:58 pm

Re: Launching solver from macro and stagnation

Postby robleyd » Fri May 01, 2020 1:41 pm

Cross posted to AskLibreOffice
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
Posts: 3396
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Launching solver from macro and stagnation

Postby FJCC » Fri May 01, 2020 5:29 pm

Here is an example of using the non-linear solver in a macro. I have not worked through your code in detail. One thing I noticed is that you set the constraint on your objective cell to EQUAL. Try setting it to GREATER_EQUAL with the .Right of the constraint set to 0 and the .Maximize of the Solver set to FALSE. The code for Constraints(6) in my file uses that approach.
(160.12 KiB) Downloaded 21 times
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Posts: 7680
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Launching solver from macro and stagnation

Postby MZ_Bard » Sun May 03, 2020 11:46 am

Thanks for the tips, I realized the problem was in a constraint. Now it's stagnating properly

Libreoffice on Windows 10 and Debian Linux
Posts: 2
Joined: Fri May 01, 2020 12:58 pm

Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 2 guests