Set solver constraint not working in VB

[Greg Snidow]

Greetings all. I've got a solver macro, which successfully sets two of three
required constraints by using variable ranges in the SolverAdd statement,
like bellow, where LastInputRow and ThetaCol are integer variables.

' Set the input range constraint
SolverAdd CellRef:=Sheets("MacroDEA").Range(Sheets("MacroDEA") _
.Cells(2, ThetaCol + 1).Address & ":" & _
Sheets("MacroDEA").Cells(LastInputRow, ThetaCol + 1) _
.Address), Relation:=1, formulatext:= _
Sheets("MacroDEA").Range(Sheets("MacroDEA") _
.Cells(2, ThetaCol + 3).Address & ":" & _
Sheets("MacroDEA").Cells(LastInputRow, ThetaCol +
3).Address)
The above code is equivalent to...
SolverAdd CellRef:="$BV$2:$BV$6", Relation:=1, formulatext:="$BX$2:$BX$6",
and it works without a hitch.

I have two range constraints set up like this, and both are added to solver
no problem. However, I also have a third constraint that is only one cell,
and I need to also use variable cell ranges. The below is the hard coded
constraint, which is added to the constraints no problem...

SolverAdd CellRef:="$BV$10", Relation:=2, formulatext:="$BX$10"

When I try to use the same set up for the third constraint, it does not
work, like this...
SolverAdd CellRef:=Sheets("MacroDEA").Cells(LastDataRow + 1, ThetaCol + 1), _
Relation:=2, formulatext:=1

I tried naming the cell's address as a variable and using the variable, but
that did not work either. The only thing I can do to make it stick is hard
code it, which will not work for my set up. I am completely stumped over
this third constraint. Any ideas? Thank you

Greg Snidow

 

[Matthew Herbert]

Greg Snidow,

Ultimately, it looks like your range objects are missing a final ")". I've
provided a slightly different approach below. It's not tested, so if Solver
wants a string input, i.e. .Address, as opposed to a Range Object, then
simply tack a .Address on the end of the range objects.

Best,

Matthew Herbert

Dim Wks As Worksheet
Dim rngCellRef As Range
Dim intRelation As Integer
Dim rngFormulaText As Range

Set Wks = Sheets("MacroDEA")

With Wks
Set rngCellRef = Range(.Cells(2, ThetaCol + 1), _
.Cells(LastInputRow, ThetaCol + 1))
Set rngFormulaText = Range(.Cells(2, ThetaCol + 3), _
.Cells(LastInputRow, ThetaCol + 3))
End With

intRelation = 1

SolverAdd CellRef:=rngCellRef, _
Relation:=intRelation, _
FormulaText:=rngFormulaText

 

[Matthew Herbert]

Greg Snidow,

I initially read through your post too quickly and didn't fully address your
question. Try using the range objects listed in my initial post and alter
them accordingly for each of the three constraints. See if this will solve
the issue, or at least help point out where the problem resides.

Best,

Matthew Herbert

Dim Wks As Worksheet
Dim rngCellRef As Range
Dim intRelation As Integer
Dim rngFormulaText As Range

Set Wks = Sheets("MacroDEA")

'-------------------------------------------------------------------
'Constraint 1
With Wks
Set rngCellRef = Range(.Cells(2, ThetaCol + 1), _
.Cells(LastInputRow, ThetaCol + 1))
Set rngFormulaText = Range(.Cells(2, ThetaCol + 3), _
.Cells(LastInputRow, ThetaCol + 3))
End With

intRelation = 1

SolverAdd CellRef:=rngCellRef, _
Relation:=intRelation, _
FormulaText:=rngFormulaText
'-------------------------------------------------------------------

'-------------------------------------------------------------------
'Constraint 2
'Add accordingly
'-------------------------------------------------------------------

'-------------------------------------------------------------------
'Constraint 3
With Wks
Set rngCellRef = .Cells(LastDataRow + 1, ThetaCol + 1)

'uncomment this and change if the FormulaText references a range
'Set rngFormulaText = .Cells(Row #, Column #)
End With

