Mac VBA Solver -- only solves last of three commands

[dmccubbi]

Hi,

I wrote a simple VBA solver macro that I can run on my PC. It is three separate problems with the same setup.

SolverReset
SolverAdd CellRef:="$C$22", Relation:=1, FormulaText:="$C$14"
SolverOk SetCell:="$C$22", MaxMinVal:=1, ValueOf:="0", ByChange:="$C$19"
SolverSolve True

SolverReset
SolverAdd CellRef:="$D$22", Relation:=1, FormulaText:="$D$14"
SolverOk SetCell:="$D$22", MaxMinVal:=1, ValueOf:="0", ByChange:="$D$19"
SolverSolve True

SolverReset
SolverAdd CellRef:="$E$22", Relation:=1, FormulaText:="$E$14"
SolverOk SetCell:="$E$22", MaxMinVal:=1, ValueOf:="0", ByChange:="$E$19"
SolverSolve True

When I try to run the same program on my Mac running Excel for Mac 2011 (ver 14.1.2), it only solves the last of the three. I can reverse the order and it still only solves the last of three. (This runs fine on my PC, so I am wondering if the Mac version is buggy.)

To resolve this, I've looked around on the web and on this forum, but have not found anything useful so far.

Best,
Don

P.S. I did not see how to attach my file, so here are the values I am using in my test spreadsheet...

C14=0.5, C19=0.1, and C22=0.1*C19. I want to adjust C19 so as to maximize C22, with the constraint that C22<=C14. (The solution is 5, in this little example.) Values in columns D and E have the same setup.

 

[zealousteedo]

This occurs because solver in mac office 2011 is run in a separate program and cannot be called iteratively by a loop function in vba code. I spoke with the technical support department of the firm that programmed solver, and they are aware of the issue, and have no intention of correcting it in the near future.

 

[dmccubbi]

Thanks so much!

Best,
Don

 

[superflyben]

Finally, an answer!! I spent far too much time trying to use a macro to loop the solver in the mac version of excel and display the solutions in a series of cells, and all it would ever do is post the same number (present at the beginning from a previous solution) in all the output cells. The counter posted correctly, and the solution from the last iteration showed in the spreadsheet so it looked like everything was functioning and I was somehow just not capturing the updated solution, but zealousteedo's answer explains it (for which I am grateful). Hopefully this post will hit on some additional keywords that might be captured in a search by somebody having similar problems. I should have tried the macro in windows right away before troubleshooting it on the mac.

 

[BobJordanB]

I too followed this path and finally consulted with the Microsoft people. They confirm it is a known problem and that they have no intention of fixing it in the current Mac version of Office (Office 2011).
It may be fixed in future versions and I encourage all people encountering the problem to submit a 'Send Feedback to Microsoft' from their Excel menu bar. By doing this it may be fixed in future versions of Excel.
If you are using earlier versions of OSX then I believe that the multiple calls to Solver technique works with Excel 2004, but that is precluded from later OSXs.
I have posted a very crude workaround on other forums. Try searching on: "VBA macro running Solver fails to update cells"

Roll on Excel 2014

Bob J.

 

[calbears]

I used Goal Seek instead of Solver and it worked like a charm in Mac Excel:

Range("A1").Select
Range("H34").GoalSeek Goal:=Range("H50").Value, ChangingCell:=Range("H8")


Range("A1").Select
Range("Q34").GoalSeek Goal:=Range("Q50").Value, ChangingCell:=Range("Q8")

End Sub

Range("A1").Select
Range("AA34").GoalSeek Goal:=Range("AA50").Value, ChangingCell:=Range("AA8")

 

[BobJordanB]

Unfortunately goal seek adjusts a single parameter to ensure another cell reaches a particular target value

By contrast, Solver adjusts many parameters (in my case typically 10 to 100) to ensure another cell is at its minimum.

So while the problems are related they are not the same.

 

[Jon Peltier]

I've added an approach that uses a Calculation event to handle Solver, rerunning it as desired, at the end of Bob's "Microsoft Answers" thread:

VBA macro running Solver fails to update cells