none
Solver and VBA

    Question

  • Hello everyone,

    I have to maximize a function using solver. I have to do it many times (iterations) and therefore, I would like to code something in VBA. I 've came up with the following code however there is something that is not working. I can maximize my value once using the solver tool however the following loop doesnt seem to work. Here is what I want to do:

    1- SetCell:="$AE$27", MaxMinVal:=3, ValueOf:="E10", ByChange:="$Z$25"
    2- Write the matching value in E10
    3- SetCell:="$AE$27", MaxMinVal:=3, ValueOf:="E11", ByChange:="$Z$25"
    4- Write the matching value in E11

    I know I'm missing the writting part but just can't figure out why. Also, should I use a string?

    --CODE---

    Sub solver_p

    Dim i As Integer, s As String

    For i = 10 To 15 Step 1
    s = Format(i, "0")

    SolverOk SetCell:="$AE$27" & s, MaxMinVal:=3, ValueOf:="E(i)", ByChange:="$Z$25" & s & ":$O$" & s
    SolverAdd CellRef:="$I$10:$I$260" & s, Relation:=4, FormulaText:="2"

    ' SolverAdd CellRef:="$M$" & s, Relation:=2, FormulaText:="$K$" & s & "^2"
    ' SolverOk SetCell:="$I$" & s, MaxMinVal:=1, ValueOf:="0", ByChange:="$N$" & s & ":$O$" & s

    SolverSolve (True)

    SolverReset

    Next i

    End Sub


    Thank you
    Wednesday, March 30, 2011 2:51 AM

All replies

  • I did this for a case where I had some non-linear data and was getting a local minimum.  I would step through creating a new init point and search for an overall minimum.  I am giving the code here.  I don't have time right now to look at your code but maybe this will help.

            While Not done
              SolverReset                              'Init solver
              SolverOptions Precision:=0.001           'Set precision
              ws.Cells(begRow, kVmmc) = ""
              ws.Cells(begRow, kVmdp) = ""
              ws.Cells(begRow, kVmap) = ""
              ws.Cells(begRow, kVk) = k
        'Set location to solve for.  Tell it to solve for minimum, set locations to change.
              SolverOK SetCell:=ws.Cells(begRow, kVsumall).Address(False, False), _
              MaxMinVal:=2, _
              ValueOf:="0", _
              ByChange:= _
                ws.Cells(begRow, kVmmc).Address(False, False) & "," & _
                ws.Cells(begRow, kVmdp).Address(False, False) & "," & _
                ws.Cells(begRow, kVmap).Address(False, False) & "," & _
                ws.Cells(begRow, kVk).Address(False, False)
              SolverSolve UserFinish:=True
              SolverFinish keepFinal:=1           'Tell it to keep solution
              If k < 3000 Then
                If ws.Cells(begRow, kVsumall) < kVsumallVal Then
                    kVsumallVal = ws.Cells(begRow, kVsumall)
                    kVmmcVal = ws.Cells(begRow, kVmmc)
                    kVmdpVal = ws.Cells(begRow, kVmdp)
                    kVmapVal = ws.Cells(begRow, kVmap)
                    kVkVal = ws.Cells(begRow, kVk)
                End If
                k = k + zStep
              Else
                ws.Cells(begRow, kVmmc) = kVmmcVal
                ws.Cells(begRow, kVmdp) = kVmdpVal
                ws.Cells(begRow, kVmap) = kVmapVal
                ws.Cells(begRow, kVk) = kVkVal
                done = True
              End If
            Wend

    Wednesday, March 30, 2011 2:28 PM
  • Thank you for your help I will try to make this work :)
    Wednesday, March 30, 2011 4:23 PM
  • I am not sure I know what you are doing but look at the SolverOK statement.

    SolverOk SetCell:="$AE$27" & s, MaxMinVal:=3, ValueOf:="E(i)", ByChange:="$Z$25" & s & ":$O$" & s

    When i is 10, the above becomes

    SolverOk SetCell:="$AE$2710", MaxMinVal:=3, ValueOf:="E(i)", ByChange:="$Z$2510:$O$10"

    It will be rejected by Solver since E(i) is not something it understands not to mention that the bychange range spans...I don't know how many cells.

    And, of course, it's nothing like what you wrote you wanted, which was:

    SetCell:="$AE$27", MaxMinVal:=3, ValueOf:="E10", ByChange:="$Z$25"

    To get that in your code you should use something like:

    SolverOk SetCell:="$AE$27", MaxMinVal:=3, ValueOf:="E" & i, ByChange:="$Z$25"

    Now, when i is 10 you will get E10 and when i is 11 you will get E11.

    Also, it is possible Solver is stuck at a local optimum.  If your search works the first time around, see if the following works:

    Before the loop save the value of Z25 in some variable.  Then, each time before calling the Solver functions return Z25 to this saved value.

    Hello everyone,

    I have to maximize a function using solver. I have to do it many times (iterations) and therefore, I would like to code something in VBA. I 've came up with the following code however there is something that is not working. I can maximize my value once using the solver tool however the following loop doesnt seem to work. Here is what I want to do:

    1- SetCell:="$AE$27", MaxMinVal:=3, ValueOf:="E10", ByChange:="$Z$25"
    2- Write the matching value in E10
    3- SetCell:="$AE$27", MaxMinVal:=3, ValueOf:="E11", ByChange:="$Z$25"
    4- Write the matching value in E11

    I know I'm missing the writting part but just can't figure out why. Also, should I use a string?

    --CODE---

    Sub solver_p

    Dim i As Integer, s As String

    For i = 10 To 15 Step 1
    s = Format(i, "0")

    SolverOk SetCell:="$AE$27" & s, MaxMinVal:=3, ValueOf:="E(i)", ByChange:="$Z$25" & s & ":$O$" & s
    SolverAdd CellRef:="$I$10:$I$260" & s, Relation:=4, FormulaText:="2"

    ' SolverAdd CellRef:="$M$" & s, Relation:=2, FormulaText:="$K$" & s & "^2"
    ' SolverOk SetCell:="$I$" & s, MaxMinVal:=1, ValueOf:="0", ByChange:="$N$" & s & ":$O$" & s

    SolverSolve (True)

    SolverReset

    Next i

    End Sub


    Thank you

    Tushar Mehta (Technology and Operations Consulting)
    www.tushar-mehta.com (Excel and PowerPoint add-ins and tutorials)
    Microsoft MVP Excel 2000-Present
    Thursday, March 31, 2011 3:59 AM