Solver automation

[nmcs]

I started using solver to do some optics filter experiments. I’ve had success trying to match a particular spectral response I’m trying to achieve by using the experimental LibreOffice Swarm Non-Linear Solver engine. It runs very slowly but eventually I get the results I’m after. I have to enter 4 cells to change to optimize a target cell and then carefully set 8 constraints as well as some options with the engine. But it’s a lot of trouble to set this up each time I want to run this with different filter sets unless I leave Calc open all the time because it doesn’t remember the parameters and there’s no way to save or restore the settings in some sort of xml file. Ideally I’d like to be able to call up solver from VBA codes using a button that makes these assignments in code, similar to pseudocode like this:

solver.engine = libreoffice_swarm_non_linear;
solver.engine.assume_integer = true;
solver.engine.assume_positive = true;
solver.target = (cell);
solver.optimizatation_type = minimum;
solver.change_list = (cell list or range);
solver.target.limit = (cell), (limit);
.
.
solver.target.limit = (cell), (limit);
solver.execute;

Is something like this available? Right now the best I can do is keep my computer on all the time and Calc open all the time so that my settings aren’t lost but this is getting old fast.

[elmau]

Look:

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

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…

And:
https://api.libreoffice.org/docs/idl/ref/interfacecom_1_1sun_1_1star_1_1sheet_1_1XSolver.html

[nmcs]

Thanks, that link was useful. I’ve created something similar where I expanded it to 4 independent variables with a total of 8 constraints. I had to replace the web quotation marks with the standard quotation marks to avoid the syntax errors. My problem now is that it’s not converging when I use VBA code to invoke the solver engine because I believe the solver engine is trying to use negative numbers. I’ve also constructed my spreadsheet so that integers can be used by the solver to speed things up, but only the experimental non-linear solver engine allows me to force integer only. I can make the DEPS Evolutionary Algorithm and SCO Evolutionary Algorithm solver engines work if I check “Assume Non-Negative Variables”. They just waste some time trying non-integral solutions but they both converge to a solution also. But from VBA code, I don’t know how to set this option. Does anyone know how to reference the solver engine options from VBA code? This should be documented somewhere?

[nmcs]

I found the issue with my code. I have R62 as my objective cell and I was OBO (Off By One). You have to be careful with the cell references since A=0 and row 1=0, so it’s easy to type the wrong row in especially if you forget this. Or if your columns are out to AZ getting the right column number has to be done carefully. There’s probably a more direct way to reference the cells by cell address but I was going off the example code which references them by column, row.

So I’m able to do what I want except now the VBA invocation of the solver doesn’t advance the stagnation progress bar, whereas when I execute it using the menu it works and stops once the solution converges and stops changing for a while. I’ve been able to set LearningCycles = 200 and that stops it after 200 iterations but it’s not ideal since some of my filter sets could take longer to converge. I’ve tried playing around with StagnationLimit but it doesn’t seem to do much unless I set it to 0, and in this case the execution stops immediately. The default seems to be 70 looking at the menu option, but I’ve tried between 1 and 70 and none of them advance the stagnation progress bar. So it’s something else.

I’d really like to be able to set the solver engine to the non-linear experimental engine because it has the fewest properties to manipulate and comes up with a solution the fastest and has a checkbox to force integer on all independent variables in one property, instead of trying to set integer constraints for each independent variables, one at a time. But the other non-linear engines also work if I can figure out a way to make the stagnation detection work from VBA. For those two, I also would like to force integer constraints but I don’t know what to use for the constraint property. I am using LESS_EQUAL and GREATER_EQUAL as shown in the example to set my computational range already. I tried INTEGER_ONLY but that isn’t it. If I could find the Solver API or list of properties somewhere I’m sure I could get this going the way I want it to run.

[nmcs]

