So I recently rebuilt a worksheet we have for tracking our clients who take income from their portfolios (here). I intend to be pulling this data into other workbooks for various reporting functions.
To facilitate this, I decided to write methods in the "workbook" module to return:
- The codenames of the sheets holding data tables
- The headers used in the data tables
- The data table from a specific sheet (specified by codename)
Is this an effective way of achieving my goal (reducing potential problems interfacing between workbooks)?
Option Explicit Public Const TOP_LEFT_CELL_STRING As String = "Client Name" Public Const CLIENT_NAME_HEADER As String = "Client Name" Public Const INCOME_AMOUNT_HEADER As String = "Income" Public Const PAYMENT_FREQUENCY_HEADER As String = "Frequency" Public Const PAYMENT_DAY_HEADER As String = "Date Paid" Public Const BASE_MONTH_HEADER As String = "Base Month" Public Const ASCENTRIC_WRAPPER_HEADER As String = "Wrapper" Public Const ASCENTRIC_ACCOUNT_NUMBER_HEADER As String = "Ascentric Acc #" Public Const ACCOUNT_TO_PAY_FROM_HEADER As String = "Account to pay from?" Public Const WS_2015_CODENAME As String = "ws_2015" Public Const WS_2016_CODENAME As String = "ws_2016"
Option Explicit Public Sub GetDataTableHeaders(Optional ByRef topLeftCellString As String, Optional ByRef clientNameHeader As String, Optional ByRef incomeAmountHeader As String _ , Optional ByRef paymentFrequencyHeader As String, Optional ByRef paymentDayHeader As String, Optional ByRef baseMonthHeader As String _ , Optional ByRef ascentricWrapperHeader As String, Optional ByRef ascentricAccountNumberHeader As String, Optional ByRef accountToPayFromHeader As String) topLeftCellString = CLIENT_NAME_HEADER clientNameHeader = CLIENT_NAME_HEADER incomeAmountHeader = INCOME_AMOUNT_HEADER paymentFrequencyHeader = PAYMENT_FREQUENCY_HEADER paymentDayHeader = PAYMENT_DAY_HEADER baseMonthHeader = BASE_MONTH_HEADER ascentricWrapperHeader = ASCENTRIC_WRAPPER_HEADER ascentricAccountNumberHeader = ASCENTRIC_ACCOUNT_NUMBER_HEADER accountToPayFromHeader = ACCOUNT_TO_PAY_FROM_HEADER End Sub Public Sub GetWorksheetCodenames(Optional ByRef ws2015 As String, Optional ByRef ws2016 As String) ws2015 = WS_2015_CODENAME ws2016 = WS_2016_CODENAME End Sub Public Function GetDataArrayFromSheetByCodename(ByVal strCodeName As String) As Variant '/ returns the datatable, or an error if could not find worksheet Dim dataTable As Variant dataTable = Array() Dim wsWasFound As Boolean Dim ws_target As Worksheet, ws As Worksheet wsWasFound = False For Each ws In ThisWorkbook.Worksheets If ws.codeName = strCodeName Then Set ws_target = ws wsWasFound = True End If Next ws If wsWasFound Then Dim tableRange As Range Set tableRange = GetTableRange(ws_target) dataTable = tableRange GetDataArrayFromSheetByCodename = dataTable Else GetDataArrayFromSheetByCodename = CVErr(2042) '/ #N/A error End If End Function