Handling error in VBA

[ChrisGreaves]

"If you design something to be idiot proof, the universe will design a better idiot."

Hi Hans. I am not sure how this fits into the argument.
If Excel is poorly designed in not having a test for a sheet's existence, how does forcing thousands of Excel users into mastering On Error make an improvement?
To my mind such an outcome only makes Excel's designers appear worse, that is, unprofessional.
Cheers, Chris

[HansV]

[StuartR]

You do need to have error handling in any module. The example given earlier of checking that a file exists before trying to open it is a classic example, because it is possible for the file to be deleted in the split second between checking and opening.

[YasserKhalil]

The problem is not related to the file if exists or not. This is not the reason of the error. The code loops through excel files and I deal with them using ADODB Recordset and the error is because some of the files are not the same structure.

[ChrisGreaves]

The problem is not related to the file if exists or not. This is not the reason of the error. The code loops through excel files and I deal with them using ADODB Recordset and the error is because some of the files are not the same structure.

Aha! Thanks Yasser, So this is a design problem rather than a VBA coding problem.

How do you plan to fix the design (of the database structure)?
And how will that affect other programs that make use of (load, read data) the database?

And most important: How much time do you think the fix will take?
Cheers, Chris

[ChrisGreaves]

You do need to have error handling in any module. The example given earlier of checking that a file exists before trying to open it is a classic example, because it is possible for the file to be deleted in the split second between checking and opening.

Agreed. Excellent example.
Operating systems make (at least back in the day made ...) use of a single instruction (cycle) that can test a bit, set the bit, and then branch conditionally on the original setting. This was part of a "Supervisor" program, or sometimes an "Executive". Such instructions were not available to the lowly application programmer writing in PLAN, ASSEMBLER, COMPASS and so on.

