I had a problem earlier about solver not adding binary constraints to a model via VBA...I've (mostly) figured that one out. But now I have a new problem. First, let me post the relevant section of offending code. I should note this is running in Excel 2007.
The basic idea here is to loop through the sheet and look at the data (which will change from execution to execution) and build the appropriate model.
THe string by_change_string contains the cell addresses of all the changing variables, which are all binary. So as the code examines each line of the spreadsheet, it determines which cells on that line are available to be considered by the model and appends them to the string.
The multiple calls to solverok with the growing by_change_string are an attempt to solve an earlier problem where the binary constraints weren't being added to the model. All of the <= constraints were, but not the binary ones. It seems you can't constrain a variable to be binary until after it has been added to the model. There used to be just a single solverok statement after the loop that constructs by_change_string was complete, but when I did it that way I got all of the <= constraints and none of the binary ones.
When this code runs on my test sheet, the resulting model should have 136 decision variables (by changing cells). I've checked, and the by_change_string does in fact have 136 addresses in it. But when I look in the solver dialog box, there are only the first 41. I've stepped through the entire execution in debug mode and seen it call solverok after each iteration, but for some reason only the first 41 by change cells are there. And there's nothing special about the row of data where things go wrong...it's not like it's the first instance of one of the select cases or something...
When solver runs on this abbreviated model, it returns a garbage solution that it says is optimal. Basically it keeps everything the same, so that the final objective function value is the same as the starting one.
So then I tried something else...you'll notice a commented out block of code at the bottom:
The idea here was to make things much more efficient by getting all those solverok and solveradd statements out of the select cases. By waiting until after the final solverok statement, all the variables are now in the model, so you should be ablto just step through the by_change_string and make each of those a binary variable. That way you only need one solverok statement and you can get rid of all the solveradd's in the select cases. So I commented out all of those solverok's and solveradd's in the select cases and ran the macro again. I get no output from solver. When I look at the solver dialog after running, both the target cell and the by changing cells fields are blank. It's as if that last solverok statement had never been run.
So then I tried uncommenting all the solverok's, but leaving the solveradd's for the binary variables commented out. I go back to getting only the first 41 decision variables. The loop to set the binary constraints did it's job, but the solveradd's aren't getting the variables into the model.
I'm pretty much stumped at this point. Any ideas?
Code:
'build string of ByChange cells and set up cascading constraints
by_change_string = ""
For i = 1 To j - 1
If Len(by_change_string) > 0 Then 'there are already some elements in the string, so we might start with a comma
If Not (Right(by_change_string, 1) = ",") Then 'make sure the last character isn't already a comma
by_change_string = by_change_string & ","
End If
End If
current_status = Sheets("Buyback Risk Area").Range("C1").Offset(i).Value
Select Case current_status
Case "Y" 'risk area is currently yellow, so green transition is available
by_change_string = by_change_string & "$E$" & i + 1
solverok setcell:="$B$" & j + 4, MaxMinVal:=2, ValueOf:="0", ByChange:=by_change_string
'add binary constraints
solveradd cellref:="$E$" & i + 1, relation:=5, formulatext:="binary"
Case "O" 'risk area is currently orange, so green and yellow transitions are available
by_change_string = by_change_string & "$E$" & i + 1 & ",$G$" & i + 1
solverok setcell:="$B$" & j + 4, MaxMinVal:=2, ValueOf:="0", ByChange:=by_change_string
'add cascading constraints
solveradd cellref:="$E$" & i + 1, relation:=1, formulatext:="$G$" & i + 1 'says E <= G, which means you can't select green unless you've already selected yellow
'add binary constraints
solveradd cellref:="$E$" & i + 1, relation:=5, formulatext:="binary"' solveradd cellref:="$G$" & i + 1, relation:=5, formulatext:="binary"
Case "R" 'risk area is currently red, so green, yellow, and orange transitions are available
by_change_string = by_change_string & "$E$" & i + 1 & ",$G$" & i + 1 & ",$I$" & i + 1
'solverok setcell:="$B$" & j + 4, MaxMinVal:=2, ValueOf:="0", ByChange:=by_change_string
'add cascading constraints
solveradd cellref:="$E$" & i + 1, relation:=1, formulatext:="$G$" & i + 1 'says E <= G, which means you can't select green unless you've already selected yellow
solveradd cellref:="$G$" & i + 1, relation:=1, formulatext:="$I$" & i + 1 'says G <= I, which means you can't select yellow unless you've already selected orange
'add binary constraints
solveradd cellref:="$E$" & i + 1, relation:=5, formulatext:="binary"
solveradd cellref:="$G$" & i + 1, relation:=5, formulatext:="binary"
solveradd cellref:="$I$" & i + 1, relation:=5, formulatext:="binary"
Case "B" 'risk area is black, so green, yellow, orange and red transitions are avaailable
by_change_string = by_change_string & "$E$" & i + 1 & ",$G$" & i + 1 & ",$I$" & i + 1 & ",$K$" & i + 1
solverok setcell:="$B$" & j + 4, MaxMinVal:=2, ValueOf:="0", ByChange:=by_change_string
'add cascading constraints
solveradd cellref:="$E$" & i + 1, relation:=1, formulatext:="$G$" & i + 1 'says E <= G, which means you can't select green unless you've already selected yellow
solveradd cellref:="$G$" & i + 1, relation:=1, formulatext:="$I$" & i + 1 'says G <= I, which means you can't select yellow unless you've already selected orange
solveradd cellref:="$I$" & i + 1, relation:=1, formulatext:="$K$" & i + 1 'says K <= M, which means you can't select orange unless you've already selected red
'add binary constraints
solveradd cellref:="$E$" & i + 1, relation:=5, formulatext:="binary"
solveradd cellref:="$G$" & i + 1, relation:=5, formulatext:="binary"
solveradd cellref:="$I$" & i + 1, relation:=5, formulatext:="binary"
solveradd cellref:="$K$" & i + 1, relation:=5, formulatext:="binary"
End Select
Next i
'buyback amount constraint
solveradd cellref:="$O$" & j + 1, relation:=1, formulatext:="$B$" & j + 2
'set target cell
solverok setcell:="$B$" & j + 4, MaxMinVal:=2, ValueOf:="0", ByChange:=by_change_string
'set binary constraints
'binary_array = Split(by_change_string, ",")
'For i = 0 To UBound(binary_array)
' solveradd cellref:=Range(binary_array(i)), relation:=5, formulatext:="binary"
'Next i
Application.ScreenUpdating = True
SolverSolve userFinish:=FalseThe basic idea here is to loop through the sheet and look at the data (which will change from execution to execution) and build the appropriate model.
THe string by_change_string contains the cell addresses of all the changing variables, which are all binary. So as the code examines each line of the spreadsheet, it determines which cells on that line are available to be considered by the model and appends them to the string.
The multiple calls to solverok with the growing by_change_string are an attempt to solve an earlier problem where the binary constraints weren't being added to the model. All of the <= constraints were, but not the binary ones. It seems you can't constrain a variable to be binary until after it has been added to the model. There used to be just a single solverok statement after the loop that constructs by_change_string was complete, but when I did it that way I got all of the <= constraints and none of the binary ones.
When this code runs on my test sheet, the resulting model should have 136 decision variables (by changing cells). I've checked, and the by_change_string does in fact have 136 addresses in it. But when I look in the solver dialog box, there are only the first 41. I've stepped through the entire execution in debug mode and seen it call solverok after each iteration, but for some reason only the first 41 by change cells are there. And there's nothing special about the row of data where things go wrong...it's not like it's the first instance of one of the select cases or something...
When solver runs on this abbreviated model, it returns a garbage solution that it says is optimal. Basically it keeps everything the same, so that the final objective function value is the same as the starting one.
So then I tried something else...you'll notice a commented out block of code at the bottom:
Code:
'set binary constraints
'binary_array = Split(by_change_string, ",")
'For i = 0 To UBound(binary_array)
' solveradd cellref:=Range(binary_array(i)), relation:=5, formulatext:="binary"
'Next iThe idea here was to make things much more efficient by getting all those solverok and solveradd statements out of the select cases. By waiting until after the final solverok statement, all the variables are now in the model, so you should be ablto just step through the by_change_string and make each of those a binary variable. That way you only need one solverok statement and you can get rid of all the solveradd's in the select cases. So I commented out all of those solverok's and solveradd's in the select cases and ran the macro again. I get no output from solver. When I look at the solver dialog after running, both the target cell and the by changing cells fields are blank. It's as if that last solverok statement had never been run.
So then I tried uncommenting all the solverok's, but leaving the solveradd's for the binary variables commented out. I go back to getting only the first 41 decision variables. The loop to set the binary constraints did it's job, but the solveradd's aren't getting the variables into the model.
I'm pretty much stumped at this point. Any ideas?