I’m now at the point where I’ve cleaned up the code to use direct cell references by name instead of column, row which a lot less error-fraught. Stagnation not working right with VBA is an annoyance but setting the learning cycles to 300 seems to work for every test case so far. So not ideal but it will have to do. Also the integer constraint is simply INTEGER. I thought it would be a keyword problem that clashed with the INTEGER type but it works. (I’ve actually gone back to all floating-point anyway to simplify things.)

But the final issue is that solver seems to want to pop open a result box every time it completes, no matter if it’s called from VBA or invoked from the menu. Not great for automation! I want to be able to have this run through thousands of sets of data unattended. I’m dumping the optimized results of each data set to a text file but I can’t be around to click the OKAY button after each set! I suppose I could make a recorded keyboard macro (not Calc macro) to click this OKAY button periodically since it appears in the same location but that’s quite an ugly solution. Is there a known way to suppress the dialog boxes in Solver or other built-in tools?

[sokol92]

Please, could you upload an example file?
Also, for a better understanding by readers, use in the text (LO) Basic instead of VBA.

[nmcs]

I meant LO BASIC, not VBA. I do have the line Option VBASupport 1 at the top of the module but it works with it remarked out so I don’t think I’m using any VBA compatibility code yet.

I’ve got quite a few macros that work together now but I’ve pasted the one below that does the solving for one data set. I also made macros to copy datasets from the database to the analysis sheet (which has charts showing system and filter stack response curves), save results on a results sheet (which includes a button to reload any selected result set back to the analysis sheet since the analysis sheet will always only have the last data set loaded). The results sheet also has conditional formatting to highlight the best datasets using color coding for further review. I still need to write a macro to generate all the filter combinations I want to optimize in a loop as well as digitize a lot more filters (only have 23 in the database currently). But I’ve tested all the macros and they work as intended. The main issues remaining are:

1. Still need to find/code a mouse clicker or keyboard automation tool to look for the “Solver Status” dialog box and click the “OK” button when it ungrays once the solver completes. Preferably using a keyboard response to close the dialog box instead of a mouse click for better reliability. Alternatively, find a way to suppress the solver status dialog box from ever appearing in the first place.
2. Optional: Speed up the solver by finding a way to make the Stagnation work from the macro. So far the stagnation progress bar never moves and I have to limit the iterations to keep it from running 15-20 minutes for a full 2000 iterations (300 seems to work for all test cases so far). Stagnation works fine when invoking the solver from the tools menu and stops solver after less than 2 minutes run time per data set.
3. Optional: Find a way to choose which solver engine is used, as well as access to many of the solver options parameters for the chosen engine.

const STR_ANALYSIS_ROW = “O7” 'Cell location on Analysis sheet that points to the analysis data row

rem Performs analysis on one optical filter stack at a time
sub AnalyzeFilterSet
Dim oDoc, oSheet, dispatcher as object
Dim Variables(3) as object
Dim Constraints(7) 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 Constraint_3 as New com.sun.star.sheet.SolverConstraint
Dim Constraint_4 as New com.sun.star.sheet.SolverConstraint
Dim Constraint_5 as New com.sun.star.sheet.SolverConstraint
Dim Constraint_6 as New com.sun.star.sheet.SolverConstraint
Dim Constraint_7 as New com.sun.star.sheet.SolverConstraint
Dim sAnalysisRow as string

oDoc = ThisComponent
oSheet = ThisComponent.getSheets().getByName("Analysis")
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
smgr = GetProcessServiceManager()
solv = smgr.createInstance("com.sun.star.sheet.Solver")
solv.Document = oDoc

rem set constraint types needed
ConstrOperatorLE = com.sun.star.sheet.SolverConstraintOperator.LESS_EQUAL
ConstrOperatorGE = com.sun.star.sheet.SolverConstraintOperator.GREATER_EQUAL

sAnalysisRow = oSheet.getCellRangeByName(STR_ANALYSIS_ROW).string	'Load in the row number with all the data

rem Assign objective target cell for solver
ObjectiveCell = oSheet.getCellRangeByName("U" & sAnalysisRow)
solv.Objective = ObjectiveCell.CellAddress

