• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Convert string to double

Uri

New Member
The following code does not deliver the required results. The separator in the region setting of Windows is defined as ","'. I attempted to overrule it in the code but it does not seem to work.

>>> use code - tags <<<
Code:
Sub str2dbl()
Dim s As String
Dim d As Double

Application.DecimalSeparator = "."
Application.ThousandsSeparator = ","
Application.UseSystemSeparators = False
s = "12.34"
d = CDbl(s)

'd gets the value of 1234

End Sub
Your advice will be very much appreciated!
 
Last edited by a moderator:
Application separators don't affect VBA's coercion functions. What are you actually trying to do?
 
Thanks Debaser, I receive input as a string and have to convert it to a double in order to perform a calculation. The Windows decimal separator is "," and I cannot change it. If I do (for a test) I get the right value. Also if I use the Replace(s, ".", ",") function I get the right result but I wonder if there is a nicer solution
 
Seems pretty clear; he wants to change a string value into a Double numeric type. Uri, I ran just this program:
Code:
Sub Main()
  vs = "12.34"
  vd = CDbl(vs)
  End Sub
...and it says the resulting value of vd is 12.34, which is what I would expect. I don't know what's happening in your program.

But when I look up the documentation for Application.DecimalSeparator (which I've never had occasion to notice before), I notice that in their example they also turn off Application.UseSystemSeparators. Here's my hypothesis: No matter what you set for DecimalSeparators, Excel will continue to use your home system separators (I assume ',' for the decimal separator and '.' for thousands separator) until you also tell Excel that Application.UseSystemSeparators = False.
 
Thanks Bob!
If I change the system separator to "." via the Windows setting then it works for me as well. However, the system separator must remain "," while the string that I receive uses "." as a separator. Excel uses the system separator even when Application.UseSystemSeparators = False . The best solution that I have found so far is to use replace before CDbl
 
Hi, as a reminder, as already written in post #2, VBA understands only the dot as the decimal separator …​
Do not confuse with the system separators on Excel side according to the Windows Regional settings !​
 
It's for the Excel engine - as explained in VBA help for UseSystemSeparators - not for VBA​
as VBA is natively in english whatever for dates or numbers …​
In this thread my VBA procedure forces the decimal separator if necessary before to paste data from web to cells :​
 
My bad : after a test it seems the VBA data type conversion functions - like CDbl, CDate - from a string​
work according to the Windows regional settings without taking care of UseSystemSeparators & DecimalSeparator
like Bob wrote in post #4 …​
VBA engine well understands dot as decimal separator within a string even if the regional settings is set with a comma​
just using the VBA Evaluate function like d = Evaluate(s) …​
Code:
Sub Demo1()
    D# = Evaluate("12.34")
    MsgBox D
End Sub
To conclude : with Evaluate - or Val - with a dot as decimal separator within a string no need to force UseSystemSeparator neither DecimalSeparator …​
 
Last edited:
Aha! Ok, then another way for Uri to do this (albeit a little clumsily) is to paste the text value into a cell and then use one of the WorksheetFunctions to convert it. Maybe.
 
You could just use Val, assuming there are no commas used as thousand separators or currency symbols. Val only recognizes the dot as the decimal separator, unlike CDbl which - like all the conversion functions - always uses your computer's regional settings.
 
I forgot the classic Val I usually use in my codes ! Memory leak - like Excel ‼ - I'm getting old …​
Aha! Ok, then another way for Uri to do this (albeit a little clumsily) is to paste the text value into a cell and then use one of the WorksheetFunctions to convert it. Maybe.
As the VBA engine is natively in english if you use a cell with the Formula property for example then the string is automatically​
converted to a number so no need to use any worksheet function :​
Code:
Sub Demo2()
    With [A1]
       .Formula = "12.34"
        D# = .Value
       .Clear
    End With
        MsgBox D
End Sub
Works as well with Value & Value2 properties in place of Formula …​
Formula is only for native english formulas - like SUM for example - whatever if Excel is a local version like French, German, Italian, Spanish, …​
 
Thank y'all for the useful tips! When "Application.UseSystemSeparators = False" did not work as I expected I wondered what its purpose was but now I understand it! The region settings play indeed an important role but now I have several ways to get the right result. Thanks again!
 
Back
Top