JacobsMaarten
New Member
- Joined
- Apr 5, 2007
- Messages
- 30
Questions
- Is there a way to change the decimal separator of VBA?
- Is there a way to define the decimal separator of a Listbox?
(Like you can define in Excel that it should use either a dot or a comma as separator)
Background info & Problem
I use inputboxes and some forms to have the user enter some doubles. However, as all numbers are returned as text, 0.022 becomes "0.022". When I now cast in VBA this string to a double, it becomes 22.
Tried Solutions
I tried the following solutions:
- Setting the decimal separator in Excel options>international to " . ". (Here in Belgium, we normally use " , " as a separator.)
- Replace (String, " , " , " . ") and Replace (String, " . " , " , ")
- Format / Numberformat "#.##0,00", "#,##0.0#" and so on (trial & error they call this)
- Setting the language that controls the default behaviour of the office applications to English (UK) instead of Dutch.
Of these 3 methods, only the "replace"-method gives sometimes good results. Why only sometimes? Because the listboxes, where the number are stored, sometimes use a " , " as decimal separator and sometimes a " . " as decimal separator... Why this inconsistency? I have no clue.
Excel Info
I use Excel 2003 SP3 on Windows XP. It is an English version of Excel.
(So, it is an example of the classical separator problem, as discussed in many threads, only I haven't found a solution that works for me)
Any help is highly appreciated.
- Is there a way to change the decimal separator of VBA?
- Is there a way to define the decimal separator of a Listbox?
(Like you can define in Excel that it should use either a dot or a comma as separator)
Background info & Problem
I use inputboxes and some forms to have the user enter some doubles. However, as all numbers are returned as text, 0.022 becomes "0.022". When I now cast in VBA this string to a double, it becomes 22.
Tried Solutions
I tried the following solutions:
- Setting the decimal separator in Excel options>international to " . ". (Here in Belgium, we normally use " , " as a separator.)
- Replace (String, " , " , " . ") and Replace (String, " . " , " , ")
- Format / Numberformat "#.##0,00", "#,##0.0#" and so on (trial & error they call this)
- Setting the language that controls the default behaviour of the office applications to English (UK) instead of Dutch.
Of these 3 methods, only the "replace"-method gives sometimes good results. Why only sometimes? Because the listboxes, where the number are stored, sometimes use a " , " as decimal separator and sometimes a " . " as decimal separator... Why this inconsistency? I have no clue.
Excel Info
I use Excel 2003 SP3 on Windows XP. It is an English version of Excel.
(So, it is an example of the classical separator problem, as discussed in many threads, only I haven't found a solution that works for me)
Any help is highly appreciated.
