Data table retrieval and indexing (+ interfacing between workbooks)

Question

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: enter image description here

Notes: enter image description here

Client Income: enter image description here

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 Const NOTES_HEADER As String = "Notes"
'/ Headers for Client Income workbook
Public WsIncClientNameHeader As String
Public WsIncIncomeAmountHeader As String
Public WsIncPaymentFrequencyHeader As String
Public WsIncPaymentDayHeader As String
Public WsIncBaseMonthHeader As String
Public WsIncAscentricWrapperHeader As String
Public WsIncAscentricAccountNumberHeader As String
Public WsIncAccountToPayFromHeader As String

---

Module B2_Project_Manual_Settings

Option Explicit
Public Sub SetWorkbooks(ByRef wbUninvestedCash As Workbook, ByRef wbIncomeList As Workbook)
 Set wbUninvestedCash = ThisWorkbook
 Set wbIncomeList = GetWorkbook(WB_INCOME_LIST_FILENAME, WB_INCOME_LIST_FILEPATH)
End Sub
Public Function GetAscentricHeaders() As Collection
 Dim col As Collection
 Set col = New Collection
 
 col.Add ADVISER_NAME_HEADER
 col.Add CLIENT_NAME_HEADER
 col.Add ASCENTRIC_NUMBER_HEADER
 col.Add PEODUCT_CODE_HEADER
 col.Add WRAPPER_VALUE_HEADER
 col.Add INVESTMENT_MODEL_HEADER
 col.Add DEPOSIT_ACCOUNT_HEADER
 col.Add RESERVE_ACCOUNT_HEADER
 col.Add INCOME_ACCOUNT_HEADER
 col.Add TRADING_ACCOUNT_HEADER
 
 Set GetAscentricHeaders = col
End Function
Public Function GetNotesHeaders() As Collection
 Dim col As Collection
 Set col = New Collection
 
 col.Add ADVISER_NAME_HEADER
 col.Add CLIENT_NAME_HEADER
 col.Add ASCENTRIC_NUMBER_HEADER
 col.Add PEODUCT_CODE_HEADER
 col.Add NOTES_HEADER
 
 Set GetNotesHeaders = col
End Function
Public Function GetClientIncomeHeaders() As Collection
 Dim col As Collection
 Set col = New Collection
 
 col.Add WsIncClientNameHeader
 col.Add WsIncIncomeAmountHeader
 col.Add WsIncPaymentFrequencyHeader
 col.Add WsIncPaymentDayHeader
 col.Add WsIncBaseMonthHeader
 col.Add WsIncAscentricWrapperHeader
 col.Add WsIncAscentricAccountNumberHeader
 col.Add WsIncAccountToPayFromHeader
 
 Set GetClientIncomeHeaders = col
End Function

---

Module C1_Uninvested_Cash_Report

