range.value = var ...unexpected results.

Here's a code snippet of a routine that saves all properties of an object to cells in a row
where r is a range (a single cell), obj is an object, prop is a String containing the name of a property of obj.
For those not familiar with the function, the callbyname function just calls the getter for a property named by a string.. same as var=obj.prop (ie var=objPerson.Name)
In this case the property returns data type Single.

The var variable shows as Variant/Single in the locals window.

Please Login or Register  to view this content.

stepping through the debugger, after the last line of code executes, the immediate window shows the following:
?var
0.565
?r.Value
0.564999997615814

I would like the cell to accurately reflect the value of the property whether the property is text, single, integer, etc.
Anyone know why the change in precision on the assignment?

What exactly are obj and prop?

Can you post the rest of the code and/or attach a sample workbook?

It's not a complete answer, but I think the explanation is the difference between the "double" data type (r.value should be double, since Excel's inherent data type for numbers is double) and the "single" data type. Double precision carries about 15 digits of precision, single carries only 7 or 8 digits. 0.564999997615814 rounded to 8 digits is 0.565.

I don't see it in the code you have posted, but, if you can figure out why "the property returns data type Single." and change it so that it returns data type double, that should fix this particular scenario.

Follow up to my previous post -- it is a simple type conversion issue. single 0.565, when converted to double, is 0.564999997615814. Test code:

Please Login or Register  to view this content.

If you execute the code, you will see (once it gets to the stop statement and enters debug mode) the value stored in dbvar and snvar, and they match what you show. Also note that, if you dim snvar as double instead of single (commented out dim statement), then they both are 0.565 instead of different.

It look like this is a type conversion problem, so you need to figure out how you want to correct the type conversion. If we assume that 0.565000000000000 is the correct result, then we either need some way to force the 0.565 to be double at the outset, or figure out how to convert from single to double without changing the value. A Round() function could probably do the job after the fact, if that is the best way https://msdn.microsoft.com/en-us/lib.../gg278630.aspx

I'm pretty sure that is why you have the change in precision. Best way to correct that probably depends on how readily you can change the dataype returned to var from single to double.

First thought -- is this really an error? The relative error between 0.565 and 0.564999... is about 5 parts per billion, a very small error. Is this error significant? Perhaps nothing special needs to be done at all.

If this is determined to be an unacceptable error, then merely changing the number format is not likely to resolve the issue. Changing the numberformat, only changes the appearance of the value in a cell. It does not change the actual value of the cell. You can make this cell look like 0.565, but it will still contain the value 0.56499999....

If you cannot change this when the value is assigned to var, I might consider a block If or Select Case to branch the code based on data type. Then, each branch of the code can perform the type conversion correctly. rng.value=round(cdbl(var),7)

You might test other data types and see which ones have trouble. Off the top of my head, the other data types (double, long, integer, string) should not have any trouble with type conversion to Excel's two data types (double/string). It might be enough to simply test for typename(var)="single" to handle this special case, and let the other type conversions occur naturally.