UDF not stepping in the direction I'd expect

PTP86

Board Regular
Joined
Nov 7, 2009
Messages
86
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:


Code:
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
 
Last edited:

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
This calculation stands out to me:
Code:
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/
 
Upvote 0
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
 
Last edited:
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top