UDF not stepping in the direction I'd expect

[PTP86]

Hi

I've built a UDF that gets called on about 100 times for each of the 200 customers that I run through the model on various dates. It works 99% of the time but doesn't for the other few. On the instances where it doesn't work, it is working for some of that customer's dates but not others.

So I've ran one of them and done a step through and seen that it's taking a step that puzzles me:

Option Explicit

Public Function Performance(Actuals as Range, Targets as Range, Grade as Range) as variant

Dim NumberofAttempts as Byte
Dim OverallGradeAPosition as Double
Dim i as byte

NumberofAttempts = 0
For i = 1 to Actuals.cells.count[INDENT]If Actuals.cells(i) <> "-" then[/INDENT]
[INDENT=2]NumberofAttempts = NumberofAttempts + 1
[/INDENT]
[INDENT]End if
[/INDENT]
Next i

OverallGradeAPosition = 0
For i = 1 to NumberofAttempts[INDENT]If Grade.cells(i) = "A" then[/INDENT]
[INDENT=2]OverallGradeAPosition = OverallGradeAPosition + Actuals.cells(i) - Targets.cells(i)[/INDENT]
[INDENT]End if[/INDENT]
Next i
OverallGradeAPosition = Round(OverallGradeAPosition,15)        'This line is here to get around the annoying thing where Excel/VBA comes up with a crazy tiny number instead of 0


Select Case OverallGradeAPosition[INDENT]Case <= 0
[/INDENT]
[INDENT=2]Do .....ABC.......
[/INDENT]
[INDENT]Case Else[/INDENT]
[INDENT=2]Do .....XYZ.......

[/INDENT]
End Select


Performance = .........

End Function

On some occasions it is entering the XYZ procedure when I would expect it to enter the ABC procedure.
For one of those cases, when I do a step through and hover the mouse over OverallGradeAPosition when it is about to do select case, it shows 0. Similarly if I put ?OverallGradeAPosition in the immediates window it says 0.

So why then does it enter the XYZ procedure?


Thanks

 

[Joe4]

This calculation stands out to me:

OverallGradeAPosition = Round(OverallGradeAPosition,15)

Do you really need to track out to 15 decimal places?
Note that Excel can only subtract 15 significant digits, so if there are any numbers (other than zero) on the left side of the decimal, it is not going to be able to track that many.

Also note that the famous floating arithmetic error in Excel can cause unexpected results.
See: https://www.microsoft.com/en-us/mic...y-does-excel-give-me-seemingly-wrong-answers/

 

[PTP86]

Obviously I am aware of the infamous floating arithmetic error. That is the whole reason I have done Round(OverallGradeAPosition,15) as per my original post.
And according to the immediates window this has successfully changed it from 0.000000000000000001 or whatever to 0, but then not reflecting that in where it flows next

 

[Joe4]

Typically, people do not round all the way out to 15 spaces. And in doing so, you may not eliminate the floating arithmetic error, as the small amount could be out in the 15th digit.

What happens if you try rounding to just 2 or 3 places?