value put in cel adds unwanted decimals

[Negentropy]

Hi,<o></o>
I’m dealing with the following puzzle, and hope there’s someone here that can solve the mystery;<o></o>

(Office 2010) When reading a value from an Access database being the number 0,2 (I’m in a country that uses , to note the decimals) and putting that value into an Excel cell, magically there are numbers added at the end of the decimals. Using Round in VBA doesn’t solve the issue. This is what I’m doing:<o></o>

‘reading the value from the database, vtSql is my SQL code<o></o>
Set rs = db.OpenRecordset(vtSql)<o></o>

‘Put the value in Sheet1 range A1, rs.Value = 0,2<o></o>
Sheets("Sheet1").Range("A1").Value = rs.Value<o></o>

Now if I look in A1 the number there = 0,200000002980232 instead of the expected 0,2000etc<o></o>

What I’ve tried and doesn’t work:<o></o>
1. Sheets("Sheet1").Range("A1").Value =Round( rs.Value, 3)<o></o>
2. Set rs.value to a variable and round that variable <o></o>

What I’ve tried and does work but makes me use extra code, is put the value into range A1 (which has the weird numbers added to the decimals) and then read that value again From range A1, round it, and then put it back:<o></o>
Range("A1").Value = Round(Range("A1").Value, 3)<o></o>

As you can tell I have a solution to the problem, however I’m not too keen on using extra code in programming for something that should work to begin with imho. Also I’m just very curious at what is causing this whole thing. I’m aware the value that’s wrong is very very small however it’s used in conditional formatting and thus messes up de formats of cells, of course there’s also a way around that, which again requires extra code and just makes the whole thing messy.<o></o>

I’m curious if anyone can solve this puzzle. Cheers!<o></o>

Chris<o></o>

 

[Andrew Poulsom]

Does this work?

Sheets("Sheet1").Range("A1").Value = WorksheetFunction.Round(rs.Value, 3)

I expect floating point arithmetic is the cause of your problem.

 

[Negentropy]

Does this work?

Sheets("Sheet1").Range("A1").Value = WorksheetFunction.Round(rs.Value, 3)

I expect floating point arithmetic is the cause of your problem.

Works perfectly sir, thank you

I expect the floathing point stuff has something to do with it, however I still find it strange as the value in VBA appears to be also simply known as 0,2 when checking;
If rs.value > 0.2 Then
MsgBox "bigger"
Else
MsgBox "not bigger"
End If
'result = not bigger, normally meaning it is 0,2 and not 0,2 and a bit
msgbox rs.Value 'this also simply shows 0,2

The whole extra numbers don't seem to come in to play until the value is actually added to the Excel sheet.

Thanks again, you saved me some messy coding

Chris