Excel Solver switches Formula Calculation method to manual during long calculations, why?

[Bulrathi]

When I use Excel Solver and it is doing a long loop/calculation (that lasts more then 30 seconds), it will then switch the Formula Calculation method from Automatic to Manual

This will ruin a cell that I use as a constraint, when the constraint goes bad the inputs the Solver is coming up with all of the sudden will make no sense and my entire data becomes useless (I get an overflow)

The issue is that my constraint is a Sum of a range of Changing variables. This works fine for quicker calculations but when I try to go deeper (by switching on Multiple starts) the calculations go much longer and it seems that Excel switches the Formula Calculation method for some reason (crash prevention perhaps?), the sum cell wont update and problems happen

What can I do? If I could set a constraint as logical statement that would capture the sum of the variables directly, this wouldn't be an issue as Solver would not be able to come up with nonsense. but because I use a different cell (with =sum) as constraint, I become vulnerable to the switching of Formula Calculation and I cant really let Solver run for a long-time

Any ideas of what can be done?

[Richard Buttrey]

Are you using VBA to run Solver?

If so see here for one suggestion

[Bulrathi]

Are you using VBA to run Solver?

If so see here for one suggestion

No, I'm pulling it up by hand. But there are triggers in changing of cells that execute a sub on VBA. That sub does not call for Solver though, it just runs calculations. One of the calculations (a ratio) is what I'm trying to solve for

[shg]

Please Login or Register  to view this content.

A sub? You can use a function in a Solver loop; I would not expect a sub to work.

[MrShorty]

We may need more specifics (a sample workbook with all pertinent macros might be best). I do not recall Solver ever switching calculation state on its own.

[Bulrathi]

Please Login or Register  to view this content.

A sub? You can use a function in a Solver loop; I would not expect a sub to work.

It does work and it does give me some results, its just that I want to exaust almost all possibilities to find the optimal solution. how can one use a function in a solver loop?

[Bulrathi]

I already put Application.Calculation = xlCalculationAutomatic in two places inside my Sub, yet, it will still stop and overflow me after 50 seconds me or so. When I debug I see that the inputs to the Changing Variables make no sense (their sum goes over what it should be), the sum cell was not updated and thats what caused the problem

[Bulrathi]

I got 4 Changing Variables whose sums cannot be over 1 (or I get an overflow), I got a sum cell (that adds up those Variables) setup as a constraint. but during the loops for some reason, the sum cell stops updating then Solver will change the variables to things that add up more than 1, the sum goes over 1 and my algo will get an overflow

I tried setting up individual constraints in the Changing Variables to make sure they individually dont go over 1 but its not enough as they can still add up to more than 1 all the way to 4. Is there a way to insert some extra logic in the Constraints?

[shg]

Did you read post #5?