Decimal and Thousand separator problems

Question

Hello,

I am using the Swedish version of Microsoft Excel. That means that I am using comma as decimal separator. To avoid confusion, I am using blank as a thousand separator.

When running the following vb-script:

dim xl 

dim xlDoc 

dim xlws 

set xl = GetObject(,"Excel.Application")

set xlDoc = xl.Workbooks.Add

set xlws = xlDoc.ActiveSheet

xlws.Cells(1, 1) = "1,234"

xlws.Cells(2, 1) = "1,23"

xlws.Cells(3, 1) = "'1,234"

xlws.Cells(4, 1) = 1.234

I get the following results:

The decimal separator is set to "," (comma) and the thousand separator is set to " " (blank).

Please note the presceeding quotation mark (') in the row "xlws.Cells(3, 1) = "**'**1,234""

Clearly A1 should either be treated as Text or the decimal separator should be recognized and treated as such. 

The current result is that the cell A1 is 1000 times bigger than expected, and might lead to disaster.

Please help.

Best regards

JS

Answer 1

Try converting the string you get from the database into a number in your code using the Val() function. Then store the value to the cell rather than a string.

ie

If the string you get from the database is called "MyStrVal", something like the following.

Dim MyNumVal as Double

MyNumVal = Val(MyStrVal)

.Cells(1,1) = MyNumVal

Regards

Gordon

Answer 2

I am sorry to say that your answer is not very useful.

The data comes from a database and are stored as strings. It is entered by different users. Some users are in countires with comma as decimal separator and others use dot.

If you enter "1,234" into an excel cell  from a string as shown in the script, Excel changes the value to onethousand twohundred and thirtyfour while "1,23" is treated as text. Similarly, the string "7/4" will become the fourth of july of the current year.

How can I avoid this decimal separator and date formatting pitfall?

Best regards

JS

Answer 3

Literal values within VBA code follow US conventions, regardless of your local system settings. So to enter 1 and 234/1000, you MUST use

xlws.Cells(1, 1) = 1.234

whether your decimal separator is a comma or a point.

Similarly, if you use

xlws.Cells(1, 1) = #10/11/2014#

VBA will enter the 11th of October in cell A1, ignoring your local date format.