using err.raise works first time but subsequently produces and automation error

[edwar368]

Hi All,
I have a generic function I use to validate if a value is within a drop down list. I recently decided to make it error within the function rather than passing it back to the calling proc, and I decided to do this by raising a custom error. This works as expected the first time it is invoked, but second and subsequent times produce an Automation error on the err.raise line. The complete sequence of events are:
1. user presses "Validate" button to start validation code
2. A user entered value fails the lookup and the expected error msgbox in the error handler is displayed
3. Usr clicks OK in msgbox and validation ends
4. User corrects the error (or not) and then again presses the validate button
5. Either the same value or another value fails the lookup function, but this time a "Run-time error '-2147220503 (800403e9)' Automation error is thrown
6. pressing debg takes me to the err.raise line
Full procedure shown below - Any advice welcome

Please Login or Register  to view this content.

[Norie]

Do you need to raise an error?

[edwar368]

Hi,
I think I know where you are going with this. I have toyed around with not raising an error and simply displaying the error message within the "If LookupFound = False Then" (And have also tested it and that works fine .... however the pureist in me is keen to find out why this is occuring, because a lot of online material makes reference to using the err.raise to keep things "neat" and also provide a single point of exit. So whilst this problem is not a showstopper for me, I am keen from a developer perspective to understand where I have gone wrong, as I was considering using this err.raise technique throughout numerous workbooks I maintain

[romperstomper]

Why are you using End on its own in real code?

I suspect that because of that, this line doesn't run:

Please Login or Register  to view this content.

(since your global variable has been reset) and you don't have any error handling in place.

[edwar368]

Hi,
I am using End on its own in this specifc function, because that is exactly what I want to happen. If this procedure fails, I want to present the user with a meaningfull enough message to identify where they have entered invaluid data (which is what the msgbox text does"), but then exit any further processing, thereby forcing the user to correct the error, before re-validating

[edwar368]

Thanks Rory, not sure if you edited your first response, but I originally only saw the question about "why using end", but I now think I know what you are getting at. Because I used "end" on its own, it has reset the global variables
Thanks you very much

[romperstomper]

Yes, sorry I did edit for clarification. Using End on its own is extremely dangerous. Given that you are using a function without a current return value, I'd return a Boolean indicating whether or not to continue, so that the calling code can check that and exit if need be.

[edwar368]

Thanks a lot for the very quick response

[romperstomper]

Glad to help.