Solver (VBA)

bendy_leather2

New Member
Joined
Sep 3, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I've written some code to perform a solver analysis and am running in to problems.
The code works as expected for me (in the UK) but when a colleague of mine uses it in The Netherlands they're getting issues.

The issue appears to be the use of decimal points. UK use a full stop, the Dutch use a comma. If i try and set a value of 0,00001 in the VBA code then solver converts that to 1. If i add it in manually then it works fine!?

I dare say that this is a result of my stupidity...

VBA Code:
Sub RunSolver()
Dim Nme As String
Dim SepStr As String


If InStr(1, 1 / 2, ",", vbBinaryCompare) <> 0 Then
    SepStr = ","
Else
    SepStr = "."
End If

    Nme = ActiveSheet.Name
    
    Sheets("Mortality").Activate
    
    SolverReset
    SolverOk SetCell:="$G$13", MaxMinVal:=1, ValueOf:=0, ByChange:="$C$14:$C$15", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
      
    'Constraints
    SolverAdd CellRef:="$C$16", Relation:=2, FormulaText:="$G$15"
    SolverAdd CellRef:="$G$13", Relation:=2, FormulaText:="$G$14"
    SolverAdd CellRef:="$C$14", Relation:=1, FormulaText:="probability_mortality_Severe_PPH"
    SolverAdd CellRef:="$C$14", Relation:=3, FormulaText:="0" & SepStr & "000001"
    SolverAdd CellRef:="$C$15", Relation:=3, FormulaText:="0" & SepStr & "00001"
    
    SolverSolve True
    
    Sheets(Nme).Select
End Sub

Any suggestions would be much appreciated.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top