Excel Solver function run in a column with input variables

Question

Function Obj_Fnc_Colmn(IN1 As Range, OP1 As Range)
Application.Run "Solver.xlam!Auto_Open"
SolverReset
X_var = IN1.Address
Y_var = OP1.Address
MsgBox X_var
SolverOK SetCell:=Y_var, MaxMinVal:=3, ValueOf:="0", ByChange:=X_var
SolverAdd CellRef:=X_var, Relation:=3, FormulaText:="0"

SolverSolve UserFinish:=False
End Function

Intention of the program is to input different set of objectives, change variables in multiple columns.

Please go through my code. Keep generating error. Same program would like to use for multiple columns.

I believe the problem is Solver.xlam!Auto_Open. If you want to make sure the Solver add-in is enabled and reachable from VBA use the code here: Preparing Solver for first use. HTH — kindoflost
– kindoflost, Commented Apr 15, 2016 at 19:51
I have tried what you suggested, but did not work. Is there any option. — GSAT-82
– GSAT-82, Commented Apr 16, 2016 at 10:00
where does it break? try commenting out that line and see what happens. — kindoflost
– kindoflost, Commented Apr 16, 2016 at 18:28
program runs with out trouble when we directly use address like "H17" for setcell and "H12" for BChange. But if we take input through function call it generates error. "Solver: An unexpected internal error occurred, or available memory was exhausted" — GSAT-82
– GSAT-82, Commented Apr 17, 2016 at 4:53
Excel does not allow a user defined function to change a cell, worksheet, or workbook properties. You will need to do this using a Sub, or possibly a change event. — OldUgly
– OldUgly, Commented Apr 19, 2016 at 8:05

Community · Accepted Answer · 2017-05-23 12:22:39Z

0

Based on this, I would use the following sub (I did test it, yes it works even without the Application.Run line) ...

Option Explicit
Sub Obj_Fnc_Colmn()
Dim IN1 As Range, OP1 As Range
Dim X_Var As String, Y_Var As String

    Set IN1 = Sheets("Sheet1").Cells(2, 4)
    Set OP1 = Sheets("Sheet1").Cells(2, 5)

    X_Var = Split(IN1.Address(external:=True), "[")(0) & Split(IN1.Address(external:=True), "]")(1)
    Y_Var = Split(OP1.Address(external:=True), "[")(0) & Split(OP1.Address(external:=True), "]")(1)

    SolverOK SetCell:=Y_Var, MaxMinVal:=3, ValueOf:=0, ByChange:=X_Var, Engine:=1
    SolverAdd CellRef:=X_Var, Relation:=3, FormulaText:=0
    SolverSolve UserFinish:=True

End Sub

Things to note: It does not use SolverReset. The strings used for SetCell, ByChange, and CellRef contain the Sheet reference (not strictly necessary, but helps avoid trouble). ValueOf and FormulaText are NOT text, but numbers.

edited May 23, 2017 at 12:22

CommunityBot

11 silver badge

answered Apr 19, 2016 at 8:14

OldUgly

2,1393 gold badges14 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel Solver function run in a column with input variables

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related