SolverReset: how to avoid Objective Cell warning?

Question

I am calling SolverReset in a macro.

However, if a previous use of Solver leaves a reference to an Objective Cell that is subsequently deleted (i.e. delete the column or row), we see the warning "Object Cell must be a single cell on active sheet" before SolverReset does its thing to avoid such problems.  (Klunk!)

Is there some way to avoid or hide the warning when trying to reset Solver?

Application.DisplayAlerts = False did not hide the warning.

I can call SolverReset after my last use of it the macro.

But that does not protect me from #REF errors left over from using Solver manually, then deleting the referenced column or row.  Besides, usually I want to leave the last Solver parameters set for various reasons.

Answer 1

Hi.  Solver stores a lot of information on your workbook & references.

I believe it's best to do a SolverReset 'First, before you delete stuff on the workbook.

However, if I understand  your question, a workaround is to give it a valid "dummy" reference prior to Reset.

    SolverOk [A1], 1, , [A1]

    SolverReset

Good luck.  :>)

Answer 2

Glad it worked.   :>)

I suppose one could have a custom Solver Reset...

Sub ResetSolver()

'// Reset Solver if Obj Cell is deleted

    With Application

        .Run "SolverOk", "A1", 3, 0, "A1"

        .Run "SolverReset"

    End With

End Sub

Answer 3

DanaD wrote:

a workaround is to give it a valid "dummy" reference prior to Reset.

    SolverOk [A1], 1, , [A1]

    SolverReset

Thanks.  That works!  I had thought of doing that, too, but I a.s.s-u-me-d it wouldn't work because:

1. I get the warning when I manually open Solver, before I could manually add a dummy reference.  So I thought the same would apply to SolverOk as well.  (Not!)
2. And theoretically, bad references could be in the constraints field.  I have no hope of deleting those, I thought, before Solver would complain.  That's why I'm doing SolverReset, in part.

But surprisingly, the following doesn't result any warnings, even though there are #REF errors in the constraints field.

    Application.Run "SolverOk", mySetCell, 3, 0, myChangeCell

    Application.Run "SolverReset"

Amazing!  Thanks again.