VBA convert string into double

[ww4612]

those following numbers are stored as string. I would like to convert them into double. I tried to used VBA recorder. But it does not record the code.
any solutiom?
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]0.0016
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]2,815,993[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]0.02%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1.00
[/TD]
[TD]1.663E-06
[/TD]
[/TR]
</tbody>[/TABLE]

 

[GTO]

I did not try, but have you tried Val()?

 

[ww4612]

yes i did. that does not work for "2,815,993", neither for "0.02%".

 

[GTO]

Stripping any sensitive/personal data, could you post a sample workbook someplace? I don't use personal storage, so if the answer is no, I totally understand; but IMO, taking data as rendered in this web page is likely not as accurate as it would be to see this portion of your workbook.

Mark

 

[Rick Rothstein]

You should be able to use the WorksheetFunction.Text to do the conversions. Consider the following...

Sub Test()

  Dim StringValue As String, DoubleValue As Double
  
  StringValue = "2,815,993"
  MsgBox WorksheetFunction.Text(StringValue, "General")
  
  StringValue = "0.02%"
  MsgBox WorksheetFunction.Text(StringValue, "General")

End Sub

 

[Rick Rothstein]

You should be able to use the WorksheetFunction.Text to do the conversions. Consider the following...

Sub Test()

  Dim StringValue As String, DoubleValue As Double
  
  StringValue = "2,815,993"
  MsgBox WorksheetFunction.Text(StringValue, "General")
  
  StringValue = "0.02%"
  MsgBox WorksheetFunction.Text(StringValue, "General")

End Sub

Actually, the above code is not complete enough... consider this code instead.

Sub Test()

  Dim StringValue As String, DoubleValue As Double
  
  StringValue = "2,815,993"
  If Len(StringValue) Then
    DoubleValue = WorksheetFunction.Text(StringValue, "General")
  Else
    DoubleValue = 0
  End If
  MsgBox DoubleValue
  
  StringValue = "0.02%"
  If Len(StringValue) Then
    DoubleValue = WorksheetFunction.Text(StringValue, "General")
  Else
    DoubleValue = 0
  End If
  
  MsgBox DoubleValue

End Sub

Note 1: The conversion to a Double takes place when VB coerces the String value returned from the Text function to a Double in order to be able to assign it to the Double variable named DoubleValue. If you did not want to use an intermediate variable, you can force the coercion to a Double by passing the value from the Text function into the CDbl function.

Note 2: The test to see if the StringValue has length is necessary because assigning an empty String to a double value, or passing the empty String into the CDbl function will raise an error.

 

[pgc01]

Hi

Try also another option:

Sub Test()
Dim d As Double
Dim s As String

s = "2,815,993"
d = WorksheetFunction.Sum(0 & s)

s = "0.02%"
d = WorksheetFunction.Sum(0 & s)

s = "1.23E+4"
d = WorksheetFunction.Sum(0 & s)

s = ""
d = WorksheetFunction.Sum(0 & s)

End Sub

 

[GTO]

PGC01, would you be willing to explain a bit, or provide a link or two?

With my pathetic understanding of worksheet functions, something + 0 seemed a big discovery.

0 & something is different.

Hope you and yours had a terrific Christmas,

Mark

 

[Rick Rothstein]

PGC01, would you be willing to explain a bit, or provide a link or two?

With my pathetic understanding of worksheet functions, something + 0 seemed a big discovery.

0 & something is different.

If the text string is a number, placing a 0 in front of it (which is what will result from the "0 &" operation) will not change its value when converted to a number (5 becomes 05 after concatenating the leading zero on it which Sum will recognize as a number and convert to the Double 5 afterwards). Doing this concatenation solves the empty String problem I mentioned because it would become 0 after the concatenation which, of course, Sum can handle. The only problem with PGC01's method is it will fail for negative numbers because -5 will become 0-5 (as a text String), not -05, which Sum will not see as a number. PGC01's method will also fail if the text String number has a leading plus sign, is surrounded by parentheses (an alternate form for a negative number), currency values where the currency symbol is located in the front (I am not sure of what it will do with currency where the symbol is in the back), dates where the leading number has two digits (it works if the leading number has only one digit), dates in any form if the month is abbreviated or spelled out, and probably some other values that I haven't thought of... the method I posted will correctly handle all of what I just mentioned.

 

[pgc01]

Hi

Thank you Rick. You are right. This will only work with (some) positive numbers.
This was another touch and go post. I has a minute I came to the board, read a post and posted the first thing that came to mind without really thinking through.
I did some quick testing with some positives and it's good to go. (Not!)
Luckily for us you won't let it go away quietly.

I'd go with Rick's solution.