Solved: Excel VBA comparison wierdness / challenge

[Apps]

Hi all,

Had Excel VBA do something yesterday that I have never seen before that is niggling me as to WHY it was happening and so wondered if anyone has seen the same sort of thing ...

(using Excel 2003)
In my job I have an excel template that users key percentages into certain cells (as well as other info).
They submit this info and it is saved to an SQL server as the excel decimal value (e.g. 20%=0.2).

They can then import the information back from the server into the same cells as before.
(0.2 is returned, Excel shows as 20%, happy days).

There are 4 cells that must add up to 100% and these are validated by VBA on the submit to the server:

(wsCost is the worksheet)


If wsCost.Cells(35, 3).Value + wsCost.Cells(35, 6).Value + wsCost.Cells(35, 9).Value + wsCost.Cells(35, 12).Value <> 1 Then
V_ValidCostMsg = "Mix % <> 100%"
End If


For this example the cell values are 0.35, 0.57, 0.08, 0 respectively.
(but the wierdness happens for any and all values, whether imported from the server or keyed directly onto the worksheet!)

The issue is that when it adds the cell values together and compares them to the <>1 then it returns True?

I have so far checked :
worksheet cell formats = percentage
sql table format = float
sql table data = 0.35, 0.57, 0.08, 0
isnumeric(wsCost.Cells(35, 3).Value)=true for all cells

So I then passed the sum into another variable ;

xxxxx=wsCost.Cells(35, 3).Value + wsCost.Cells(35, 6).Value + wsCost.Cells(35, 9).Value + wsCost.Cells(35, 12).Value

; and then checked that with isnumeric(xxxxx)=true (and has a value of 1)

But then if I use;

If xxxxx<>1 Then V_ValidCostMsg = "Mix % <> 100%"
- this still returns True!

The same thing returns True with xxxxx declared as a Double variable;

Dim xxxxx as Double
xxxxx=wsCost.Cells(35, 3).Value + wsCost.Cells(35, 6).Value + wsCost.Cells(35, 9).Value + wsCost.Cells(35, 12).Value


All through if I hover over the variable I see it as the value of 1 (not "1") but the comparison just doesn't want to work?

I have also done all of the above using wsCost.Range("##").value instead of wsCost.Cells(#,#).Value and it still does the same thing.

In the end I compared the addition result to a string instead and it worked! So I have had to pass the sum of the cell values into a string and compare it against a string value of "1" - which works - but it is obviously annoying me that I have had to do this workaround in the first place!


xxTemp$ = (wsCost.Cells(35, 3).Value + wsCost.Cells(35, 6).Value + wsCost.Cells(35, 9).Value + wsCost.Cells(35, 12).Value)
If xxTemp$ <> "1" Then
V_ValidCostMsg = "Mix % <> 100%"
End If


And just to top the annoyance all off this is only happening with one template file of many exact same template files and the others all work fine with the original code in! :dunno

Anyone else seen this/similar experiences/ideas?

[Jan Karel Pieterse]

This is a well known computer problem. COmputers represent numbers in the decimal system as binary numbers, so a conversion must take place to enable the computer to do math with those numbers. Decimal numbers with a floating point can often not be exactly represented by a binairy number; we get a (small) conversion error. After doing the math (binairy), another conversion has to take place to decimal again, which may cause yet another rounding error.
So if you're comparing the sum of a number of added floating point numbers, the total the computer calculates may differ slightly from what you'd expect.
The trick is to test whether you're close enough:
If ABS(1-TheSumGoesHere)<0.00001 Then
'Let's assume we have 100 percent
Else
'Hmm, maybe something wrong here
End If

[Apps]

Hi Jan,

Thanks for that. Although I still no idea why the other templates were/are happy with the original code and worked fine?
Surely this would be the case for all of the templates running the same code with no exceptions?

[Jan Karel Pieterse]

Not if the numbers are different. Whether or not you end up with exactly 1 depends on the combination of numbers you ask it to add.

[Apps]

Not if the numbers are different. Whether or not you end up with exactly 1 depends on the combination of numbers you ask it to add.


Hi Jan,

Excellent, many thanks for the valuable insight! :thumb