Solver Optimization

[ferbg]

Hi,
I'd like to know if somebody can explain to me how to use the SOLVER function of Excel running a VBA macro instead of running it from the Tools menu of Excel

Thanks in advance
Bye
Fernando

 

[ETID]

An easy way would be to run the macro recorder while going through running the solver as you would normally, then when complete , click stop and look at the code that was created,

remember the values that you set in the solver

then you can see where you can splice in variables into the code (your values will give you hints) as to where.

 

[ferbg]

Hi ETID, thank you for your quick reply.
Sorry, but I'm not an expert of VBA and my problem is just how to make interact the macro of my program with the solver function. If I launch the solver as I normally do the macro is not runned, so I tried to run the solver function using the following sintax:

The sub Losses Estimation is called by a button in the spreadsheet.

-----------------
Sub Losses_Estimation()

'Clear any previous Solver settings
SolverReset

'Set up the parameters for the model:
SolverOk SetCell:=Range("n37&quot, maxMinVal:=2, ByChange:=("c28,g11,AMAIN()&quot

'Add the constraints for the model.
SolverAdd CellRef:=Range("g11&quot, relation:=3, FormulaText:=45
SolverAdd CellRef:=Range("g11&quot, relation:=1, FormulaText:=70
SolverAdd CellRef:=Range("c28&quot, relation:=1, FormulaText:=g21

'Display the Solver Results dialog
SolverSolve

'Finish and keep the final results
SolverFinish KeepFinal:=1

End Sub


This subroutine performs the calculations

Sub AMAIN()

With Worksheets(3)


PI = 3.1415926
PI2 = PI * 2
N = 101 ' NUMBER OF POINTS IN FI - DIRECTION

'
' GET DATA FOR VANELESS DIFFUSER
'
RO2 = .Cells(12, 3).VALUE
C2 = .Cells(33, 3).VALUE
ALP2 = .Cells(36, 3).VALUE



etc,etc
--------------------
My purpose is that at each step of the solver iteration the sub AMAIN() should be called and runned, while instead it doesn't work.


I don't know how to write the code to get this interaction between the solver and the sub AMAIN().
I hope I've well explained the problem, thak you again.

Fernando

 

[ETID]

alt+f11 toggles between the vba editor and your spreadsheet
once there add the solver reference library...Tools>References>..check solver from list.


INSERT A MODULE....Insert>Module
then paste this code


--------------------------------

Sub solve_it()

Dim x As Long

x = InputBox("solve to what value&quot



SolverOk SetCell:=Range("$A$3&quot, MaxMinVal:=3, ValueOf:=x, ByChange:=Range("$A$1:$A$2&quot
SolverSolve

End Sub


------------------------------------
in my spread sheet


IN A1 (3)
IN A2 (2)
IN A3 =A1*A2 (6)

Then press alt+f8 to run the macro...select "solve_it" then click run.





THEN I RUN THIS MACRO

 

[ETID]

Oh and by the way...you want to start the macro recorder first, then go through the steps of the solver.