VBA identical nrs not matching in if statement

Question

I apologise, I realise this may have been asked a gazillion times already but I've tried looking and I can't find a situation like mine. I'm still learning, and I've never posted here before either so please be gentle with me.

I am working in Excel VBA. I have two cells (PICKAmt and INVAamt, same sheet) and they each have the exact same number in them, e.g. 399.80.

Even after formatting the cell to 0.00 VBA still reads them both as "399.8", but also still says they are not the same
They are both "Doubles"
This same calculation worked under the exact same circumstances on a different sheet

My code says:

Private Function INVACalc(ByVal INVAamt As Double, Holdcell As String, lastcell1 As String) As Double   
Dim PICKAmt As Double  
Dim APSDamt As Double  
Dim lastcell3 As String  
Dim SUND As String  
Dim APIE As String

Worksheets("GL").Activate  
Range(lastcell1).Offset(0, 9).NumberFormat = "0.00"  
Range(lastcell1).Offset(0, 9).Value = Application.WorksheetFunction.Sum(Range("AL:AL"))         'add up total of all lines  
PICKAmt = Range(lastcell1).Offset(0, 9).Value  
If PICKAmt = INVAamt Then    
   'do stuff  
Else  
  'do something else  
End if

It goes straight to the "Else", as if the two amounts are not identical, which they are. They have no extra spaces or characters. The only difference I can pick up between them is that the INVAAmt cell is formatted as "Custom" and the PICKAmt cell is formatted as "Number", but again, this same code does the job just fine on other sheets with the same formatting. I am at a loss.

Do subtract one number from another to see the difference. Dont rely on formatted value. If there is a difference, it will go to `Else`. — shahkalpesh, Jul 21 '21 at 17:34
Do you mean `debug.print(PICKAmt - INVAamt)` or `debug.print(INVAamt - PICKAmt)` returns 0? — shahkalpesh, Jul 21 '21 at 17:43
Well this is interesting... last night I did the subtraction in the sheet itself, and not the code, it came to 0. This morning when I saw shahkalpeshp's comment I added the line to the code and ran it again, expecting it to be pure 0. What I got was this: -1.13686837721616E-13.... I was not expecting that — Ursula, Jul 22 '21 at 06:33
Either `If Format(PICKAmt,"0.00") = Format(INVAamt,"0.00") then` Or `If Abs(PICKAmt-INVAAmt) < 0.001` then — CDP1802, Jul 22 '21 at 08:03
@CDP1802 - the first option worked. I am new enough to not understand WHY it worked, so feel free to explain it to me. Thank you :) — Ursula, Jul 23 '21 at 14:17

score 0 · Answer 1 · answered Jul 23 '21 at 15:59

The problem is the way decimals are represent in binary as explained here. The 'same' decimal can have more than one floating point representations, for example

Sub explain()
   Dim n As Double, m As Double, k As Double
   n = 0.6
   m = 0.3 + 0.3
   k = 0.1 + 0.1 + 0.1 + 0.1 + 0.1 + 0.1
   Debug.Print Format(m - n, "0.000000000000000000")
   Debug.Print Format(k - n, "0.000000000000000000")   
End Sub

First result is 0.000000000000000000 the second 0.000000000000000111. One solution is to format the doubles as strings and compare the strings.

If Format(PICKAmt,"0.00") = Format(INVAamt,"0.00") Then

VBA identical nrs not matching in if statement

1 Answers1