Retrieve value using vb6 from an excel workbook - precision question

[CodeMonkey]

Hi All
I have a Vb6 app from which I am retreiving a value in a cell in a
worksheet in a workbook.

The code goes something like this:

SomeLabel.caption = MyCells(rownum,colnum).value

Looking directly at the cell in the workbook, I see a value of 3.

However when I debug.print or msgbox MyCells(rownum,colnum).value from
the vb6 app, I find that the value is actually more like 2.9999889889
or something similar.

Whether I format the cell in the workbook as a number to 0 decimal
places, or as General, makes no difference. I conclude from this that
the vb6 code is actuall retrieving the excel *stored* value rather than
the excel *presented* value. Is this correct? How do I retrieve the
value "3" rather than the entire floating point value? Or is the only
way to coerce the data type in the vb6 app using a ceiling like
function or declaring a variable as type integer and assigning it?

Any help appreciated.

Regards
Andrew

 

[Guest]

You are 100% correct!

Something like
Dim v As Variant
v = Cells(1, 1).Value

will pick up what you call the *stored* value. The *displayed* value is
just smoke & mirrors. For example, you can format 1 to appear as a 2.

Your solutions are equally correct.

 

[Bob Phillips]

Andrew,

I think that your conclusions are correct.

Rounding and ceiling is the obvious solution, but don't forget that there
are worksheetfunctions to call upon, such as Round, Roundup, etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Peter T]

Hi Andrew,

These floating point problems also occur in VBA. If you're not concerned
with long decimals maybe coerce to a Single

Sub test()
Dim v
Dim dbl As Double
Dim sng As Single
Dim i As Long

For i = 1 To 10
Cells(i, 1).Formula = "=1/10"
Next

[a11].Formula = "=Sum(a1:a10)" ' should =1, but in XL only approximately

v = [a11].Value
dbl = [a11].Value
sng = [a11].Value

Debug.Print v = 1, v - 1
Debug.Print dbl = 1, dbl - 1
Debug.Print sng = 1, sng - 1

End Sub

 

[CodeMonkey]

Many thanks to all for replying so promptly.

Regards
Andrew