Percentiles from two different macro

[billj]

Hello everyone,

Do you know why the two macros below produce different results? The 2nd one seems to be in line with normal Excel formula. But the 1st does not.

The sample file is attached here. Thank you.

Please Login or Register  to view this content.

[zbor]

That's because CDBL od DIV/0 in row 8 return you 2007

Please Login or Register  to view this content.

Actually, if you remove all text then that value drop to 0

[billj]

This is really odd. Why an error value is converted to Cdbl?

[MrShorty]

Here's what I see happening, and I will start with a disclaimer that I am not really well versed in some of the programming issues.

As I usually do with something like this, the first thing I do is start adding Stop statements so I can enter debug mode and try to see what is going on. If you are unfamiliar with VBA's debugging tools, this could be a good time to learn. The final version I ended on looks like this.

Please Login or Register  to view this content.

The most interesting part was the part about the type mismatch error. The Iserror(c.value) returns true, the c.value+0 is a type mismatch error, so I started to wonder how I even ended up in the middle of this block If. It should have crashed. That's when I looked farther up and noticed the "on error resume next" statement in effect, telling VBA, "If you encounter any kind of run-time error like a type mismatch, just ignore it and move on to the next statement." So VBA is merrily looping through the list, and it comes to the error 2007 div/0 error. When it tries to add 0 to this error, it can't, but it has been told to ignore errors and keep going, so it moves to the next statement inside of the block if and keeps going. It sees the 2007 in the error number and converts that to double and stores the 2007 in the array.

I do not know why you included the on error resume next statement. It sometimes seems that we frequently include this in our procedures almost reflexively without actually thinking through the actual error trapping and handling we want to do. I would suggest that you go back through your development process. Reconsider why you included the on error resume next statement - what errors are you expecting and trying to trap and what you want to do with each possible error. Then revise your error handler to something more appropriate to the actual errors you end up seeing.

PS on edit: the site's firewall is being really stubborn about anything that looks like html today. I will see if I can get it to let me post the code and such. Hopefully there is enough in the plain text that it finally allowed me to post for you to understand what is going on.
PPS on edit: I think I finally got a final version that I like and the the firewall allowed (seemed to be related to my use of "onerror" misspelling of the VBA statement).

[billj]

Hi MrShorty. Your post is such a useful guidance to help me find the errors. UDFs are particularly tricky to debug for errors.

I will keep your code and learn from it. A big thank you to you