Change VBA Decimal Separator

[JacobsMaarten]

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.

 

[Norie]

Jacobs

How exactly are you doing the 'cast'?

Are you using something like Val or CDbl?

What decimal separator do you actually want to use/allow?

 

[JacobsMaarten]

Norie,
the cast is done with CDbl.

By default my computer is set to use "," as a decimal separator (as is customary in Belgium, Europe).

However, I've set Excel to use "." as a decimal separator. And this is the decimal separator that I would like to use. All (futher) users of the workbook have their Excel set this way.

Basically, a solution to my problem would be if either
a) I could make sure that a "0.002" string is casted to 0,002, as VBA seems to use "," as a decimal separator, although that if I would enter assign a value to a double through a hard entry in my code, I need to use

dim dbl as double
dbl=0.002

,or
b) VBA just uses "." as a decimal separator, so that "0.002" stays 0.002 when casted and does not become 2.

 

[Norie]

As far as I know VBA uses . for the decimal separator, no matter what your settings.

And I still don't see how you are ending up with 2 from 0.002.

 

[RoryA]

CDbl uses settings from your system locale, so it ignores Excel settings.