Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Numerical accuracy in Form Text-boxes
Ron,
Thanks for this info. It helps explain why I find strange values in my axis limits for my charts. (I see the same effect there) Have been using round functions and stuff to avoid it when changing the axis settings by code. -- sb "Ron Rosenfeld" wrote in message ... On Thu, 21 Aug 2003 22:47:25 +1000, IM wrote: I use a text-box in a form to enter numerical data. When I enter a number, say, 14.6, in the text-box and then have the VBA code transfer the data to my spreadsheet. Sometimes, but not always, the number shown on the spreadsheet is not exactly 14.6, but 14.59999987.. Of course, I could add a few lines to the code to round the number off to 2 or 3 decimal places. But there must a better way. What is it ? Why does this happen ? It happens because VBA of precision limits in VBA and/or Excel; and due to the fact that certain numbers cannot be represented exactly in binary. An example in decimal arithmetic would be the attempt to represent 1/3 in decimal format. There are several solutions depending on your goals: Use the DECIMAL data type. Round. Use an appropriate number format in the Excel cell (this would leave the result as 14.5999999... but display it as 14.60). --ron |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Numerical accuracy in Form Text-boxes
On Sat, 23 Aug 2003 11:04:33 +1000, IM wrote:
Ron Thanks for the reply. I do know exactly how numbers are stored and the cause of round-off errors. But what irks me is the problem of consistency. What I mean by that is if I enter 14.6 in a worksheet cell, the problem I mentioned will never happen. But if the number is extracted from a textbox via VBA, it does. Apparently, Excel by itself works fine in this respect. Only its interfacing with VBA that will bring up the problem. I just wonder if that's an inherent problem with Visual Basic. If yes, Microsoft could handle that easily, since there is no problem with that within Excel. IM Could there be an issue in your code? If I enter 14.6 into an input box, assign it to a variable which has been dim'd as double, and then print it, I get 14.6 Very simply: ============= Sub test() Dim c As Double c = InputBox("Enter a number") Debug.Print c End Sub ============ It seems as if VBA is handling 14.6 OK in this simple routine. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Link Text Boxes on Form? | Excel Discussion (Misc queries) | |||
User Form Text Boxes - Copy format of text boxes | Excel Discussion (Misc queries) | |||
text boxes retaining their last value on form close and restart | Excel Discussion (Misc queries) | |||
convert numerical value to a text form | Excel Discussion (Misc queries) | |||
Form Text Boxes moving | Excel Worksheet Functions |