none
VBA Function with Range Argument passed to Excel Solver RRS feed

  • Question

  • I set up a function to run solver with some specific cells but solver seems not to receive the inputs. I consider the arguments as ranges passed by ByVal and ByRef with no success. The solver has already been done directly in the spreadsheet so the problem has a solution.

    Function Garch(control As Boolean, ByRef MyArg As Range, ByRef LogL As Range) As String
    If control = True Then
        Worksheets("Garch").Activate
        SolverOkDialog setCell:=LogL, MaxMinVal:=1, byChange:=MyArg
        SolverAdd cellRef:=persistence, relation:=1, formulaText:=0.9999
        SolverSolve userFinish:=False
        Garch = "Done"
        Else
        Garch = "Not Done"
    End If
    End Function


    Tuesday, January 6, 2015 2:41 PM

Answers

  • I consider the arguments as ranges passed by ByVal and ByRef with no success. The solver has already been done directly in the spreadsheet so the problem has a solution.

    ByVal or ByRef doesn't matter in this case, but I guess you try to use the function as UDF!?
    It is not possible to use the Solver inside a UDF (nor it is allowed to use "Worksheets("Garch").Activate").

    Andreas.

    • Marked as answer by Pedro Cut Tuesday, January 6, 2015 6:09 PM
    Tuesday, January 6, 2015 2:54 PM
  • Use a simple macro with a FOR loop, e.g.:

    Sub Macro1()
      'Sample:
      'SolverOk SetCell:="$AQ$269", MaxMinVal:=2, ValueOf:=0, ByChange:= _
        "$AK$269:$AL$269", Engine:=1, EngineDesc:="GRG Nonlinear"
      Dim i As Long
      'From row 269 to 272
      For i = 269 To 272
        SolverReset
        SolverOk SetCell:=Range("AQ" & i), MaxMinVal:=2, ValueOf:=0, ByChange:= _
          Range("AK" & i & ":AL" & i), Engine:=1, EngineDesc:="GRG Nonlinear"
        SolverSolve True
      Next
    End Sub 

    • Marked as answer by Pedro Cut Tuesday, January 6, 2015 6:09 PM
    Tuesday, January 6, 2015 5:32 PM
  • Thank you
    • Marked as answer by Pedro Cut Tuesday, January 6, 2015 6:09 PM
    • Unmarked as answer by Pedro Cut Tuesday, January 6, 2015 6:09 PM
    • Marked as answer by Pedro Cut Tuesday, January 6, 2015 6:09 PM
    Tuesday, January 6, 2015 6:09 PM
  • Andreas,

    I changed the code as you find below and it worked:

    Sub MacroX()
      Worksheets("GARCH").Activate
      Dim i As Long
      Dim j As Long
        For i = 3 To 3
        j = i * 4 + 2
        SolverReset
        SolverOk SetCell:=Cells(12, j), MaxMinVal:=1, ByChange:=Range(Cells(3, j), Cells(7, j)), Engine:=1, EngineDesc:="GRG Nonlinear"
        SolverAdd cellRef:=Cells(6, j), relation:=1, formulaText:=0.9999
        SolverSolve userFinish:=True
      Next
    End Sub

    Thanks for the help

    • Marked as answer by Pedro Cut Wednesday, January 7, 2015 11:03 AM
    Wednesday, January 7, 2015 11:03 AM

All replies

  • I consider the arguments as ranges passed by ByVal and ByRef with no success. The solver has already been done directly in the spreadsheet so the problem has a solution.

    ByVal or ByRef doesn't matter in this case, but I guess you try to use the function as UDF!?
    It is not possible to use the Solver inside a UDF (nor it is allowed to use "Worksheets("Garch").Activate").

    Andreas.

    • Marked as answer by Pedro Cut Tuesday, January 6, 2015 6:09 PM
    Tuesday, January 6, 2015 2:54 PM
  • Any idea of a way around this? I need to run about 100 times the solver with different cells, any suggestions?
    • Marked as answer by Pedro Cut Tuesday, January 6, 2015 6:09 PM
    • Unmarked as answer by Pedro Cut Tuesday, January 6, 2015 6:09 PM
    Tuesday, January 6, 2015 3:08 PM
  • Use a simple macro with a FOR loop, e.g.:

    Sub Macro1()
      'Sample:
      'SolverOk SetCell:="$AQ$269", MaxMinVal:=2, ValueOf:=0, ByChange:= _
        "$AK$269:$AL$269", Engine:=1, EngineDesc:="GRG Nonlinear"
      Dim i As Long
      'From row 269 to 272
      For i = 269 To 272
        SolverReset
        SolverOk SetCell:=Range("AQ" & i), MaxMinVal:=2, ValueOf:=0, ByChange:= _
          Range("AK" & i & ":AL" & i), Engine:=1, EngineDesc:="GRG Nonlinear"
        SolverSolve True
      Next
    End Sub 

    • Marked as answer by Pedro Cut Tuesday, January 6, 2015 6:09 PM
    Tuesday, January 6, 2015 5:32 PM
  • Thank you
    • Marked as answer by Pedro Cut Tuesday, January 6, 2015 6:09 PM
    • Unmarked as answer by Pedro Cut Tuesday, January 6, 2015 6:09 PM
    • Marked as answer by Pedro Cut Tuesday, January 6, 2015 6:09 PM
    Tuesday, January 6, 2015 6:09 PM
  • Andreas,
    I liked the concept but I need to loop through columns instead of rows, I tried unsuccessfully the code below, any suggestions?

    Sub MacroX()
      Dim i As Long
      Dim j As Long
        For i = 3 To 3
        j = i * 4 + 2
        SolverReset
        SolverOk SetCell:=Range(Cells(12, j)), MaxMinVal:=1, ByChange:=Range(Cells(3, j), Cells(7, j)), Engine:=1, EngineDesc:="GRG Nonlinear"
        SolverAdd cellRef:=Range(Cells(6, j)), relation:=1, formulaText:=0.9999
        SolverSolve userFinish:=True
      Next
    End Sub

    Wednesday, January 7, 2015 10:48 AM
  • Andreas,

    I changed the code as you find below and it worked:

    Sub MacroX()
      Worksheets("GARCH").Activate
      Dim i As Long
      Dim j As Long
        For i = 3 To 3
        j = i * 4 + 2
        SolverReset
        SolverOk SetCell:=Cells(12, j), MaxMinVal:=1, ByChange:=Range(Cells(3, j), Cells(7, j)), Engine:=1, EngineDesc:="GRG Nonlinear"
        SolverAdd cellRef:=Cells(6, j), relation:=1, formulaText:=0.9999
        SolverSolve userFinish:=True
      Next
    End Sub

    Thanks for the help

    • Marked as answer by Pedro Cut Wednesday, January 7, 2015 11:03 AM
    Wednesday, January 7, 2015 11:03 AM