# value put in cel adds unwanted decimals

#### Negentropy

##### New Member
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>

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

#### Andrew Poulsom

##### MrExcel MVP
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

##### New Member
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

Replies
2
Views
155
Replies
1
Views
251
Replies
2
Views
235
Replies
3
Views
313
Replies
5
Views
257

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,517
Messages
5,832,225
Members
430,117
Latest member
RRattle

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back