The specific case of a file being deleted (when maybe it shouldn't) during an application run is a good example of a need for some means of testing if a user has done something they shouldn't. If a user like me deletes a file while waiting for an application to complete on my laptop, my knuckles should be rapped. Such an error-monitoring chunk of code should issue a display message, start ringing a bell, and send an electric current through the seat of my chair. The absence of a critical file is a serious matter, and I suspect that in all cases this would be a reason for the application to cease gracefully and wait for the human-error to be corrected.

Once the file is (held) open by the application, the operating system should inhibit the deletion of the opened file. This a good argument for system design that dictates "Open all required files at once, so that they are held for the application program"

I think that an application program should be told only that a critical error has occurred, and a log file read to see what was going on. A human error requires a human intervention. Especially in a large corporation. When the fortnightly payroll application finds that the time sheet data file has been deleted, there is little that the application programmer can do to program around that event.

But in terms of testing for data format, type, and boundary conditions ("No data in the input file" etc.), all of that should be perceived at the design stage. Only external events (user deleting a file) should be trapped by On Error, and the management of that event ought not to be the problem of the application programmer.

I see that Yasser has volunteered that the database design is flaky. Treat the problem at the source; otherwise the application program becomes a Bramble Bush of traps.

IMNSHO

Cheers, Chris

[Jay Freedman]

If VBA was a truly object-oriented language instead of an unholy alliance of MS Basic and sort-of-objects, there would not be an On Error statement at all. Instead, as in C++, Java, and (!) PowerShell, the try-catch structure would be the standard for just about everything.

"Never ask what could go wrong -- you may find out."

[ChrisGreaves]

If VBA was a truly object-oriented language instead of an unholy alliance of MS Basic and sort-of-objects, there would not be an On Error statement at all. Instead, as in C++, Java, and (!) PowerShell, the try-catch structure would be the standard for just about everything.

I can live with that.
Thanks Jay.
Chris

[DocAElstein]

Hello
I think perhaps we are all here not really far off a similar opinion on these things.
If…. I consider a scale of 0% to 100% , where
_ 0% is where you would use error handling just like you would any formula or function in Excel or VBA
, and at the other extreme
_ 100% is somewhere near Chris thinking close to everything can be done without it.
, Then….
I think a lot of people would be like Hans at 90% - using it mostly when he does not know of a way to do something without it, ( because there most likely isn’t), or when it is a nice efficient way to do something. I might be at 95% but only because I often don’t need efficiency and the extra coding to do something I find interesting and pretty. ( I might go up more towards 100% depending on what Chris tells or shows in the next year or two…. )
You will have generally a lot of people close to 0% I think. They simply just don’t know yet, as they are new to VBA and or computing generally, or they just don’t care too much one way or the other. Occasionally they may be very stupid, or pretending to be: - like the guy that puts On Error Resume Next at the top of his macro then posts… I just need a very tiny bit of help. My code is perfect, no errors at all, it just isn’t doing what I want …
I don’t think many people are in the middle. So really we all in the thread are pretty well close to the same opinion I think.
I expect if my (ex) Mother–In-law visited for a longer time then expected, I could get her up to 85% after a bit of careful explanation of the subject.
_._______

If VBA was a truly object-oriented language instead of an unholy alliance of MS Basic and sort-of-objects, there would not be an On Error statement at all. Instead, as in C++, Java, and (!) PowerShell, the try-catch structure would be the standard for just about everything.

I can live with that.

Yes I too can live very happily with that, and it was pleasing to read. Without the wider experience in computing or others, such as, Jay, I have had similar thoughts based only on gut feeling, but was not sure, so a more qualified confirmation is helpful, thanks.
We disused this non perfect object-oriented language in VBA before . I thought maybe it was a bit Anarchistic at the time, not adhering to the Hierarchy ideas which are often presented at an early stage of learning object-oriented- programming, (OOP).

Perhaps the transgression from the norm is not always a bad thing, if perhaps it is done in a not too reckless way. Such transgression form the socially accepts norm can sometimes help to give very useful and novel solutions, which no one ever thought of before. At least that has frequently been my experience.
In the case of Runtime Error handling in VBA, the transgression has possibly resulted in a lot of extra stuff available compared to the standard try-catch structure.

The large amount of extra stuff, is the root of a lot of problems and mis understandings, IMO.
A big problem as I see it with Runtime Error handling in VBA is that all the extra things we have , which could be very useful, simply are not understood by almost anyone fully. The subject is certainly not at all difficult. I am the living evidence for that. If I can understand it fully then anyone can. But the number of permutations of what we can do with the extra available stuff is just so high that I think for most people it does not justify them sitting down and taking the time needed to learn it fully. –
Consider a professional painter, Fred. He buys a tub of paint in a new very unconventional mixture. It gives a few thousand extra possibilities including some extremely nice textures that some people would pay a fortune to have. It requires a good understanding of exactly how to mix and stir it, both to get all these thousands of extra possibilities, but also, to prevent occasionally some new problem occurring that totally messes everything up. The Painter, Fred, really has not got, and probably never will have, the time to go to college for half a year and learn all about it. But he takes a quick crash course from the seller, uses the paint, takes some advantages of the extra stuff, but misses most of them. In fact the new problem cropped up once or twice, once very badly, so from that day on he never uses the stuff anymore and warns others to avoid it..
Back in the real world – What I have seen time and time again: I saw it again just recently: A very respected VBA expert , currently regarded as near the top of the best Tutorial makers. He has the most detailed , usually complete guides to subjects in VBA, and is almost as verbose as me when he write Blogs or tutorials! He personally told me he was putting off doing a Tutorial on Runtime Error handling in VBA until he really understood it fully. A few months ago he finally did such a Tutorial. He has missed out about two thirds of the subject, badly confused two distinctly different things, and got a few things blatantly wrong. A few thousand programmers will have a similar experience to Fred now, and then go on to warn others of the bad things in Runtime Error handling in VBA, because of their ignorance and stupidity in the subject. But I am not criticising, and definitely not intending to insult anyone. These people are all far smarter than me and know tons more about VBA then me. I just prefer to understand fully the little I do know, well at least in computing software anyway, - I always thought it should be an exact subject. That’s all, :-)
_._____

it should not be labelled "On Error" at all, but rather "On an error that could not be anticipated ahead of time"

I agree generally that the terminology is bad generally. That adds to the confusion, and makes it require even more time to learn it all, because it’s easy to get knocked off course. There may be some secret logic to the terminology that is proprietary / confidential, but to the end user the words used are both confusing and in some cases misleading.

... on our roads ... there are no accidents, only collisions.

Exactly, future car “accidents” will be caused by software glitches



Alan