How does Solversolve Control Macro

  • Thread starter Thread starter Dacvid Cardner
  • Start date Start date
D

Dacvid Cardner

Solver manipulates input variables to make a target
function in a cell achieve a maximum, minumum or specified
value.

However, if the target value is to be generated by a 500
line VB Visual Basic program, how does one get the Solver
to drive the VB program? Is this possible? I've tried
ShowRef:="Validator II", which is the start of the VB
program, but the system just twitches a few times and
exits. Setting PassThru:= does not change anything.
 
Hi David. Did you ever manage to get a response/solution to this question? I have the exact same problem and just can't get it figured out. I have a macro written in VBA (called “BatchRun”) that needs to be run at every iteration that the solver goes through. I’ve tried using the ShowRef function in my VBA code, but can’t seem to get this work. Any advice would be HUGELY appreciated. The code I’m using to run the Solver via VBA is as follows.


Sub OptimalTriggerPercentageSolver()



' Clear any previous Solver settings

SolverReset



'Set solver options to enable stepthru so that macro can run at each iteration

SolverOptions StepThru:=True



' Set up the parameters for the model.

' Set the target cell L8 to maximum value by changing cell F3 and running the BatchRun Macro.



SolverOK SetCell:=Range("L8"), MaxMinVal:=1, _

ByChange:=Range("F3:F4")



' Solve the model but do not display the Solver Results dialog box.

SolverSolve UserFinish:=True, Showref:="BatchRun"



' Finish and keep the final results.

SolverFinish KeepFinal:=1



End Sub





Thank you!!!!!



Alex
 
Hi

I am having the exact same trouble. I did find an alternative but not elegant way to do it. The problem is that using a macro that calculate the value remove the fact that there is a formula in the cell, which seems to be the problem.
In my particular case, I have several variables but one that change for every line (namely frequency), plus the variable(s) to fit (or ByChange value if you prefer).
I just defined a function that does the calculation I need but which requires the my frequency and my ByChange variables as entries. As a consequence, at every solver iteration, the ByChange data are changed, and so the cell that call the function. Solver can therefore do its job.

I believe the problem is coming from this necessity of having an equation that is affected by the change of the ByChange data. As soon as this function is disabled (case if a macro does the work and return a double in the cell), the solver does not work. The only option I see would be to modify the SolverSolve function itself but I am not a specialist.

Anyway, this is the reason why, I believe, the ShowRef:="Zimm" in my case, did not work, and it is for yours too I believe. You simply need something that modifies itself in the Ceel when ByChange data is modified by SolverSolve

Hope this help
 
Back
Top