solver madness 1

[RobBroekhuis]

This is driving me crazy. Trying to use Excel's solver, using the following code:

Sub findshiftconversion()
    SolverReset
    SolverOk SetCell:="Gfinal", MaxMinVal:=2, ByChange:="SHconv"
    SolverAdd CellRef:="SHconv", Relation:=1, FormulaText:=1
    MsgBox (solverget(5, "sheet1"))
    SolverAdd CellRef:="SHconv", Relation:=3, FormulaText:=0
    MsgBox (solverget(5, "sheet1"))
    SolverSolve
End Sub

SHconv and Gfinal are single-cell named ranges on sheet1. The proper reference to solver.xla has been set. The code executes, but refuses to put in the first constraint (i.e., the first SolverAdd statement doesn't do anything). This is obvious from the solverget values, which return 0 on first call, and 1 on second call. The same happens when I record the macro - Excel puts in extra junk and doesn't name the ranges in the code, but it amounts to a similar macro. It does what it needs to do when I record the macro, but doesn't work when I try to replay it. (The constraint is essential, otherwise Excel gets into numerical problems). Anyone know how to get around this?
Thanks


Rob

 

[RobBroekhuis]

A little extra information: the constraint gets added if the formulatext is 0.99 or 1.01, but not if it's 1. Now that's ODD!


Rob

 

[Nikita6003]

Hi Rob

And just to drive you even more crazy: tried your codeand it works ok for me...
I do vaguely remember something about a bug in the Solver. Tried trawling the M$ site but of course there's no specifics on bugs ;-)
But have a go at repeating the first 2 lines (i.e. resetting the solver & setting the Target & Changing cells)

Sorry I can't be more specific but I ran into this waaaaay back at college & haven't got anything on paper/disk/etc

HTH - let me know if it worked (n i k i t a aet y )a h o o . c o . u k

Cheers
Nikki
Look, mommy, I'm flying!

 

[RobBroekhuis]

Nikki,
Thanks for looking into this! I found one other reference in the newsgroup archives to the same problem (it's always with a <=1 constraint). Sometimes redoing the workbook, or uninstalling/reinstalling the solver addin appears to work, other times it doesn't. So yeah, an Excel bug seems most likely. I can live with the <=0.9999 constraint, so I'm not even going to pursue this any further at this point.
Thanks again
Rob


Rob

 

[combo]

Hi Rob,
the way solwer keeps data is creating hidden names in the worksheet. So maybe direct assigning ranges to those used by solver will help. (To unhide name change 'visible' property to True.)

combo

 

[RobBroekhuis]

Combo,
Excellent information! I can indeed set the solver_rhs2 refersto value to "=1", and it gets everything just right. Thanks much!


Rob