Data table retrieval and indexing (+ interfacing between workbooks)

This project utilises the external-workbook-retrieval methods found here for the second workbook.

This is the beginning of a project. The code here covers:

Initial setup (define workbooks, unmerge any cells in data input)

Then, for the 3 worksheets across 2 workbooks: Find the table ranges (this workbook only), Create arrays of the same. Find the indexes of required columns within said arrays.

As always, all feedback is welcomed but especially:

How maintainable is this code?

Some screenshots of the table headers:

Ascentric:

Notes:

Client Income:

N.B.

The subs for retrieving Range/Arrays for the 2 same-workbook sheets (Ascentric Data / Notes Data) could be refactored into a standard "GetWorksheetData" function, but I've kept them separate in case I need to customise the subs for each sheet further down the line.

N.B.

The same is true of the 3 "Get Column Indexes" Functions. Virtually the same now but could change in the future.

Module B1_Public_Variables

```
Option Explicit

Public Const WB_INCOME_LIST_FILEPATH As String = "S:\Lumin Admin Docs\Ascentric Cash Management\"
Public Const WB_INCOME_LIST_FILENAME As String = "Ascentric Client Income List.xlsm"

Public Const ASCENTRIC_TOP_LEFT_CELL_STRING As String = "Adviser" '/ At present, on row 3
Public Const NOTES_TOP_LEFT_CELL_STRING As String = "Adviser"

'/ Headers for this workbook

Public Const ADVISER_NAME_HEADER As String = "Adviser"
Public Const CLIENT_NAME_HEADER As String = "Client Name"
Public Const ASCENTRIC_NUMBER_HEADER As String = "Client Ref"
Public Const PEODUCT_CODE_HEADER As String = "Product"
Public Const WRAPPER_VALUE_HEADER As String = "Wrapper Value (WV)"
Public Const INVESTMENT_MODEL_HEADER As String = "Model Name"
Public Const DEPOSIT_ACCOUNT_HEADER As String = "Deposit Cash"
Public Const RESERVE_ACCOUNT_HEADER As String = "Reserve Cash"
Public Const INCOME_ACCOUNT_HEADER As String = "Income Account"
Public Const TRADING_ACCOUNT_HEADER As String = "Trading Account"
Public C

A Worksheet is a class

And a class is a blueprint for an object.

'/ Headers for this workbook

Public Const ADVISER_NAME_HEADER As String = "Adviser"
Public Const CLIENT_NAME_HEADER As String = "Client Name"
Public Const ASCENTRIC_NUMBER_HEADER As String = "Client Ref"
Public Const PEODUCT_CODE_HEADER As String = "Product"
Public Const WRAPPER_VALUE_HEADER As String = "Wrapper Value (WV)"
Public Const INVESTMENT_MODEL_HEADER As String = "Model Name"
Public Const DEPOSIT_ACCOUNT_HEADER As String = "Deposit Cash"
Public Const RESERVE_ACCOUNT_HEADER As String = "Reserve Cash"
Public Const INCOME_ACCOUNT_HEADER As String = "Income Account"
Public Const TRADING_ACCOUNT_HEADER As String = "Trading Account"
Public Const NOTES_HEADER As String = "Notes"

These global variables could be exposed as properties of the worksheet they belong to.

For example, the Notes sheet could have these members:

Private Const ADVISER_NAME_HEADER As String = "Adviser"
Private Const CLIENT_NAME_HEADER As String = "Client Name"
Private Const CLIENT_REF_HEADER As String = "Client Ref"
Private Const PRODUCT_CODE_HEADER As String = "Product" 'fixed a typo here
Private Const NOTES_HEADER As String = "Notes"

Public Property Get HeaderAdviserName() As String
    HeaderAdviserName = ADVISER_NAME_HEADER
End Property

Public Property Get HeaderClientName() As String
    HeaderClientName = CLIENT_NAME_HEADER
End Property

Public Property Get HeaderClientRef() As String
     HeaderClientRef = CLIENT_REF_HEADER
End Property

Public Property Get HeaderProductCode() As String
     HeaderProductCode = PRODUCT_CODE_HEADER
End Property 

Public Property Get HeaderNotes() As String
     HeaderNotes = NOTES_HEADER
End Property

What gives?

• You eliminate a bunch of globals

• You expose a sheet's headings as properties of that sheet

In a similar way, you could have a class module representing some sort of "model" for the client income worksheet, and expose get/let accessors for properties, instead of using globals; name the class ClientIncomeWorksheetModel and you'll never need a comment like this:

'/ Headers for Client Income workbook

I can't help but notice you're no longer outdenting Dim statements. Well done! It makes the code much easier to read top-to-bottom. I like that you're declaring them right next to where they're assigned.

I don't think the functions in the Get_Sheet_Data module need to actually Activate the worksheet they're getting the dataRange for. In fact, this Activate call feels like a side-effect of a function call, which is somewhat surprising/unexpected: if I say "I'm going to fetch the data from sheet X", and I do that and only that, would you expect Application.ActiveSheet to hold a new worksheet reference when I return with a value?

The module naming is a bit awkward, but I can see how it can help browse a VBA project in the VBE's poorly tooled Project Explorer. At one point in 2016 Rubberduck 2.0 will be released, and you can drop these funky prefixes and do this instead:

Option Explicit
'@Folder Foo.Bar

And then that module/VBComponent will show up in Rubberduck's Code Explorer under a folder named "Bar", itself under a folder named "Foo".

But until that's released, leveraging the alpha sort of VBComponents in the Project Explorer is pretty much your only hope to facilitate organization and navigation.

StackExchange Code Review Q#114641, answer score: 4