title: Excel VBA: Read decimals as "," and convert it to "." while calculating
body: <p>I'm conducting an optimization using <em>Solver</em> and I've had some issues now with decimals. Using a Swedish Excel my default decimal point is a comma "," but I've read from forums that <em>Solver</em> need to have decimals in the form of a period sign "." </p>
  
  <pre><code>Private Sub CommandButton1_Click()
    Worksheets("Blad1").Activate
    SolverReset
  
    SolverOk SetCell:=B63, MaxMinVal:=1, ByChange:=Range("B45:B62")
  
    SolverAdd CellRef:=B45, Relation:=1, FormulaText:=P24        
    SolverAdd CellRef:=B45, Relation:=3, FormulaText:=O24
    SolverAdd CellRef:=L45, Relation:=1, FormulaText:=L3
    SolverAdd CellRef:=L46, Relation:=3, FormulaText:=L4
    SolverAdd CellRef:=B63, Relation:=3, FormulaText:=B42      
    SolverSolve
  End Sub
  </code></pre>
  
  <p>The above is my code with in short (there are many more constraints in reality). Here all cell references are to cells containing decimal number with delimiter ",". I tried to add a Custom Format of the type "0.00" (if it's not possible to convert the decimals to periods and convert it back to commas in the answer, then that's not the optimal solution but an OK solution) but it resulted in integers, say 3, becoming "0.03" where the intention were to have it like "3.00". </p>
  
  <p>Is there a way to read a decimal number having "," convert it to "." while doing calculation and subsequentially provide the answer with "," without having to "globally" change the decimals from commas to periods, as I have many Excel workbooks which will not work as intended using ".". </p>
  
  <p>Thanks in advance</p>
  
  <p>EDIT: Tested some more now and I realize that the Solver actually <em>can</em> "handle" comma decimals. Indeed Solver can print a solution consisting of comma decimals, however it appears that some functions, for instance SolverSolve, is based around end point decimals. I tested to implement the following in a new Workbook:</p>
  
  <pre><code>Sub Makro1()
     Dim MyNumber1 As Double
     Dim MyNumber2 As Double
  
     Set MyNumber1 = Sheets("Blad1").Cell(20, 1).Value
     Set MyNumber2 = Sheets("Blad1").Cell(21, 1).Value
     Dim i As Integer
     Dim rSetCell As Range, rByChange As Range, rPrecision As Range
     Range("A1").Resize(1, 4) = Array("Precision", "c", "b", "a")
     For i = 1 To 16
         Set rPrecision = Range("A1").Offset(i, 0)
         Set rSetCell = Range("A1").Offset(i, 1)
         Set rByChange = Range("A1").Offset(i, 2).Resize(1, 2)
         rSetCell.Formula = "=SQRT(" &amp; rByChange(1).Address &amp; "^2+" &amp; rByChange(2).Address &amp; "^2)"
         rPrecision = 10 ^ -i
         SolverReset
         SolverOk MyNumber1, 3, 5, rByChange.Address
         SolverOptions Precision:=rPrecision.Value
         SolverAdd rByChange(1).Address, 4
         SolverAdd MyNumber1, 1, MyNumber2
         SolverSolve True
     Next
   End Sub
  </code></pre>
  
  <p>(Where the vast majority of code is taken from Andreas Killer at <a href="http://answers.microsoft.com/en-us/office/forum/office_2010-customize/excel-solver-macros-do-not-work-when-using-comma/8b595198-8492-4382-89c4-5fc144f7bc43?page=1" rel="nofollow">http://answers.microsoft.com/en-us/office/forum/office_2010-customize/excel-solver-macros-do-not-work-when-using-comma/8b595198-8492-4382-89c4-5fc144f7bc43?page=1</a> in order to check whether the program can print comma decimals, which it could). </p>
  
  <p>Adding <code>SolverAdd MyNumber1, 1, MyNumber2</code> where MyNumber1 is cell A20, which is 2,3 and MyNumber2 is A21 which is 2,5. Hence, the solution would not be feasible if the program actually could manage comma decimals (these parameters are never changed, and the constraint is always violated). Checking the TypeName Before adding the Dim Double for the two variables showed that A20 where, in fact, recognized as an String. </p>
  
  <p>Trying to implement this solution gives me the error <code>Object Required</code> and a highlight on the code snippet <code>Sub Makro1()</code>. Why won't this work? Has anyone a clue how to make this work without needing to change the system language for all Excel workbooks, so I can avoid to adjust every Excel sheet I've ever created containing formulae?</p>
  
  <p>EDIT: 
  <code>Set MyNumber1 = Sheets("Blad1").Cell(20, 1).Value</code> and <code>Set MyNumber2 = ..</code> both gives the error <code>Object Required</code>. </p>