|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Design question: encapsulating excel workbook functions in word 2016 vba project
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 Code:
Function AddRosterRow(pSheetName as String, pRowContent as Variant) as Long Code:
Function UpdateRosterRow(pSheetName as String, pRowContent as Variant, pRowNumber as Long) as Boolean Of the options I’ve considered: (1) Encapsulate an additional two functions / subs: Code:
Function OpenRosterWorkbook(pPathName as string) as Variant Code:
Sub ReleaseRosterWorkbook(pRosterObject) 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. |
#2
|
|||
|
|||
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. |
#3
|
||||
|
||||
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.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#4
|
|||
|
|||
Quote:
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 |
#5
|
||||
|
||||
See, for example, the Excel instantiation code in: https://www.msofficeforums.com/45624-post5.html
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#6
|
|||
|
|||
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 |
Tags |
excel 2016, vba code, word 2016 |
|
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 |