Excel VBA ".value" / Strange Behaviour!!!

[ifiaz]

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?

 

[Robin Hammond]

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

 

[Bill Martin]

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

 

[ifiaz]

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

 

[NickHK]

ifiaz,
Can't reproduce it in Excel 2000.

Have you tried the Detect and Repair ?

NickHK

 

[fiazidris]

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.

 

[fiazidris]

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.

 

[fiazidris]

Is this problem unsolvable?

Is there no one who can help me out?

 

[okaizawa]

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.aspx?scid=kb;en-us;280504

 

[fiazidris]

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.

 

[okaizawa]

Hi fiazidris,

Thank you for your reporting.
at a guess, it might be a problem of OS or hardware settings.
but I can't think of the cause.
Do other programs calculate correctly?

What does this do?

Sub Test()
Debug.Print 1 / 3
End Sub


the following DLL(in fp.zip) can call the _controlfp function. if you
want to confirm the current FPU precision, try this.

my briefcase
http://briefcase.yahoo.com/bc/okaizawa2/lst?.dir=/a

_control87, _controlfp
http://msdn.microsoft.com/library/en-us/vclib/html/_crt__control87.2c_._controlfp.asp

 

[fiazidris]

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.

 

[Jezebel]

Do you have a PC with the Intel floating point bug in the chip?

My Excel VBA gives 0.333333333333333 as expected.

 

[okaizawa]

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.

 

[fiazidris]

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.

 

[fiazidris]

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.

 

[Jezebel]

No, formatting c: is definitely the answer.

 

[fiazidris]

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.

 

[fiazidris]

Follow up from microsoft.public.excel.programming [[ 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.

Can't someone from Microsoft or an MCSE have an answer for this...?

Please help!