title: Converting String to Double in Excel / Macro
body: <p>I try create new function in Excel, witch will counting given values (something like SUM function, but only with given prefix). </p>
  
  <pre><code>  A
  ---------    
  1|AA30  
  2|AA10 
  3|BC446 
  4|AA10
  
  // result will be 50  on SUM_PREFIX(A1:A4;"AA")
  </code></pre>
  
  <p>Problem is, when the value is in the form e.g AA10,434  or AA4.43. Could me anyone help me with my problem? This is my first stript in VB.</p>
  
  <pre><code> Function SUM_PREFIX(Data As Range, prefix As String) As Double
  
      Dim result As Double
      Dim strVal As String
      Dim i As Integer
      Dim objRegExp As Object
  
     Set objRegExp = CreateObject("vbscript.regexp")
     With objRegExp
          .IgnoreCase = True
          .MultiLine = False
          .Pattern = "^[" + prefix + "]+[0-9]+(\,|\.)?[0-9]?$"
          .Global = True
      End With
  
      For i = 1 To Data.Rows.Count
          Debug.Print Data.Cells(i, 1)
          If objRegExp.Test(Data.Cells(i, 1)) = True Then
              strVal = Replace(Data.Cells(i, 1), prefix, "")
              Debug.Print strVal
              strVal = Trim(Replace(strVal, ",", "."))
              Debug.Print strVal
              result = result + CDbl(strVal)
          End If
      Next i
  
      SUM_PREFIX = result
  End Function
  </code></pre>
  
  <p>Thanks for help. </p>