[Solved] Using Solver from Basic

[KPU57]

I am new to OpenOffice and LibreOffice and have found that Solver really works well for a problem I am working on.

Now I would like to incorporate Solver into a macro ... I think I have nearly succeeded but my code fails at the "solve.Objective = oCell.CellAddress" line with the error message:

BASIC runtime error.
An exception occurred
Type: com.sun.star.uno.RuntimeException
Message: [jni_uno bridge error] UNO calling Java method Objective: [map_to_uno():com.sun.star.table.CellAddress] null-ref given!
java stack trace:
.

Here is my code so far:

Code: Select all

Sub StartMeasure

Dim v1 as Single
Dim v2 as Single
Dim oDocument    As Object
Dim oSheet       As Object
Dim oCell        As Object
Dim oCellRange   As Object
Dim Variables(4) As New com.sun.star.table.CellAddress
      
   oDocument=ThisComponent
   oSheet=oDocument.Sheets.getByName("Model_2")
   svc = createUnoService( "com.sun.star.sheet.FunctionAccess" )

   oCellRange = oSheet.getCellRangeByPosition( 2, 12, 2, 15 ) 'C13:C16
   arg = array( oCellRange )
   v1 =  svc.callFunction( "AVERAGE", arg )

   oCellRange = oSheet.getCellRangeByPosition( 2, 39, 2, 42 ) 'C40:C43
   arg = array( oCellRange )
   v2 =  svc.callFunction( "AVERAGE", arg )
   
   oCell=oSheet.getCellByPosition(3,3) ' D4
   oCell.setValue(v1)
   oCell=oSheet.getCellByPosition(3,4) ' D5
   oCell.setValue(v2)
   
   smgr = GetProcessServiceManager()
   solv = smgr.createInstance("com.sun.star.sheet.Solver")
   solv.Document = oDocument
 
   Variables(0) = oSheet.getCellByPosition(3, 3).CellAddress 'D4
   Variables(1) = oSheet.getCellByPosition(3, 4).CellAddress 'D5
   Variables(2) = oSheet.getCellByPosition(3, 5).CellAddress 'D6
   Variables(3) = oSheet.getCellByPosition(3, 6).CellAddress 'D7
   solv.Variables = Variables()

   oCell = oSheet.getCellByPosition(5,44) 'F45
   solv.Objective = oCell.CellAddress

   solv.Maximize = False
   solv.Solve()
 ' Print solv.ResultValue
End Sub

Any suggestions gratefully received! Thanks!
BTW surely there must be more settings for Solver than are listed here: http://www.openoffice.org/api/docs/comm ... olver.html
Should I be looking somewhere else?

[Villeroy]

Works for me with OpenOffice 3.4.1 and 4.0

[KPU57]

Really???!!! Perhaps I should use OpenOffice instead of LibreOffice - I'll give it a try!
Many thanks for your help!

[KPU57]

Installed OpenOffice 4.0 and it works! Thanks for that!

[tavert]

I ran into this same problem with LibreOffice. I'm guessing it might be related to the additional solver types that you may have installed, one of the evolutionary algorithms is the default choice for Solver engine when created with:

Code: Select all

solv = smgr.createInstance("com.sun.star.sheet.Solver")

In debug mode I'm seeing solv have a whole different set of properties than the linear solver I was expecting from most Solver-macro code snippets I've seen, which work with OpenOffice. I was able to fix this and create a linear solver with the expected set of properties by changing that line to:

Code: Select all

solv = smgr.createInstance("com.sun.star.comp.Calc.Solver")

Hopefully this may be of use to someone.