Help!

I have a spreadsheet with a cell containing the value 0.03

The spreadsheet (in its associated VBA) references a DLL written in VB6, and passes this value (0.03), as type Single, to a function in the DLL.

The function stores the value in a variable of type Single.

The spreadsheet (in its associated VBA) now calls a function in the DLL that returns the value as type Single.

The spreadsheet puts the value into another cell, to check it.

NOW - the value that arrives back in the cell (after passing via the DLL) is 0.0299999993294477

This small error in the value (that is read back from the DLL) occurs for any value entered in the original cell.

What is happening here?

I cannot allow this to happen, because my DLL and spreadsheet MUST use and display EXACTLY the same values. It is critical.

Please can you help?

Thanks.

James

//##

Are you sure you are passing the spreadsheet variable to a variable in the DLL of type Single and not Double? The value 0.0299999993294477 has too high a precision to be Single.

One thought is that your spreadsheet is actually storing 0.0299999993294477 but only displaying 0.03 because of the rounding features in Excel. On return the value is being stored in a bigger cell that is not susceptible to rounding. Try increasing the size of the source cell and remove any formatting to see if this is the problem.

Regards
Owain Williams

Owain,

The value in the original cell is definitely 0.03 exactly (no rounding, no formatting applied to the cell).

The DLL treats the value as Single throughout (without any type changes at any stage).

It's strange... does anyone else have any ideas?

I have the suspect that the number that are passed as Single are then stored and/or displayed as Double. try this
    Dim d As Double
    Dim s As Single
    s = "0.03"
    d = s
    Debug.Print d
the result is 2.99999993294477E-02 that are exactly what you get.
follow Owain's suggestion and try to resize the cells to see what happens
Marco

I think (no sure) that Excel use Double to make the calculation. Mixing Double and Single in calculation is bad. The way that 0.03 is stored in a Single is different from Double. To 0.0299999993294477 is a correct answer according to IEE standard, but is mathematically incorrect.
To avoid this problem, I would suggest that your change your DLL to use Double to make is calculation.

If you want more information on this, search for on "IEEE-754" or "IEEE-754 double precision floating point". I found this site http://babbage.cs.qc.edu/courses/cs341/IEEE-754.html, it show you how the number are store in Single and Double so you can see the difference

Stéphane

Thanks, all.

Yes - the solution is to use Double throughout in the DLL, instead of Single. Works fine now.

Thanks.