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 tablesThe headers used in the data tablesThe data table from a specific sheet (specified by codename)Is this an effective way of achieving my goal (reducing potential problems interfacing between workbooks)?Module A1_Public_VariablesOption ExplicitPublic Const TOP_LEFT_CELL_STRING As String = Client NamePublic Const CLIENT_NAME_HEADER As String = Client NamePublic Const INCOME_AMOUNT_HEADER As String = IncomePublic Const PAYMENT_FREQUENCY_HEADER As String = FrequencyPublic Const PAYMENT_DAY_HEADER As String = Date PaidPublic Const BASE_MONTH_HEADER As String = Base MonthPublic Const ASCENTRIC_WRAPPER_HEADER As String = WrapperPublic 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_2015Public Const WS_2016_CODENAME As String = ws_2016Workbook ModuleOption ExplicitPublic 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_HEADEREnd SubPublic Sub GetWorksheetCodenames(Optional ByRef ws2015 As String, Optional ByRef ws2016 As String)    ws2015 = WS_2015_CODENAME    ws2016 = WS_2016_CODENAMEEnd SubPublic 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 IfEnd Function
Interfacing between workbooks (Exporting data Table and key properties)
vba;excel