A couple of Solver-questions

[Jaymond Flurrie]

First of all, if this is in wrong section, please tell me where to look for help.

So I'm working with a complex problem here and this problem has been solved in Excel already, I just had to make an interface for it. I decided to translate the whole formula crunching part to VBA and I'm done with that. Now I'm left with the part where I need to use solver, and I have never programmed against Solver. I read some online material about it but I was left with a couple of questions to begin with.

-What is "FormulaText"? This example I saw uses "4" as the FormulaText, but I have no idea why. Is it just what the formula bar happens to say about that cell?

-Can I just pass arrays instead of ranges to Solver? Or do I have to write those arrays to ranges first?

-Here's the starting conditions for solver that was used in Excel, which, like said, works perfectly when driven without VBA:

'TargetCell: $A$3
'To Max
'Subject to the Constraints: $A$1 <= $B$1
'By Changing Variable Cells = $A$2:$J$2
'Subject to the Constraints: $A$2:$J$2 <= 1
'Subject to the Constraints: $A$2:$J$2 = Integer

and now I'm trying to pass those to Solver by VBA. My "$A$2:$J$2" is in variant array vToOpt and that has a 1D-array sized 1 to n cells, might be 1, might be 100. This $A$1 is in variable lOptGr (long), $A$3 is in variable lOptMax (long) and $B$1 is a constant that user inputs.

When I record this as a macro driving it, I get (after cleaning extra rows away):

    SolverAdd CellRef:="$A$1", Relation:=1, FormulaText:="$B$1"
    SolverAdd CellRef:="$A$2:$J$2", Relation:=1, FormulaText:="1"
    SolverOk SetCell:="$A$3", MaxMinVal:=1, ValueOf:=0, _ 
    ByChange:="$A$2:$J$2", Engine:=2, EngineDesc:="Simplex LP"
    SolverSolve

So how do I get this into a VBA code? I have tried something so far:

    SolverAdd CellRef:=lOptGr, Relation:=1, FormulaText:="3"
    SolverAdd CellRef:=vToOpt, Relation:=1, FormulaText:="1"
    SolverOk SetCell:=lOptMax, MaxMinVal:=1, ValueOf:=0, _ 
    ByChange:=vToOpt, Engine:=2, EngineDesc:="Simplex LP"
    SolverSolve True

but is it even close to what I should have? How do I get both the array (or variant, whatever) out from solver to show that "with these you get the maximum value" and the maximum number (lOptMax) as well?

 

[Jaymond Flurrie]

You can derive the result in A3 by abitrarily complex means, and long as it's via UDFs, not subs. Do you understand why, or should I explain?

A limitation is that Solver has difficulty in solving problems with flat spots -- it is much better at solving smooth functions, because that allows it to calculate partial derivatives for estimating slope and sensitivity.

So the answer is that I should build a UDF which would calculate this result, and then Solver tries to optimize that by changing those A2:J2 cells.

Ok, I'll give that a try. Thanks for the help!

 

[shg]

So the answer is that I should build a UDF ...

Or use formulas, as approriate. Unless the problem is especially well suited to VBA and not to formulas, the formulas would almost always be faster.

 

[Jaymond Flurrie]

Or use formulas, as approriate.

That's actually something I considered too. Basically this is nothing more complex than IF-formula is, otherwise just basically sums and multiplies. I thought about building a megaformula there, but is there a limit when megaformula is too long for Excel itself? I mean, does there come a limit when Excel would really prefer using some intermediate result cells?

 

[shg]

If the formula is too long, you won't be allowed to enter it.

I'm not a fan of megaformulas; some poor dog (maybe you, a year later) that has to maintain them takes one look, their eyes start bleeding, and they decide they'd be better off spiffing up their resume.

 

[Jaymond Flurrie]

If the formula is too long, you won't be allowed to enter it.

I'm not a fan of megaformulas; some poor dog (maybe you, a year later) that has to maintain them takes one look, their eyes start bleeding, and they decide they'd be better off spiffing up their resume.

If they are used right and well documented (like for example I have the VBA-code equivalent to that) and it prevents using 6 million (seriously) useless cells, then absolutely, I'm all for using them. Another good way to document that is to keep the previous version without those megaformulas available too.

 

[shg]

Well, you now have three options to choose from.

 

[Jaymond Flurrie]

Well, you now have three options to choose from.

Yeah, that'll keep me busy, thanks!