Interfacing between workbooks (Exporting data Table and key properties)

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_FRE

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

StackExchange Code Review Q#113364, answer score: 2