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
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()
rem CHOOSING THE SOLVER
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
counter=-1
counter2=-1
colScore=-1
rem reading the size of the problem
oCell = Sheet.getCellByPosition(0,0)
loc=oCell.Value
oCell = Sheet.getCellByPosition(0,loc+2)
rows=oCell.Value
oCell = Sheet.getCellByPosition(0,loc+4)
cols=oCell.Value
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
counter=counter+1
counter2=counter2+1
nchar=Len(oCell.String)
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
oCell.Value=1
End If
rem finding the score cell
If InStr(oCell.String,"Score") <> 0 Then
colScore=j+1
End If
rem assigning the score cell
If j=colScore AND i=0 Then
solv.Objective = oCell.CellAddress
counter2=counter2+1
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
solv.LearningCycles=20000
solv.DECR=0.05
solv.DEFactorMin=0.1
solv.DEFactorMax=0.3
solv.AgentSwitchRate=1
solv.StagnationLimit=2
solv.Tolerance=100
solv.GuessVariableRange=false
solv.Solve()
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))
oDoc.Close(true)
End Sub