Option Explicit
Public Sub GenerateUninvestedCashReport()
 '/========================================================================================================================================================================================================================================================================
 '/ Description:
 '/========================================================================================================================================================================================================================================================================
 '/ Author: Zak Armstrong
 '/
 '/ Inputs:
 '/ A copy-pasted Data Table from Ascentric (provided as excel export). Specifically, the company-wide "Uninvested Cash" Report.
 '/ An internal spreadsheet used to track all Ascentric clients who take regular income.
 '/ A table of account notes, indicating the account to be appended to.
 '/
 '/ Outputs:
 '/ A list of all uninvested cash (by account), sorted by % of total value.
 '/ A list of all scheduled income payments, with indications of whether sufficient cash is available to pay it.
 '/ A list of all Accounts not currently attached to a Model
 '/ Account Notes attached to the above
 '/
 '/ Program flow:
 '/ Import client income data table from external workbook.
 '/ Locate specific columns
 '/ Set Data Number Formats
 '/ Get all Inputs as data Arrays
 '/ Validate all data (E.G. removing "--"(used to indicate empty cells), checking that required columns all contain the correct kind of data (Data types, String values etc.)
 '/ Cross-reference data tables and compute values
 '/ Format Outputs (sorting, visual formatting etc.)
 '/
 '/ Data structure / Unique identifiers:
 '/ Ascentric account number. Usually 1 per client (2 if a personal and a joint account). All-digits, no fixed length, no leading zeroes. Typically 8 or 9 digits.
 '/ Product codes. Each account can contain multiple products E.G. Pension Account (APA), ISA, General Investment Account (GIA). sometimes more than one of each E.G. APA, APA2, APA3 etc.
 '/ Each line in each table refers to one Account number and one product code. If either is missing, data cannot be allocated.
 '/========================================================================================================================================================================================================================================================================
 '/========================================================================================================================================================================================================================================================================
 
 Dim wbUninvestedCash As Workbook, wbIncomeList As Workbook
 
 SetWorkbooks wbUninvestedCash, wbIncomeList
 
 ActivateAndUnmerge wsAscentricData
 
 '/ Ascentric Data Variables ======================================================================================
 Dim ascentricDataArray As Variant, ascentricDataRange As Range, ascentricColumnNumbers As Scripting.Dictionary
 
 Set ascentricDataRange = GetAscentricDataRange
 ascentricDataArray = ascentricDataRange
 Set ascentricColumnNumbers = GetAscentricColumnNumbers(ascentricDataArray)
 
 '/ Notes Data Variables ======================================================================================
 Dim notesDataArray As Variant, notesDataRange As Range, notesColumnNumbers As Scripting.Dictionary
 
 Set notesDataRange = GetNotesDataRange
 notesDataArray = notesDataRange
 Set notesColumnNumbers = GetNotesColumnNumbers(notesDataArray)
 
 '/ Client Income Data Variables ======================================================================================
 Dim clientIncomeDataArray As Variant, clientIncomeColumnNumbers As Scripting.Dictionary
 
 wbIncomeList.GetDataTableHeaders clientNameHeader:=WsIncClientNameHeader _
 , incomeAmountHeader:=WsIncIncomeAmountHeader _
 , paymentFrequencyHeader:=WsIncPaymentFrequencyHeader _
 , paymentDayHeader:=WsIncPaymentDayHeader _
 , baseMonthHeader:=WsIncBaseMonthHeader _
 , ascentricWrapperHeader:=WsIncAscentricWrapperHeader _
 , ascentricAccountNumberHeader:=WsIncAscentricAccountNumberHeader _
 , accountToPayFromHeader:=WsIncAccountToPayFromHeader
 
 clientIncomeDataArray = GetClientIncomeDataArray(wbIncomeList, Year(Now))
 Set clientIncomeColumnNumbers = GetClientIncomeColumnNumbers(clientIncomeDataArray)
 
 CloseWorkbook wbIncomeList
 
 '/======================================================================================
 
 '/====================================================================================================================================
 ' Check that the arrays contain the expected data in the expected places
 '/====================================================================================================================================
 
 '/====================================================================================================================================
 '/ Get all regular income from arrAscentricData into the income report
 '/====================================================================================================================================
 
 '/====================================================================================================================================
 '/ Grab all regular income accounts from ascentric data
 '/====================================================================================================================================
 
 '/====================================================================================================================================
 '/ Cross reference with Monthly Income data
 '/====================================================================================================================================
 
 '/====================================================================================================================================
 '/ Cross reference with Quarterly Income data
 '/====================================================================================================================================
 
 '/====================================================================================================================================
 '/ Create "Not attached to Model" Report
 '/====================================================================================================================================
 
 '/====================================================================================================================================
 '/ Create Uninvested Cash Report
 '/====================================================================================================================================
 
 '/====================================================================================================================================
 '/ Match notes to their respective accounts
 '/====================================================================================================================================
 
 '/====================================================================================================================================
 '/ Transpose income report and no model report, print to worksheet
 '/====================================================================================================================================
 
 '/====================================================================================================================================
 '/ Visual / Data Fromatting
 '/====================================================================================================================================
 
End Sub

---

Module C2_Get_Sheet_Data

Option Explicit
Public Function GetAscentricDataRange() As Range
 wsAscentricData.Activate
 Dim dataRange As Range
 Dim topLeftCell As Range, searchRange As Range
 Set searchRange = wsAscentricData.Range(Cells(1, 1), Cells(10, 10)) '/ 10x10 is a purely arbitrary search range
 Set topLeftCell = CellContainingStringInRange(searchRange, ASCENTRIC_TOP_LEFT_CELL_STRING)
 
 Dim ascentricTableFinalRow As Long, ascentricTableFinalCol As Long
 
 AssignRangeBoundsOfData topLeftCell, UB1:=ascentricTableFinalRow, UB2:=ascentricTableFinalCol, useCurrentRegion:=False '/ Not Current Region because un-merging may split table into multiple regions
 Set dataRange = wsAscentricData.Range(topLeftCell, Cells(ascentricTableFinalRow, ascentricTableFinalCol))
 
 Set GetAscentricDataRange = dataRange
 
