I welcome your thoughts on how to encapsulate my "On Error" statement and label for my word 2016 module that maintains an excel workbook.

I’ve rendered my daughter’s construction business client and vendor names as spreadsheets in an excel 2016 workbook. And I've customized her various waiver, invoice, notary documents and roster maintenance with word 2016 forms.

The only need to invoke the workbook is to initially load the session’s client and vendor rosters, insert a new member or update a member. I’ve created a module I named “modRoster” to address these needs.

I do not want to lock up the workbook for the entire session because only rarely will there be a need for anything but the initial roster load. And that occurs in the initial form’s activation.

Were it not for the opening, closing and error handling for the workbook, the encapsulation is straight forward. to load each sheet from the workbook into its own array (I opted not to use collections) that returns the row number inserted and All three, of course, require opening and closing the workbook and an On Error label given that the error event could happen in any of these functions. (Alas, I have proven that repeatedly.)



Of the options I’ve considered: (1) Encapsulate an additional two functions / subs: that returns the workbook object and and call them as the first and last statements in the other functions. But, then, where do I put the On Error statement and label without creating a pathological connection?

Or (2) Create an umbrella function that invokes the other functions via a Select Case statement between the opening and close of the workbook. That simplifies where to locate the On Error but requires passing all three parameters to the umbrella function which smells bad to me.

These are the options I’ve considered. And I’m open to an entirely different approach.