• 1

• 8.8k
• 2
• 31
• 69

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)?

Module "A1_Public_Variables"

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"

"Workbook" Module

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