intRelation = 2

'add in rngFormulaText if uncommented from above
SolverAdd CellRef:=rngCellRef, _
Relation:=intRelation, _
FormulaText:=1
'-------------------------------------------------------------------

 

[Greg Snidow]

Mathew, thank you for your suggestions. I am almost ready to give it a go,
but I wanted to throw something out there. I ended up reversing my data set
up, which meant I had to slightly alter almost 1000 lines of code, so that
the constraint ranges would not change, so I no longer need variables for
them in the Solver part. I thought I was good to go until I tried it. Now,
with the hard coded constraint ranges, all three constraints are added with
no problems. However, now the *same* variable I *was* using for the ByChange
range no longer works, and it only works if I hard code *it*. I will let you
know how it comes out. I'm just learning how to use range variable in a With
block, so it might take me a little while. Thanks again.

Greg

 

[Matthew Herbert]

Greg,

When you use the With Statement, ensure that you qualify correctly. I'll
illustrate with a simply example below. (See the explanation after the code).

Sub QualifyRanges()
Dim wksOne As Worksheet
Dim wksTwo As Worksheet
Dim Rng As Range

Set wksOne = Worksheets("Sheet1")
Set wksTwo = Worksheets("Sheet2")

wksOne.Select

With wksTwo
.Range("A1").Value = "This is worksheet two."
Set Rng = Range("A1:B5")
MsgBox Rng.Address(External:=True)
.Select
End With

End Sub

The main point here is that the Rng Object is referring to Sheet1 (i.e. the
current ActiveSheet by default), not Sheet2.

The Set statement creates an object. So, in the code above I created three
objects – wksOne, wksTwo, and Rng. wksOne and wksTwo are Worksheet objects
and Rng is a Range object. Thus, wksOne and wksTwo take on all the
attributes available to Worksheets and Rng takes on all the attributes
available to Range. Cool, huh? The With statement allows you to run a
series of statements for one object. So, With the wksTwo object, perform all
the “.†attributes (i.e. you get the benefit of Intellisence popping up when
you type the dot operator). For example, it’s as if the .Select in the With
statement were wksTwo.Select. With statements help prevent errors, decrease
the amount of typing that is needed, and help with code compilation; however,
you fall into the potential trap of not applying a “.†appropriately, like
with the Set Rng line of code above. The Set Rng line of code is missing the
“.†before Range("A1:B5"). Thus, when the program gets to this line of code,
the default hierarchy is the active workbook and the active worksheet. Even
though the Set Rng statement is embedded in the With statement, Rng is set to
a range on Sheet1, not Sheet2. Put the “.†before Range("A1:B5") and rerun
the code. (The External parameter of the Address property returns the fully
qualified address of the range). Below are the message box results of the
two scenarios:
- Set Rng = Range("A1:B5"): [Book1]Sheet1!$A$1:$B$5
- Set Rng = .Range("A1:B5"): [Book1]Sheet2!$A$1:$B$5

Notice the difference in Sheet1! and Sheet2!

Best,

Matt

 

[Greg Snidow]

Matt, thank you so much for the thorough explaination of the using with
blocks, and that ranges and worksheets are treated as objects. I've been
messing around with using with blocks for a couple of weeks, but I have never
come accross a meaninful explaination. However, I never really looked hard.
Anyhow, I knew something was different about ranges, because you can't do
MsgBox(Range(variable)). I have definitely benefitted from your input here.
On my issue, though, all is good now. I got some weird error one time while
running the macro that said something like "Solver ran out of memory..." or
something like that. So I tried to run Solver one time from the toolbar, and
same thing. I ended up making a new workbook, copying and pasting the code,
and voila, all three constraints worked as variables again, as I knew they
should have done in the first place. Thanks for the time you took out of
your day to consider my problem, I very much appreciate all the help I get
here.

Greg

Greg,

When you use the With Statement, ensure that you qualify correctly. I'll
illustrate with a simply example below. (See the explanation after the code).

Sub QualifyRanges()
Dim wksOne As Worksheet
Dim wksTwo As Worksheet
Dim Rng As Range

