CODEWITHSUNDEEP
vbaexcelformat
JFS
JFS

Reputation: 3152

Error using "Format" double with three decimal

I have a weird behavior using Format in my VBA code. I have a double number that should be converted to text and written in a cell. As soon as I use three decimals to be shown the number 1.0076237755 is converted wrongly:

thisSheet.Cells(...) = Format(cellValue, "#,##0.000")

is showing 1008,000 (German number location with decimal separator ",")

with using only two decimals it somehow works:

thisSheet.Cells(...) = Format(cellValue, "#,##0.00")

is showing 1,01 (German number location with decimal separator ",")

I have no idea what I'm missing here. I never saw that before and I use Format a lot. Any advice?

Upvotes: 1

Views: 233

Answers (2)

R3uK
R3uK

Reputation: 14537

I've tried quite a few possibilities with Format but it doesn't seem to work properly on its own.

If you add thisSheet.Cells(...).NumberFormat = "#,##0.000", it will display what you want but you'll still have the whole value stored (in .Value2)

So I decided to try with Round function, and it does work properly :

thisSheet.Cells(...) = Round(CDbl(Replace(Cel, ".", ",")), 3)
thisSheet.Cells(...) = Format(Round(CDbl(Replace(Cel, ".", ",")), 3), "#,##0.000")

Upvotes: 1

Alex Weber
Alex Weber

Reputation: 448

According to this page, it sounds like in VBA, the decimal separator is always a period. You can get around this, using the code below. 

thisSheet.Cells(...) = Replace(Format(cellValue, "#,##0.000"), ".", ",")

Since the decimal separator is now a string, any action to that cell will treat it as if it were a thousands separator, and not a decimal. But this should give you what you want.

Upvotes: 1

Related Questions