I'm not sure if this is an artifact of Excel OLE or Winbatch.
I have a number that requires a high degree of precision. I apply a decimal format to Excel:
objWB.Worksheets('Worksheet 1').Range(objWB.Worksheets('Worksheet 1').Cells(1,1),objWB.Worksheets('Worksheet 1').Cells(1,1)).NumberFormat = "0.00000000000000000000"
In Excel, you can see the required precision I need. However, when I output the value it loses precision down to 8 decimals:
Message('',objWB.Worksheets('Worksheet 1').Range('A1:A1').Value)
How can I get this to output all 20 decimals specified in the format?
Please see the Decimals function in the Consolidated WIL Help file.
That doesn't fix it. The input number has 20 signification digits, yet both code variants below still only show 8 digits. If I multiply the number *1000000 you can see more precision, but still only 8 decimals. I suspect this may be an OLE-based .Value issue...
Decimals(-1)
OutputValues = oApp.WorksheetFunction.Transpose(objWB.Worksheets('Adjust Data - Round 1').Range('P10:S%LastRow%').Value)
Message('',objWB.Worksheets('Adjust Data - Round 1').Range('S13:S13').Value)
Decimals(20)
OutputValues = oApp.WorksheetFunction.Transpose(objWB.Worksheets('Adjust Data - Round 1').Range('P10:S%LastRow%').Value)
Message('',objWB.Worksheets('Adjust Data - Round 1').Range('S13:S13').Value)
There are three factors to consider. The first is the WinBatch will only use 10 digits of precision when converting from a floating point number to a string. This does not affect the value of the floating point number contained in a floating point variable. I should have read your original post a little more carefully and mentioned this.
The second factor is floating point precision. Double precision floating point numbers only have 52 bit for the mantissa which limits the precision and 11 bits for the exponent which limit magnitude. This arrangement is specified by the IEEE-754 standard. A 20 significant digit number cannot be represented at full precision.
The third factor is how Excel and COM represent floating point numbers. I am not an Excel expert but Excel certainly takes advantage of multiple internal representations of numbers. Also COM has 3 ways to represent floating point numbers - R4, R8, and DECIMAL. Consider the following:
Num = 1234567890.0123456789
fltNum = ObjectType('R4', Num )
dblNum = ObjectType('R8', Num )
decNum = ObjectType('decimal', Num)
strText = 'R4: ':fltNum:@CRLF:'R8: ':dblNum:@CRLF:'DECIMAL: ':decNum:@CRLF
fltNum = ObjectType('R4', '1234567890.0123456789')
dblNum = ObjectType('R8', '1234567890.0123456789')
decNum = ObjectType('decimal', '1234567890.0123456789')
strText := 'R4: ':fltNum:@CRLF:'R8: ':dblNum:@CRLF:'DECIMAL: ':decNum
Message('Variant Numbers ':Num, strText)
Best advice is to figure out how Excel is storing your floating point and maybe getting it to return floats as DECIMAL variants.
Perhaps someone with more Excel experience can suggest a better solution.
I think Excel is stuck with 15 decimal precision. Can be extended with the product below which they say is free to use.
http://precisioncalc.com/
A link to a better explanation of IEEE-754 and Excel than my feeble attempt:
https://blogs.office.com/en-us/2008/04/10/understanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers/