Retrieve value using vb6 from an excel workbook - precision question
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 |
Retrieve value using vb6 from an excel workbook - precision questi
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. -- Gary's Student "CodeMonkey" wrote: 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 |
Retrieve value using vb6 from an excel workbook - precision question
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) "CodeMonkey" wrote in message oups.com... 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 |
Retrieve value using vb6 from an excel workbook - precision question
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" wrote in message oups.com... 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 |
Retrieve value using vb6 from an excel workbook - precision question
Many thanks to all for replying so promptly.
Regards Andrew |
All times are GMT +1. The time now is 04:06 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com