I’m guilty and I’d like to come clean. I’m probably guilty of a lot of things, but I only wish to confess two transgressions. I’ve been working with some code lately, some mine, some not, and it made me appreciate proper coding techniques.
I’m generally a believer in the one-exit-point rule. But I admit I don’t follow it religiously. One-exit-point means that every procedure has only one way out. In VBA, there are generally two ways to get out of a procedure, End Sub and Exit Sub. There’s also End, but I’ve never had cause to use it and I never will. There is one particular code construct I like that violates this rule.
If SomeCondition Then Exit Sub
If SomeOtherCondition Then Exit Sub
If SomeThirdCondition Then Exit Sub
I like this construct because it puts all my failure modes at the top of the sub. They’re easy to see and easy to edit. Recently, I had to run another procedure at the end of this one, regardless of whether it failed. Without those Exit Subs, I could have called the procedure right before the End Sub and been done with it. Instead, I had to code it like this.
If SomeCondition Then
If SomeOtherCondition Then
If SomeThirdCondition Then
I can’t say I’ve run into this a lot, but it really drove home the value of one exit point. Had I been more rigorous, I would have coded it like this
On Error GoTo ErrorHandler
If SomeCondition Then Err.Raise glERRQUIET
If SomeOtherCondition Then Err.Raise glERRQUIET
If SomeThirdCondition Then Err.Raise glERRQUIET
If Err.Number = glERRQUIET Then
Now I have one exit point; the Exit Sub in the ErrorExit label. Any time I want to exit a sub quietly, I can raise an error with my global quiet error constant. I still get all my failure conditions at the top and nicely grouped. When I need to incorporate LoggingProc, I only have to call it once.
My next sin has to do with entry point procedures. Entry point procedures are procedures that the user can cause to run, as opposed to procedures that are called form other procedures. In the context of an application, that means they can be called via a control placed on a spreadsheet; a menu, toolbar, or ribbon control; or by firing an event (like the Worksheet_Change event). In the vast majority of my projects, I have only three places that I have to look for entry point procedures, and it’s usually only two.
- The MEntryPoints module
- The CAppEvents custom class module
- The ThisWorkbook and sheet class modules
I realize that the third one isn’t one place, but you get my meaning. If my code is an add-in, all of the event code is in CAppEvents and all of the non-event entry points are in MEntryPoints. Except when I don’t apply the level of rigor that I should. In some cases, I get lazy and put an entry point procedure in a different standard module.
What’s the harm? Here’s an example: Due to some changes, I now need to verify that a particular workbook is open before my code can run. I have to identify every way the code can start (entry points) and check for the existence of the workbook. When I know where they all are, it’s relatively easy. When I don’t, it’s not.
Those are my sins. I shall endeavor to improve. Where do you cheat?