rem Assign independent variables and store in solver variable array for manipulation
VariCell0 = oSheet.getCellRangeByName("C" & sAnalysisRow)
Variables(0) = VariCell0.CellAddress
VariCell1 = oSheet.getCellRangeByName("F" & sAnalysisRow)
Variables(1) = VariCell1.CellAddress
VariCell2 = oSheet.getCellRangeByName("I" & sAnalysisRow)
Variables(2) = VariCell2.CellAddress
VariCell3 = oSheet.getCellRangeByName("L" & sAnalysisRow)
Variables(3) = VariCell3.CellAddress

rem Initialize starting variables to help speed up the convergence, not sure if this helpful
VariCell0.value = 0.500
VariCell1.value = 0.500
VariCell2.value = 0.500
VariCell3.value = 0.500

'Set contraints for all independent variables
Constraint_0.Left = VariCell0.CellAddress
Constraint_0.Operator = ConstrOperatorLE
Constraint_0.Right = oSheet.getCellRangeByName("A" & sAnalysisRow).value
Constraints(0) = Constraint_0

Constraint_1.Left = VariCell0.CellAddress
Constraint_1.Operator = ConstrOperatorGE
Constraint_1.Right = oSheet.getCellRangeByName("B" & sAnalysisRow).value
Constraints(1) = Constraint_1

Constraint_2.Left = VariCell1.CellAddress
Constraint_2.Operator = ConstrOperatorLE
Constraint_2.Right = oSheet.getCellRangeByName("D" & sAnalysisRow).value
Constraints(2) = Constraint_2

Constraint_3.Left = VariCell1.CellAddress
Constraint_3.Operator = ConstrOperatorGE
Constraint_3.Right = oSheet.getCellRangeByName("E" & sAnalysisRow).value
Constraints(3) = Constraint_3

Constraint_4.Left = VariCell2.CellAddress
Constraint_4.Operator = ConstrOperatorLE
Constraint_4.Right = oSheet.getCellRangeByName("G" & sAnalysisRow).value
Constraints(4) = Constraint_4

Constraint_5.Left = VariCell2.CellAddress
Constraint_5.Operator = ConstrOperatorGE
Constraint_5.Right = oSheet.getCellRangeByName("H" & sAnalysisRow).value
Constraints(5) = Constraint_5

Constraint_6.Left = VariCell3.CellAddress
Constraint_6.Operator = ConstrOperatorLE
Constraint_6.Right = oSheet.getCellRangeByName("J" & sAnalysisRow).value
Constraints(6) = Constraint_6

Constraint_7.Left = VariCell3.CellAddress
Constraint_7.Operator = ConstrOperatorGE
Constraint_7.Right = oSheet.getCellRangeByName("K" & sAnalysisRow).value
Constraints(7) = Constraint_7

solv.Variables = Variables()	'Assign solver independent variables
solv.Constraints = Constraints()	'Assign solver independent variable constraints

rem solv.SelectEngine = 1	'Just a guess, don't know how you select the engine!
solv.Maximize = False	'Minimize = True
solv.StagnationLimit = 70	'No help with stagnation
solv.Tolerance = 0.000001	'Likewise no help, these are the defaults observed in menu options
solv.LearningCycles = 300	'Limit iterations until stagnation works with BASIC, not ideal!

rem Here you need to do something to suppress or handle the result dialog box before calling Solve
rem Ugly but currently best option if dialog box can't be suppressed is to call an external
rem mouse-clicking automation macro looking for Solver Status dialog box and auto-clicks the OK
rem button when the button is no longer grayed out
rem Call Mouse-click script
solv.Solve	'There is no callback AFAIK, this will hang here until solver finishes and dialog box closes

end sub

[sokol92]

Thanks for the macro example! How can I run a macro? Maybe you will upload a file (*.ods) - there is a corresponding button on the message editing toolbar.

[erAck]

@nmcs

I do have the line Option VBASupport 1 at the top of the module but it works with it remarked out so I don’t think I’m using any VBA compatibility code yet.

