Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jun 2004
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Use of Solver.xla through VBA (Excel 2003)

    Manipulating solver.xla through vba does not work when the workbook that contains the code is opened with a command button on the toolbar. If the workbook is opened in a standard manner(FIle...Open or double click in a windows folder), the code runs successfully. I need to provide the ability to open the workbook with a tool bar button. The toolbar button does open the workbook but some solver statements such as SolverOptions do not work.

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Use of Solver.xla through VBA (Excel 2003)

    Can you provide detailed information of what you're doing? In a small-scale test I had no problems getting Solver to work from VBA in a workbook opened from a toolbar button.

  3. #3
    New Lounger
    Join Date
    Jun 2004
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Use of Solver.xla through VBA (Excel 2003)

    Items 2 and 8 do not work. The others have not generated the error - "Solver: an unexpected internal error occurred, or available memory was exhausted".

    1. SolverReset
    2. SolverOptions MaxTime:=100, Iterations:=100, Precision:=0.000001, AssumeLinear _
    :=False, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1, _
    IntTolerance:=5, Scaling:=True, Convergence:=0.001, AssumeNonNeg:=False
    3. solverAdd cellref:="$h$5", relation:=1, formulatext:="=0.99*MIN(stressup)"
    4. solverAdd cellref:="$h$3", relation:=3, formulatext:="0.001"
    5. solverAdd cellref:="$h$4", relation:=3, formulatext:="0.001"
    6. solverAdd cellref:="$h$5", relation:=3, formulatext:="0"
    7. solverAdd cellref:="$h$5", relation:=3, formulatext:="0"
    8. SolverOk SetCell:="$H$6", MaxMinVal:=1, ValueOf:="0", ByChange:="$h$3:$h$5"
    9. SolverSolve

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Use of Solver.xla through VBA (Excel 2003)

    One suggestion I found is to include this line at the beginning of the code:

    Application.Run "Solver.xla!Auto_Open"

    See if that gets rid of the problems. For 2, I found the suggestion to set only those arguments that are different from the default settings, in your example

    SolverOptions Iterations:=100, Convergence:=0.001

  5. #5
    New Lounger
    Join Date
    Jun 2004
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Use of Solver.xla through VBA (Excel 2003)

    Thanks for the prompt reply. I'll give your suggestions a try.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •