Interfacing between workbooks (Exporting data Table and key properties)

Question

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

Answer 1

Just looking at your function there 2 ways to improve it:

Public Function GetDataArrayFromSheetByCodename(ByVal strCodeName As String) As Variant
  Dim ws As Worksheet
  For Each ws In ThisWorkbook.Worksheets
    If ws.CodeName = strCodeName Then
      Set GetDataArrayFromSheetByCodename = GetTableRange(ws)
      Exit Function
    End If
  Next ws
  GetDataArrayFromSheetByCodename = CVErr(2042) '/ #N/A error
End Function

When having LOTS of sheets, this may be faster, but uses an error:

Public Function GetDataArrayFromSheetByCodename(ByVal strCodeName As String) As Variant
  On Error Resume Next
  If Len(ThisWorkbook.VBProject.VBComponents(strCodeName).Name) = 0 Then
    GetDataArrayFromSheetByCodename = CVErr(2042)
  Else
    Set GetDataArrayFromSheetByCodename = GetTableRange(ThisWorkbook.VBProject.VBComponents(strCodeName))
  End If
End Function

Also as a quick-inprove just add Exit For after your wsWasFound = True to not loop all sheets that are left after you found a match.

For the second version of the function: I use the fact that errors inside of conditions inside of If-statements are always true when using On Error Resume Next. Len(....Name) = 0 Then normally NEVER can be true when there is a need of a name. But having an error inside will act like the whole statement is true. So it simply tries to catch the item. If it exists, it also has a name this way Len will be >0 and be false (now your calculation will be done), else (it doesn't exist) it will act like If is true and the = CVErr(2042) will be set.
For the Subs: they doesn't make sense to me. What do they do? As it looks to me, they do simply nothing.

Last hint: You may change the function to As Range. When set a variable to it, you later could simply check by If Variable Is Nothing then. So there would be no need for = CVErr(2042).

EDIT
just to show the way i would hand over the values (while im pretty sure you will not use it)

Public Function GetDataTableHeaders() As Variant
GetDataTableHeaders = Array("Client Name", "Client Name", "Income", "Frequency", _
  "Date Paid", "Base Month", "Wrapper", "Ascentric Acc #", "Account to pay from?")
End Function

then simply set a variable to it or directly use GetDataTableHeaders(2) to get "Income"... as it looks, you may want to output the values to a sheet as headers and this way it would be much easier. However, you want to make it "not understandable" in some way, and for that it will be better to stay as it is right now :)