Set wksOne = Worksheets("Sheet1")
Set wksTwo = Worksheets("Sheet2")

wksOne.Select

With wksTwo
.Range("A1").Value = "This is worksheet two."
Set Rng = Range("A1:B5")
MsgBox Rng.Address(External:=True)
.Select
End With

End Sub

The main point here is that the Rng Object is referring to Sheet1 (i.e. the
current ActiveSheet by default), not Sheet2.

The Set statement creates an object. So, in the code above I created three
objects – wksOne, wksTwo, and Rng. wksOne and wksTwo are Worksheet objects
and Rng is a Range object. Thus, wksOne and wksTwo take on all the
attributes available to Worksheets and Rng takes on all the attributes
available to Range. Cool, huh? The With statement allows you to run a
series of statements for one object. So, With the wksTwo object, perform all
the “.†attributes (i.e. you get the benefit of Intellisence popping up when
you type the dot operator). For example, it’s as if the .Select in the With
statement were wksTwo.Select. With statements help prevent errors, decrease
the amount of typing that is needed, and help with code compilation; however,
you fall into the potential trap of not applying a “.†appropriately, like
with the Set Rng line of code above. The Set Rng line of code is missing the
“.†before Range("A1:B5"). Thus, when the program gets to this line of code,
the default hierarchy is the active workbook and the active worksheet. Even
though the Set Rng statement is embedded in the With statement, Rng is set to
a range on Sheet1, not Sheet2. Put the “.†before Range("A1:B5") and rerun
the code. (The External parameter of the Address property returns the fully
qualified address of the range). Below are the message box results of the
two scenarios:
- Set Rng = Range("A1:B5"): [Book1]Sheet1!$A$1:$B$5
- Set Rng = .Range("A1:B5"): [Book1]Sheet2!$A$1:$B$5

Notice the difference in Sheet1! and Sheet2!

Best,

Matt

Mathew, thank you for your suggestions. I am almost ready to give it a go,
but I wanted to throw something out there. I ended up reversing my data set
up, which meant I had to slightly alter almost 1000 lines of code, so that
the constraint ranges would not change, so I no longer need variables for
them in the Solver part. I thought I was good to go until I tried it. Now,
with the hard coded constraint ranges, all three constraints are added with
no problems. However, now the *same* variable I *was* using for the ByChange
range no longer works, and it only works if I hard code *it*. I will let you
know how it comes out. I'm just learning how to use range variable in a With
block, so it might take me a little while. Thanks again.

Greg

 

[Dana DeLouis]

SolverAdd CellRef:=Sheets("MacroDEA").Range(Sheets("MacroDEA") _

.Cells(2, ThetaCol + 1).Address & ":" & _
Sheets("MacroDEA").Cells(LastInputRow, ThetaCol + 1) _
.Address), Relation:=1, formulatext:= _
Sheets("MacroDEA").Range(Sheets("MacroDEA") _
.Cells(2, ThetaCol + 3).Address & ":" & _
Sheets("MacroDEA").Cells(LastInputRow, ThetaCol +

Hi. Not any better, but see if there are any ideas here you could use.

Sub Demo()
Const ThetaCol As Long = 73
Const LastInputRow As Long = 6
Const LastDataRow As Long = 9

Dim LHS As Range
Dim RHS As Range
Dim NR As Long 'Number of Rows

'Sheet must be active for Solver...
Sheets("MacroDEA").Activate

'Number of Rows
NR = LastInputRow - 2 + 1

Set LHS = Cells(2, ThetaCol + 1).Resize(NR)
'RHS is 2 Columns over...
Set RHS = LHS.Offset(0, 2)

'Start a clean Solver...
SolverReset
'Add SolverOk, etc...

SolverAdd LHS.Address, 1, RHS.Address
SolverAdd Cells(LastDataRow + 1, ThetaCol + 1), 2, 1
End Sub

= = = = = = = = = = =
HTH
Dana DeLouis

Note: I don't know your model, but be careful if BV10 is a "Sum()"
formula that must equal 1, due to precision / tolerance of Solver.