User Defined Formula Function causes Excel to Freeze and Crash

Hi all.
I wrote the following UDF to return a string address for a discontinuous range of cells selected according to multiple criteria. It appears to work as intended when I tested it.

But…

If the UDF is in place, EVEN IF IT IS NOT BEING USED excel will become unresponsive each time I try to save, and then crash. Also filling in new cells with any values can trigger excel to freeze up. This is sub-optimal.

I’m not getting any error messages, and the same issue occurs on other computers. Comment out the Function, and there is no problem.

Can anyone point out what I am doing wrong? Any help would be GREATLY appreciated.

Please Login or Register  to view this content.

You have the On Error statemenst that loops.
So go back to the drawing board and review your on error statements and don't forget to clear them

The one really killing you is this one

Please Login or Register  to view this content.

The error is still there so after the Next i it goes back to the On Error statement

Originally Posted by Keebellah

You have the On Error statemenst that loops.
So go back to the drawing board and review your on error statements and don't forget to clear them

Yes. The idea is that if an error occurs for any cell being compared,
the loop will skip to the end,
clear the error,
and reinstate error handling for the next iteration of the loop

Originally Posted by Keebellah

The one really killing you is this one

Please Login or Register  to view this content.

The error is still there so after the Next i it goes back to the On Error statement

I don't understand. I thought that

Please Login or Register  to view this content.

clears the error. Why would the error still be there?

Clear error

Please Login or Register  to view this content.

To end error trapping

Please Login or Register  to view this content.

Your trap takes you back one line of code which is the Next
So it stays in that loop because the first line after the Next throws an error

When coding disable the On Error ... command until you‘ve tested all the code then enable it

Thanks Keebellah,

I had been avoiding "Err.Clear" because of this description in 'Excel Macro Mastery, VBA Error Handling – A Complete Guide'
"Note 1: Err.Clear resets the text and numbers in the error object but it does not clear the error – see On Error GoTo -1 for more information about clearing the actual error."

I guess I had misunderstood something.

Your trap takes you back one line of code which is the Next

Wait, could you please confirm that. You are saying that

Please Login or Register  to view this content.

jumps back one line? I've never seen that in any description of the functions, nor observed it. I would have expected that to wreck a lot of other code I've written.

Eitherway, swapping the for Err.clear HAS solved my problem, but left me feeling that I must have been misinformed on a LOT of stuff I've been reading.

When coding disable the On Error ... command until you‘ve tested all the code then enable it

I do exactly this. The code did exactly what wanted. I then added similar error handling that I've used a bunch of times before... And everything went to hell and couldn't understand why.

When testing code it is good practice to add a bread moment and then from that point on step though each line of code where you think something might trigger an error and observe.

VBA is 100% inspiration and perspiration with some very important ingredients: time, patience and creativity