|
Excel VBA ".value" / Strange Behaviour!!!
Using Excel 2002 and VB6.3
I type just four numbers 1, 8, 5 and 2. That is 1852 in A1. NO FORMATS AT ALL. JUST FOUR NUMBERS. A ------------- 1 | 1852 | ------------- I use VBA Immediate Window to do the following ? Range("A1").Value 1852.00034359738 <<<< Load of crap. and it returns some unnecessary numbers after the decimal point. --- Using Excel 2000 and VB6.0 I do the same thing... ? Range("A1").Value 1852 <<< Works as expected ---- I am very much annoyed with this behaviour as I have to export lots of these values to an access database and each of those values are garbled after the decimal point. Can someone please explain the reason for this strange behaviour? This problem is strangely new to me as I haven't experienced this before. Is it 1) a bug in the version of excel or vba (or) 2) a configuration thingy? |
Excel VBA ".value" / Strange Behaviour!!!
With Excel 2002 (XP) SP3 and VBA 6.3 I can't replicate it.
If this happened normally we would know about it, so it sounds like there is something quite wrong. Can't imagine that this has anything to do with configuration. It's not a bug that would have survived for so long without a fix. I'm not sure where that leaves you. Anyone else have an idea before a reinstall? Robin Hammond www.enhanceddatasystems.com "ifiaz" wrote in message oups.com... Using Excel 2002 and VB6.3 I type just four numbers 1, 8, 5 and 2. That is 1852 in A1. NO FORMATS AT ALL. JUST FOUR NUMBERS. A ------------- 1 | 1852 | ------------- I use VBA Immediate Window to do the following ? Range("A1").Value 1852.00034359738 <<<< Load of crap. and it returns some unnecessary numbers after the decimal point. --- Using Excel 2000 and VB6.0 I do the same thing... ? Range("A1").Value 1852 <<< Works as expected ---- I am very much annoyed with this behaviour as I have to export lots of these values to an access database and each of those values are garbled after the decimal point. Can someone please explain the reason for this strange behaviour? This problem is strangely new to me as I haven't experienced this before. Is it 1) a bug in the version of excel or vba (or) 2) a configuration thingy? |
Excel VBA ".value" / Strange Behaviour!!!
ifiaz wrote:
Using Excel 2002 and VB6.3 I type just four numbers 1, 8, 5 and 2. That is 1852 in A1. NO FORMATS AT ALL. JUST FOUR NUMBERS. A ------------- 1 | 1852 | ------------- I use VBA Immediate Window to do the following ? Range("A1").Value 1852.00034359738 <<<< Load of crap. and it returns some unnecessary numbers after the decimal point. --- Using Excel 2000 and VB6.0 I do the same thing... ? Range("A1").Value 1852 <<< Works as expected ---- I am very much annoyed with this behaviour as I have to export lots of these values to an access database and each of those values are garbled after the decimal point. Can someone please explain the reason for this strange behaviour? This problem is strangely new to me as I haven't experienced this before. Is it 1) a bug in the version of excel or vba (or) 2) a configuration thingy? -------------------------------------- Ignoring VBA for the moment, if you look at the cell on the spreadsheet what do you see? If you format it to show you lots of decimal places? Bill |
Excel VBA ".value" / Strange Behaviour!!!
Trust me. It is a new blank workbook and worksheet. I just type 4
numbers 1, 8, 5 and 2 without any formatting. For your info, There are no decimal places even I deliberately format it. I know what I am doing in excel, so be assured that the cell contains just four numbers. Following procedure prints 1852.00034359738 in the immediate window --- Sub BugTest() Range("A1").Value = 1852 Debug.Print Range("A1").Value End Sub --- So Is reinstall the only option? |
Excel VBA ".value" / Strange Behaviour!!!
ifiaz,
Can't reproduce it in Excel 2000. Have you tried the Detect and Repair ? NickHK "ifiaz" wrote in message oups.com... Trust me. It is a new blank workbook and worksheet. I just type 4 numbers 1, 8, 5 and 2 without any formatting. For your info, There are no decimal places even I deliberately format it. I know what I am doing in excel, so be assured that the cell contains just four numbers. Following procedure prints 1852.00034359738 in the immediate window --- Sub BugTest() Range("A1").Value = 1852 Debug.Print Range("A1").Value End Sub --- So Is reinstall the only option? |
Excel VBA ".value" / Strange Behaviour!!!
NickHK wrote: ifiaz, Can't reproduce it in Excel 2000. Have you tried the Detect and Repair ? NickHK I did Detech and Repair, same result. I did a Reinstall, same result. What should I do. Should I just uninstall office xp completely from the system and start over again. Please advise. |
Excel VBA ".value" / Strange Behaviour!!!
I did a complete UnInstall and a fresh install. Still it doesn't work.
I hope some experts out there suggest me how I may go about fixing this problem. |
Excel VBA ".value" / Strange Behaviour!!!
Is this problem unsolvable?
Is there no one who can help me out? wrote: I did a complete UnInstall and a fresh install. Still it doesn't work. I hope some experts out there suggest me how I may go about fixing this problem. |
Excel VBA ".value" / Strange Behaviour!!!
Hi,
I suppose that probably no one had experienced that problem. the number 1852.00034359738 seems like the result of conversion from a single precision floating-point number to a double precision. for instance, Sub Test() MsgBox CDbl(CSng("1.852")) * 1000 End Sub but AFAIK, the Value property never does so. What does this macro output? 'assuming 1852 was typed in A1 Sub Test2() On Error Resume Next Application.EnableEvents = False Debug.Print Range("A1").Value Debug.Print Range("A1").Formula Debug.Print Range("A1").NumberFormat Range("A2").Clear Range("A2").Value = 1852 Debug.Print Range("A2").Value Debug.Print Range("A2").Formula Debug.Print Range("A2").NumberFormat Application.EnableEvents = True End Sub how about testing in the safe mode (start excel.exe with /s switch) see: How to troubleshoot startup problems in Excel http://support.microsoft.com/default...b;en-us;280504 -- HTH, okaizawa |
Excel VBA ".value" / Strange Behaviour!!!
Hi,
--- Test 1 returns 1851.99986485288 --- Test 2 returns 1852.00034359738 1852 General 1852.00034359738 1852 General --- One additional problem I noticed today is when I want to type in the code window for example: TestVar = 350.23 What actually happens is... Once I finish entering the last '3' and click [enter], the code window shows as below: TestVar = 350.228178550713 THIS IS VERY STRANGE!!! ---- I started Excel in Safe Mode by using the [Start] - [Run] - EXCEL.EXE /S - [Enter]. Excel opens in [Safe Mode] The results are same as above. No improvements at all. --- Please give me some more suggestions so I may try. Thanks. |
Excel VBA ".value" / Strange Behaviour!!!
This
Sub Test() Debug.Print 1 / 3 End Sub returns 0.333331383035028 --- I tried VBA in MSWord and the behaviour is the same as in Excel. --- I have cygwin(unix)-Perl in my pc and it returned. $ perl -e 'print 1/3;' 0.333333333333333$ --- I have no idea what you want me to do about _controlfp function. Also, your briefcase folder seems to be empty. Thanks. |
Excel VBA ".value" / Strange Behaviour!!!
Do you have a PC with the Intel floating point bug in the chip?
My Excel VBA gives 0.333333333333333 as expected. wrote in message ups.com... This Sub Test() Debug.Print 1 / 3 End Sub returns 0.333331383035028 --- I tried VBA in MSWord and the behaviour is the same as in Excel. --- I have cygwin(unix)-Perl in my pc and it returned. $ perl -e 'print 1/3;' 0.333333333333333$ --- I have no idea what you want me to do about _controlfp function. Also, your briefcase folder seems to be empty. Thanks. |
Excel VBA ".value" / Strange Behaviour!!!
Hi,
in vba on my pc, setting 24-bit(single) precision to FPU, 1 / 3 returns 0.333333343267441. so I'm not sure if it's the problem of FPU precision. anyway, if it is just problem of vba, some files or settings in your pc must be broken. but I don't know about relevant files and parameters. Also, your briefcase folder seems to be empty. sorry, my fault. if you mail me, I can send the file. -- HTH, okaizawa wrote: This Sub Test() Debug.Print 1 / 3 End Sub returns 0.333331383035028 --- I tried VBA in MSWord and the behaviour is the same as in Excel. --- I have cygwin(unix)-Perl in my pc and it returned. $ perl -e 'print 1/3;' 0.333333333333333$ --- I have no idea what you want me to do about _controlfp function. Also, your briefcase folder seems to be empty. Thanks. |
Excel VBA ".value" / Strange Behaviour!!!
This problem really drives me nuts... and I need a solution to this
badly. If there is anything you want me to do apart from formatting c:\, i will go ahead and do it. You could get my e-mail address from the postings. Thanks. |
Excel VBA ".value" / Strange Behaviour!!!
This problem previously, about 3 months earlier NEVER existed. Between
then and now, I haven't made any major software installation or changes in my system. |
Excel VBA ".value" / Strange Behaviour!!!
No, formatting c: is definitely the answer.
wrote in message ups.com... This problem previously, about 3 months earlier NEVER existed. Between then and now, I haven't made any major software installation or changes in my system. |
Excel VBA ".value" / Strange Behaviour!!!
Just so that everyone knows. This problem still remains to be solved.
I haven't taken the step of formatting c:\ yet, as I believe some of you out there has a smart solution. |
Excel VBA ".value" / Strange Behaviour!!!
Follow up from microsoft.public.excel.programming [[ Excel VBA
".value" / Strange Behaviour!!! ]]] wrote: Just so that everyone knows. This problem still remains to be solved. I haven't taken the step of formatting c:\ yet, as I believe some of you out there has a smart solution. Can't someone from Microsoft or an MCSE have an answer for this...? Please help! |
| All times are GMT +1. The time now is 05:23 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com