Be aware that some functions work semantically different even if syntactically valid with or without that option enabled, so you may not notice immediately.

[ms777]

Hi,
You can automatically close dialog boxes using the AccessibleContext. This is lots of programming, but works without any external tools like AutoIt. Have a look at Andrew Pitonyak’s Macro document https://www.pitonyak.org/AndrewMacro.odt , around chapter 10. Search it for ms777.
Good luck, ms777

[nmcs]

I’ve already completed my filter analysis, doing several hundred thousand combinations analyzed, with the work split between multiple computers. One of them was an Azure WIndows VM server running 24/7. I used this AHK script that I made which is very simple and I all do is double-click the shortcut to it before I start processing my solver macros.

SetTitleMatchMode, 2

Loop
{
WinWait, Solver Status ;wait for Solver Status dialog box
WinClose, Solver Status ;close Solver Status dialog box - will close when all iterations done
}

;note: this process runs in the background and can be manually closed when done using it with task manager or a win process kill call (or simply rebooting computer)

Your idea of using AccessibleContext is clever but I’m not sure this would work in this instance. Because when you call .SOLV, the macro code is stuck there until it completes (which means someone has clicked the OK button on the dialog box to close it out). You can’t call any dialog closing macro code ahead of calling .SOLV, and placing it after .SOLV is too late, so I can only imagine this would even work if the solver also has a known callback function you can place your code in to close the dialog box. It would be nice to have everything wrapped up in one .ods file, but this is not a professional product; it was only a tool used to create the resulting output data set which I’m analyzing now.

[nmcs]

I actually get some sort of error if I leave Option VBASupport 1 unremarked out, some syntax about a function requiring an = but I just got rid of the option since it works perfectly without it.

I now have everything thing working exactly as I had hoped for when I started this venture. Solve is running silently without any user input in the background on an initial ~75k dataset as I write this. I used cell formulas to create the combinations I needed instead of using macro code to code the combinations. They are all on my “Results” sheet and I use a pointer to keep track of which dataset is the next to be processed, as well as some other things I track. This way these cells act as “static” variables and I can quit the processing at any time and pick right up where I left off by simply clicking the run button. I’ve reduced the iterations to 10 for the initial study because it gets me to about 80% of the solution and allows me to eliminate a ton of unproductive filters for the next “round” of evaluations. If I had left it to run for the full solution, I calculated that it could take thousands of years to complete instead of just a week or two!

For the very important dialog box closer, I am using AutoHotKey v1.34. I tried the beta v2 (which has slightly different syntax than v1), but it would crash every few hundred cycles saying it couldn’t find “Solver Status” with the Solver Status box plainly visible. So I downgraded to v1.34 and it’s working great. I launch the script to run in a loop in the background and it closes the Solver dialog box immediately after each cycle. I keep LO Calc minimized and after starting the macro I minimize it as well. The Solver Status dialog box still opens up on the screen each cycle and disappears when the AHK script closes it, but it stays in the background so you can use your computer normally without interruptions while Solver is crunching datasets.

One odd thing is that for each Solver dataset, it takes longer and longer to execute for the same number of iterations. I thought I was imagining this but I left it running for a few hours and when I came back the reported time for the 10 iterations was on average, several seconds longer. The AHK script handles it just fine though without any sleep command and simply closes it when the number of specified iterations is reached. This is only an annoyance if you are running a zillion solver iterations as I am. If you are only running a hundred or so you won’t notice this. The way I “reset” solver so it’s fast again is simply saving my LO Calc file, quitting out of it and relaunching it (not necessary to reboot the computer). Then it’s fast initially and very gradual takes longer and longer. So I do this solver resetting a few times a day and once at the end of the day and the next morning. The effort has already paid off well as this automated system has identified some nice combinations that greatly improve the transfer function response over hand-picked filter combinations, and less than 20% of the combinations have been calculated.

[sokol92]

Such a design

oSolver.EnhancedSolverStatus=False

can suppress the dialogue box.
Thanks to @Lupp for the link.