PDA

View Full Version : [SOLVED] Some explanation requested on converting string to double.



William
04-16-2014, 02:57 AM
Hello,

I'm a complete newbie when it comes to understanding the fundamentals of VBA. I have recently started a development and come across a perplexing issue (for me anyway) and I would like it if someone could explain or try and answer my questions set out below.

I have 2 examples that I would like some assistance on:

Firstly the code below works perfectly fine and converts the string value to a Double.



Sub TestConvertStringToDouble()


Dim ConvertedVal As Double

ConvertedVal = CDbl("6061776101")


End Sub


Mouse over the ConvertedVal variable in debug shows that the value is 6061776101 after stepping to the End Sub code.

1.



Sub TestConvertStringToDouble()


Dim ConvertedVal As Double

ConvertedVal = CDbl("6061776101.01")


End Sub


The above code gives a runtime error 13 "Type Mismatch", perhaps someone can explain to me why adding a decimal point to the string value has caused this error and what the correct way of converting the string value is?

2.



Sub TestConvertStringToDouble()


Dim ConvertedVal As Double

ConvertedVal = 6061776101.01


End Sub


This code runs successfully but interesting to note that when I mouse over the ConvertedVal variable the value shown is 6061776101,01 and not 6061776101.01. Why would there be a comma in the value instead of a decimal point?

Also when I write the ConvertedVal to a cell it still writes the comma instead of a decimal point.

I think the answers to the above questions will help me understand some of the fundamentals of data conversion in VBA.

Regards,
William

Bob Phillips
04-16-2014, 03:14 AM
What is happening here William is that you have a non-US/UK version of Excel which uses a comma as the decimal separator, not the period. So even though Excel works on US format variables, as soon as you cast it it uses Excel's formats, so it is seeing 6061776101.01 as 6061776101,01 which is not a number in VBA.

William
04-16-2014, 03:30 AM
Thank you xld. I'm using Office 365, do you know how I can get the editor to recognise the period as the decimal separator?

William
04-16-2014, 03:45 AM
Thank you xld. I'm using Office 365, do you know how I can get the editor to recognise the period as the decimal separator?

I managed to find the setting. Open Excel Options, select Advanced and then untick "Use system separators", then change the separator from comma to period.