End Function
Public Function GetNotesDataRange() As Range
 wsAccountNotes.Activate
 Dim dataRange As Range
 Dim topLeftCell As Range, searchRange As Range
 Set searchRange = wsAccountNotes.Range(Cells(1, 1), Cells(10, 10))
 Set topLeftCell = CellContainingStringInRange(searchRange, NOTES_TOP_LEFT_CELL_STRING)
 
 Dim notesTableFinalRow As Long, notesTableFinalCol As Long
 
 AssignRangeBoundsOfData topLeftCell, UB1:=notesTableFinalRow, UB2:=notesTableFinalCol, useCurrentRegion:=False
 Set dataRange = wsAccountNotes.Range(topLeftCell, Cells(notesTableFinalRow, notesTableFinalCol))
 
 Set GetNotesDataRange = dataRange
End Function
Public Function GetClientIncomeDataArray(ByRef wbIncomeList As Workbook, ByVal ixYear As Long) As Variant
 Dim dataArray As Variant
 Dim codenameClientIncomeWs As String
 
 '/ I don't like the hacky nature of this, but it will run fine for the next 2 years by which time I sincerely *hope* we'll have moved to a proper database system
 
 With wbIncomeList
 If ixYear = 2015 Then .GetWorksheetCodenames ws2015:=codenameClientIncomeWs
 If ixYear = 2016 Then .GetWorksheetCodenames ws2016:=codenameClientIncomeWs
 If ixYear = 2017 Then .GetWorksheetCodenames ws2017:=codenameClientIncomeWs
 End With
 
 dataArray = wbIncomeList.GetDataArrayFromSheetByCodename(codenameClientIncomeWs)
 
 GetClientIncomeDataArray = dataArray
 
End Function

---

Module C3_Get_Column_Indexes

Option Explicit
Public Function ColumnIndexesOfStringsInArrayRow(ByRef searchStrings As Collection, ByRef dataArray As Variant, ByVal rowToSearch As Long) As Scripting.Dictionary
 Dim dict As Scripting.Dictionary
 Set dict = New Scripting.Dictionary
 
 Dim arrSearchRow As Variant
 arrSearchRow = RowFrom2dArray(dataArray, rowToSearch)
 
 Dim searchText As String, colNum As Long
 Dim ix As Long
 
 For ix = 1 To searchStrings.Count
 searchText = searchStrings(ix)
 colNum = IndexInArray1d(arrSearchRow, searchText)
 dict.Add searchText, colNum
 Next ix
 
 Set ColumnIndexesOfStringsInArrayRow = dict
End Function
Public Function GetAscentricColumnNumbers(ByRef ascentricDataArray As Variant) As Scripting.Dictionary
 Dim headers As Collection
 Set headers = GetAscentricHeaders
 
 Dim headerRow As Long
 headerRow = LBound(ascentricDataArray, 1)
 Dim dict As Scripting.Dictionary
 Set dict = ColumnIndexesOfStringsInArrayRow(headers, ascentricDataArray, headerRow)
 
 Set GetAscentricColumnNumbers = dict
End Function
Public Function GetNotesColumnNumbers(ByRef notesDataArray As Variant) As Scripting.Dictionary
 Dim headers As Collection
 Set headers = GetNotesHeaders
 
 Dim headerRow As Long
 headerRow = LBound(notesDataArray, 1)
 Dim dict As Scripting.Dictionary
 Set dict = ColumnIndexesOfStringsInArrayRow(headers, notesDataArray, headerRow)
 
 Set GetNotesColumnNumbers = dict
End Function
Public Function GetClientIncomeColumnNumbers(ByRef clientIncomeDataArray As Variant) As Scripting.Dictionary
 Dim headers As Collection
 Set headers = GetClientIncomeHeaders
 
 Dim headerRow As Long
 headerRow = LBound(clientIncomeDataArray, 1)
 Dim dict As Scripting.Dictionary
 Set dict = ColumnIndexesOfStringsInArrayRow(headers, clientIncomeDataArray, headerRow)
 
 Set GetClientIncomeColumnNumbers = dict
End Function

• Dirk Reichel: Hmm... why not directly Dim dict As New Scripting.Dictionary?

Answer 1

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.

• Kaz: I didn't know I could just add properties to non-class code modules. That's awesome!
• Mathieu Guindon: Well ThisWorkbook and all Worksheet objects are class modules; and user forms, too! :)