Solved: Excel VBA comparison wierdness / challenge
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)
[vba]
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
[/vba]
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 ;
[vba]
xxxxx=wsCost.Cells(35, 3).Value + wsCost.Cells(35, 6).Value + wsCost.Cells(35, 9).Value + wsCost.Cells(35, 12).Value
[/vba]
; and then checked that with isnumeric(xxxxx)=true (and has a value of 1)
But then if I use;
[vba]
If xxxxx<>1 Then V_ValidCostMsg = "Mix % <> 100%"
[/vba] - this still returns True!
The same thing returns True with xxxxx declared as a Double variable;
[vba]
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
[/vba]
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!
[vba]
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
[/vba]
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?
