Excel / VBA adding unwanted decimals

[PinkeyNBrain]

Basics of problem:
- Read value from cell into a var:
EX: myvar = .Range("B300").Value ' say val is 0.023
- Copy val into another cell:
EX: .Range("B3").Value = myvar ' B3 now contains 0.0230000000447035


More;
- Excel 2010
- B300 format = General
- myvar is DIMed as Variant
- B3 format = Number

Tried:
- .Range("B3").Value = CDec(myvar) ' And CSng()
- .Range("B3").Formula = CDec / CSng
- .Range("B3").Value = myvar + 0.0 ' Not really 0.0 keeps changing to 0# ???

I'm guessing that it's fallout of VBA trying to convert between different vartypes, but I'm at a loss as to what to do about it.

 

[SkipVought]

Hi,

Nothing is 'adding' ANYTHING to your value. THAT is the value that is in B300 and the SAME VALUE is in B3!

Exactly what do you want?

BTW formatting the cell changes NOTHING! The underlying value is UNCHANGED.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[combo]

In older excel, having exact 0.23 in a cell, I've got 0.0230000000447035 copied when myVar was declared as Single. No difference for Double.
Anyway, there's no exact binary representation for 0.23, so the precision matters.

You could try direct assignment:
.Range("B3")=.Range("B300")


combo

 

[PinkeyNBrain]

combo - I messed around with your thought. I started a totally new workbook and tested the following

Sub test_mac()
   Dim myvar As Variant
   myvar = Range("A1").Value        ' A1 preset to 0.023
   Range("A2").Value = myvar        ' Works as expected
   Range("A3").Value = CDec(myvar)  ' Works as expected
   Range("A4").Value = CSng(myvar)  ' Reproduces what I'm seeing
End Sub

I ended up stepping trough the code and found a location where the value passes through a variable of type Single (call it MySubVar). As soon as the value gets into MySubVar, I can Debug.Print that var and the additional '0000044...' is now part of the orignal value. What I found I can do is

Dim MySubVar as Variant
MySubVar = MyPassedInVar  ' MyPassedInVar is also Variant
.Range(DestCell).Value = CDec(MySubVar)

In an attempt to explain how things reached this point - The code is a little more extensive than a basic macro. I dumed it way down in the initial post to get the concept across. Data is pulled from a SQL database into a sheet. The user can select a row of data and load it into a form, run a few tests against it, then save it if the results are favorable. Passing the data between subroutines in an array of variant records is an easy way to go. In short, using .Range("B3").Value = .Range("B300").Value isn't as easy an option as it appears.

Anyway - thanks combo for the second reference toward type-Single vars.

 

[vbajock]

I had the same problem, I used the format function ex. format(somnum,"0.000") and it fixed it everytime.

 

[PinkeyNBrain]

Definitely will keep that in mind - thanks. Still a bit disconcerting that it is so easy to hit this. I'm in an environment where 6 decimal places of accuracy is standard. We will soon be moving to 8 (ie. Hundred millionths) – that remaining 00447 is starting to get close so to speak. Out of curiosity I tried the test_mac sub on a 64bit PC using 64bit Excel, exact same result. I was hoping the rounding error would have been much less significant.