Design question: encapsulating excel workbook functions in word 2016 vba project

grNadpa · #1 05-07-2022, 11:18 AM

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.

Code:

Function LoadRosters() as Boolean

to load each sheet from the workbook into its own array (I opted not to use collections)

Code:

Function AddRosterRow(pSheetName as String, pRowContent as Variant) as Long

that returns the row number inserted and

Code:

Function UpdateRosterRow(pSheetName as String, pRowContent as Variant, pRowNumber as Long) as Boolean

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:

Code:

Function OpenRosterWorkbook(pPathName as string) as Variant

that returns the workbook object and

Code:

Sub ReleaseRosterWorkbook(pRosterObject)

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.

grNadpa · #2 05-08-2022, 06:49 AM

Here's what I did. But please suggest any alternatives.
I went with option (1). I avoided the pathological connections by coding an "On Error" imperative in each function / sub method to GoTo a label within that method which, among other things, contains a "On Error Resume Next" thus maintaining my encapsulation.

macropod · #3 05-08-2022, 04:29 PM

Simply giving us the first lines of a series of Functions with no actual code, and no Word documents or Excel workbooks from which we might understand their structure and what you're doing, gives precious little for anyone to work with.

In any event, you should avoid using 'On Error' wherever possible, as doing so might be hiding errors you shouldn't be ignoring.

grNadpa · #4 05-09-2022, 06:44 AM

Quote:

Originally Posted by macropod

In any event, you should avoid using 'On Error' wherever possible, as doing so might be hiding errors you shouldn't be ignoring.

In lieu of "On Error", I'd welcome your alternative should the "Set" of the workbook below fail.

Code:

Function InstantiateWorkbook() As Boolean
'Purpose:   Instantiate session's Excel Workbook
'Requires:  RosterPath (Windows Explorer path to workbook)
'Returns:   exwb (Excel Workbook)
'           objExcel (Excel.Application)
'Note:      RosterPath, exwb and objExcel defined in Declarations
    On Error GoTo InstantiateError
    
    Set exwb = objExcel.Workbooks.Open(RosterPath)
    
    On Error Resume Next
    InstantiateWorkbook = True
    Exit Function
    
InstantiateError:
    MsgBox "Error initializing Roster Spreadsheet." & vbCrLf & _
        "Have programmer confirm that" & vbCrLf & _
        "(1) MicrosoftExcel 16.0 Object Library" & vbCrLf & _
        vbTab & "checked in Project References" & vbCrLf & _
        "(2) " & RosterPath & vbCrLf & _
            "folder is present", , "Activation Error"

    UnlockSpreadsheet
    InstantiateWorkbook = False
    Exit Function

Code:

Sub UnlockSpreadsheet()
    On Error Resume Next
    exwb.Close
    Set exwb = Nothing
End Sub

macropod · #5 05-09-2022, 08:41 AM

See, for example, the Excel instantiation code in: https://www.msofficeforums.com/45624-post5.html

grNadpa · #6 05-10-2022, 06:26 AM

Quite interesting. Thank you. The checks to test if Excel is already running I find particularly helpful.

As far as the On Error issues, it appears the solution is not to eliminate the statements but to limit use to "resume next" and disable through "GoTo 0" before exiting the sub.

Thank you for your valuable responses

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Another Unique Question for managing a project plan in Excel	IamThatGuy	Excel	5	02-09-2022 02:10 PM
Excel 2016: A query from same workbook	ArviLaanemets	Excel	0	01-29-2018 07:22 AM
Macro Personal Workbook does not load in Excel 2016	Intruder	Excel Programming	0	01-21-2017 05:44 AM
Export Project in Excel Workbook	codeghi	Project	3	09-28-2016 11:32 AM
Newbie VBA question re Excel 2016	Highlander	Excel Programming	3	01-22-2016 08:02 PM