Hello,

I am trying to use OpenSolver with a user defined function. please see below rthe VBA code

I have a number of problems:

1. How do I define a stoping criterion, other than maximum number of iterations? For example I would like to have a constraint on the goal, like goal<0,goal>-10.
2. While in the UDF, I want to update the worksheet with some information. It seems that the access to the sheet from a place other than the algorithm itself is blocked. No updates from th UDF but I do see updates of the goal value and variables
Is there a way to allow the UDF to update the sheet?

Thank you

Sub EXCEL_OPENSOLVER_EXAMPLE_FUN()
'
'
' CBC
' Gurobi
' NeosCBC
' NeosCplex
' Bonmin
' Couenne
' NOMAD
' NeosBon
' NeosCou
'
'
'
Dim TestSheet As Worksheet
Set TestSheet = Sheets("HFSS")

OpenSolver.ResetModel Sheet:=TestSheet

SetChosenSolver "GUROBI"

OpenSolver.SetObjectiveFunctionCell TestSheet.Cells(8, 2), Sheet:=TestSheet ' CELL(8,5) IS A FUNCTION

OpenSolver.SetObjectiveSense MinimiseObjective, Sheet:=TestSheet


'Variables Definition
OpenSolver.SetDecisionVariables TestSheet.Range(Cells(2, 2), Cells(2, 2))

'Constraints Definition
OpenSolver.AddConstraint TestSheet.Range(Cells(12, 2), Cells(12, 2)), RelationLE, TestSheet.Range(Cells(12, 4), Cells(12, 4)), Sheet:=TestSheet
OpenSolver.AddConstraint TestSheet.Range(Cells(13, 2), Cells(12, 2)), RelationGE, TestSheet.Range(Cells(13, 4), Cells(12, 4)), Sheet:=TestSheet

MaxIter = GetMaxIterations()
Debug.Print "MaxIter ", MaxIter
RunOpenSolver
End Sub
Function FUN(B2)
Call HFSS_OPTIMIZATION_ITERATION(ITER, B2, GOAL)
FUN = GOAL
End Function
Public Sub HFSS_OPTIMIZATION_ITERATION(ITER, B2, GOAL)
'
'
' 1 - OPEN HFSS PROJECT FILE
' 2 - CHANGE VARIABLES
' 3 - RUN
' 4 - EXPORT RESULTS
' 5 - DELETE RESULTS, CLOSE PROJECT, QUIT HFSS
' 6 - IMPORT RESULTS
' 7 - CALCULATE GOAL FUNCTION

' 1 - OPEN HFSS PROJECT FILE
' SETTING FOR OPEN HFSS PROJECT FILE
SS = "HFSS"
DLN = Sheets(SS).Cells(2, 6).Value
PLN = Sheets(SS).Cells(3, 6).Value
HLN = DLN + "" + PLN
ITER = ITER + 1
Dim FREQ(), ARG()
Dim oAnsoftApp
Dim oDesktop
Dim oProject
Dim oDesign
Dim oEditor
Dim oModule
Set oAnsoftApp = CreateObject("Ansoft.ElectronicsDesktop")
Set oDesktop = oAnsoftApp.GetAppDesktop()

oDesktop.OpenProject HLN

oDesktop.RestoreWindow
Set oDesktop = oAnsoftApp.GetAppDesktop()
oDesktop.RestoreWindow
Set oProject = oDesktop.GetActiveProject
Set oDesign = oProject.GetActiveDesign()
Set oModule = oProject.GetActiveDesign()
Set oModule = oDesign.GetModule("AnalysisSetup")

DesignName = oDesign.getname()
ProjectName = oProject.getname()
ProDir = oDesktop.GetProjectDirectory
setupnames = oModule.GetSetups
Path = oProject.GetPath()
Path = Replace(Path, "/", "")

'Set oDesign = oProject.SetActiveDesign(DesignName)
' 2 - CHANGE VARIABLES

Variable_Name = Sheets(SS).Cells(2, 1).Value
Variable_Value = B2
Variable_units = Sheets(SS).Cells(2, 3).Value
Variable_Value = Str(Variable_Value) + Variable_units
VN = "NAME:" + Variable_Name
oDesign.ChangeProperty Array("NAME:AllTabs", Array("NAME:LocalVariableTab", Array("NAME:PropServers", "LocalVariables"), Array("NAME:ChangedProps", _
Array(VN, "Value:=", Variable_Value))))

' 3 - RUN
On Error Resume Next
oProject.AnalyzeAll
If Err.Number <> 0 Then
GOAL = 10 ^ 6
GoTo BYE
Exit Sub
End If
' 4 - EXPORT RESULTS

Set oModule = oDesign.GetModule("ReportSetup")

REPORT_NAME = "SPAR"
REPORT_NAME_FILE = DLN + "" + REPORT_NAME + ".csv"
oModule.ExportToFile REPORT_NAME, REPORT_NAME_FILE

'5 DELETE RESULTS, CLOSE PROJECT, QUIT HFSS


' 6 - IMPORT RESULTS AND GOAL CALCULATION
'
' GOAL=SUM OF ALL SPAR TO BE MINIMIZED
'
SUM_SPAR = 0
I = -1
Open REPORT_NAME_FILE For Input As 1
While EOF(1) = False
Line Input #1, T$
I = I + 1
Wend
Close 1
imax = I
Open REPORT_NAME_FILE For Input As 1
ReDim FREQ(I), ARG(I, J)
Line Input #1, T$
For I = 0 To imax - 1
Line Input #1, T$
TT = Split(T$, ",")
FREQ(I) = TT(0)
ARG(I, J) = TT(1)
SUM_SPAR = SUM_SPAR + TT(1)
Next I
Close 1
GOAL = SUM_SPAR
BYE:
oDesign.DeleteFullVariation "All", False
oDesktop.CloseProject ProjectName
oDesktop.QuitApplication
Debug.Print ITER, B2, GOAL
Sheets(SS).Cells(15, 2).Value = ITER
Sheets(SS).Cells(ITER + 6, 6).Value = ITER
Sheets(SS).Cells(ITER + 6, 7).Value = GOAL
End Sub