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?

 

[shg]

In my experience in automating Solver, it's happier with cell addresses (which must be on the same sheet as the target cell) than with range references. So assuming that lOptMax is a range, replace it with lOptMax.Address

Also, you should start with SolverReset.

FormulaText is what appears in the right side of the Constraint dialog.

 

[Jaymond Flurrie]

In my experience in automating Solver, it's happier with cell addresses (which must be on the same sheet as the target cell) than with range references. So assuming that lOptMax is a range, replace it with lOptMax.Address

Also, you should start with SolverReset.

FormulaText is what appears in the right side of the Constraint dialog.

lOptMax is just a number, which is supposed to grown as big as possible by changing the values in the vToOpt variant array.

If I would write these intermediate results that Solver reads to ranges, is the solver smart enough to understand that there's a big bunch of VBA code behind there? How do I tell Solver that "yeah, go ahead and chance, looks like a constant, but it is not"?

 

[shg]

The target cell for Solver is just that, a cell. The cell must contain a formula, otherwise it has no dependency on the inputs. That formula can contain native functions or UDFs. It cannot be something changed by a sub.

 

[Jaymond Flurrie]

The target cell for Solver is just that, a cell. The cell must contain a formula, otherwise it has no dependency on the inputs.

So then I must build an UDF behind that cell to use my VBA code as the numbercruncher instead of just having a couple of ranges of 100x30000 cells? Or what's the smart way to do this?

 

[shg]

I don't know what you're doing, Raymond, so I'd be hard-pressed to guess.

How about a 10,000-foot explanation?

 

[Jaymond Flurrie]

I don't know what you're doing, Raymond, so I'd be hard-pressed to guess.

Trying to make this Solver-problem:
'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

to use as many arrays as possible instead of ranges. I'm just confused about how versatile Solver is as for using arrays instead of ranges.

 

[shg]

I understand that part of it, but have no idea what's behind A3.

 

[Jaymond Flurrie]

I understand that part of it, but have no idea what's behind A3.

A long integer (or can be just normal integer too) that counts it's value based on values in range $A$2:$J$2 by VBA code. The calculation itself works nicely already, I just would need solver to optimize this. Now, if this wouldn't have that complex VBA part behind it, this would be simple, but how do I create the program so that there are VBA arrays in the code included?

Let's just for a simple example take that kind of scenario, that in those $A$2:$J$2 cells each one has to be either 0 or 1 and this $A$3 is the sum of them and we need to find with what combination we get the maximum sum of them. The key here is that you can't use just ranges, you have to begin with reading them into a VBA array from which you somehow count the sum from by using Solver.

 

[shg]

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.