Do we need to adapt the VBA excel solver to work with German excel?

Question

I'm working on an excel tool which calls an Excel solver from VBA. This tool is used by colleagues in different countries.

I'm trying to improve the old VBA code so that:

• it uses the range names instead of hardcode cell (so that the code will be working even if the worksheet is modified in the future),
• setting lower precision than the Excel's default value (to reduce the run time),
• avoiding the pop-up message at the end (so no need for the user's intervention).

While the new code is working fine for me (using English excel version) and for a French colleague (so probably using French excel version), but it doesn't work for a German colleague. It prompts directly to a wrong answer with the new code (without any error), however a correct answer can be obtained by using the old code. The old and new code are below.

OLD CODE:

    Application.Run "SolverReset"
    Application.Run "SolverOk", "$A$1", 1, "0", "$C$1:$C$3"
    Application.Run "SolverDelete", "$C$2", 3, "$D$1"
    Application.Run "SolverAdd", "$C$2", 3, "$D$1"
    Application.Run "SolverOk", "$A$1", 1, "0", "$C$1:$C$3"
    Application.Run "SolverDelete", "$C$3", 1, "$D$3"
    Application.Run "SolverAdd", "$C$3", 1, "$D$3"
    Application.Run "SolverOk", "$A$1", 1, "0", "$C$1:$C$3
    Application.Run "SolverDelete", "$C$3", 3, "$D$2"
    Application.Run "SolverAdd", "$C$3", 3, "$D$2"
    Application.Run "SolverOk", "$A$1", 1, "0", "$C$1:$C$3"
    Application.Run "SolverSolve", True

NEW code:

    SolverReset
    solverOptions Precision:=0.001
    solverOptions AssumeNonNeg:=False
    SolverAdd CellRef:=Range("rng_param1"), Relation:=3, FormulaText:=Range("rng_param1_lowLimit")
    SolverAdd CellRef:=Range("rng_param2"), Relation:=3, FormulaText:=Range("rng_param2_lowLimit")
    SolverAdd CellRef:=Range("rng_param2"), Relation:=1, FormulaText:=Range("rng_param2_highLimit")
    SolverOk SetCell:=Range("rng_maxValue"), MaxMinVal:=1, ValueOf:=0, ByChange:=Range("rng_ALL_parameters") _
       , Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverSolve UserFinish:=True, ShowRef:="ShowTrial"

Obviously, the range names were added correctly (there is no regression in my tests). So the two codes should give the same answer. And the same file that having issue running on a German PC, is working fine with my computer.

I also tried to comment the line with solverOptions (as it was not done in OLD code and I had some doubt about the number format for the precision value "0.001" if it's understood correctly in the German excel). But it doesn't help.

Could anyone please shed some light on this issue ? I couldn't find any solution from this forum nor from google. Why the old code works but not the new one for my colleague (however all is working fine for me) ? Is there anything to adapt with the German excel version? How can I make the new code running for all international excel version? I'd avoid to ask him to change the excel setting to English version.

Many thanks for your help.

Answer 1

Too long for a comment...

Try running just the lines:

SolverReset
solverOptions Precision:=0.01
solverOptions AssumeNonNeg:=False

...then open up Solver and see if the Precision got set as intended. If it did then that may not be the issue.

Also - try recording a macro in the German locale vs non-German locale while setting up the same Solver scenario manually - do the results differ between locales and how do they compare to your code ?