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:

- 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.
- 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.
- 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