Apache OpenOffice Community Forum

Posted: **Mon Sep 16, 2013 3:42 pm**

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?

Posted: **Mon Sep 16, 2013 7:03 pm**

Works for me with OpenOffice 3.4.1 and 4.0

Posted: **Mon Sep 16, 2013 7:51 pm**

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

Posted: **Tue Sep 17, 2013 11:04 am**

Installed OpenOffice 4.0 and it works! Thanks for that!

Posted: **Tue Jan 28, 2014 5:27 am**

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.

Apache OpenOffice Community Forum

[Solved] Using Solver from Basic

[Solved] Using Solver from Basic

Re: Using Solver from Basic

Re: Using Solver from Basic

Re: Using Solver from Basic

Re: [Solved] Using Solver from Basic