Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Solver Macro (2002)

    I ran Excel's Solver and set a target cell, the constraints and the variable cells, and it all ran perfectly.

    However, I would now like users to be able to change the variable cells and for the Solver to recalculate automatically.
    I don't want users to have to type <Tools> <Solver> and re-input all the constraints etc.

    Does anyone know of vba code to do this.

    Thanks

    Robert

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

    Re: Solver Macro (2002)

    Calculating a solution uses a lot of resources, so I don't think it is a good idea to let the Solver recalculate automatically each time one of the input cells is changed. Instead, I'd do the following:
    - Set a reference to the SOLVER in Tools | References... in the Visual Basic Editor. If you've already set up the Solver, the references should be near the top of the list, so you only need to tick its check box and click OK.
    - Create the following simple macro:
    <code>
    Sub SolveIt
    SolverSolve True
    End Sub
    </code>
    - Create a command button from the Forms toolbar on the worksheet and assign the <code>SolveIt</code> macro to it.

    The user can click the command button after having changed one or more input cells.

  3. #3
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Solver Macro (2002)

    I would never have thought to reference the Solver Add-in
    That works perfectly. Thanks as always, Hans.

    Regards

    Robert

Posting Permissions

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