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