Excel Locking after running macro using solver

Question

Hello,

I'm using solver (multiple times) in a macro and the code works. However, upon completion the target cell (green border) is locked and doesn't move with another cell (green dashed border) responding to mouse and arrow keys but not inputing text. So far I've only been able to force quit to get functionality and I lose the results.

Is there a VBA code to release solver (tried SolverFinish and prompting a message box)?

Frank

<The thread has been moved to the correct category by forum moderator>

Answer 1

It's a heat exchanger so you know input conditions as well as surface area. The solution makes an initial estimate of one exit temperature then solves for the other based on the energy balance (F19). The usuable area is then solved for zero by modifying the initial guess. This then introduces an error in the energy balance, hence the loop, which sets an overall precision.

I don't think it's an infinite loop problem because the message box appears and I can hit the return key to get rid of it but then the cells are still locked.

Is there a release macro function or something?

Answer 2

>...

Do Until ActiveCell.Value < 5

     SolverOk SetCell:="$F$19",...

     SolverOk SetCell:="$F$21", ...

Loop

I don't know what you have set up, but suppose both cells do in fact get set to zero.

If the active cell (whatever that might be) >=5, then the program will run in an infinite loop.

It doesn't sound like your model is set up correctly.

I would start by adding a constraint that F24 <= 5 in the Solver run, not in a macro loop.

Answer 3

Actually it was l24 not l21 that's solid green, interestingly running it again I couldn't even click the message box so it's freezing before that.

Answer 4

Hi.  It's usually something like:

FinishCode = SolverSolve(True)

> ...I'm using solver (multiple times) in a macro

It may also be that you are adding constraints over and over again in a loop.

Make sure the constraints each loop are what you want, and nothing is left over from the previous run.

Thanks for the reply, I think I've tried all that (code below). It's weird to me that I have a locked cell (here l21) but then a cell that responds to the cursor, not sure why they would separate.

Sub Balance()

'

' Balance Macro

'

Range("$f$24").Select

Do Until ActiveCell.Value < 5

    SolverOk SetCell:="$F$19", MaxMinVal:=3, ValueOf:=0, ByChange:="$D$16", Engine _

        :=1, EngineDesc:="GRG Nonlinear"

    SolverSolve True

    Solverreset

    SolverOk SetCell:="$F$21", MaxMinVal:=3, ValueOf:=0, ByChange:="$D$15", Engine _

        :=1, EngineDesc:="GRG Nonlinear"

    SolverSolve True

    Solverreset

Loop

Range("$l$24").Select

Do Until ActiveCell.Value < 5

    SolverOk SetCell:="$l$19", MaxMinVal:=3, ValueOf:=0, ByChange:="$j$16", Engine _

        :=1, EngineDesc:="GRG Nonlinear"

    SolverSolve True

    Solverreset

    SolverOk SetCell:="$l$21", MaxMinVal:=3, ValueOf:=0, ByChange:="$j$15", Engine _

        :=1, EngineDesc:="GRG Nonlinear"

    SolverSolve True

    Solverreset

Loop

MsgBox ("Calculations Complete")

End Sub

Answer 5

Hi.  It's usually something like:

FinishCode = SolverSolve(True)

> ...I'm using solver (multiple times) in a macro

It may also be that you are adding constraints over and over again in a loop.

Make sure the constraints each loop are what you want, and nothing is left over from the previous run.