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 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
Dim dict As New Scripting.Dictionary? \$\endgroup\$ – Dirk Reichel Dec 22 '15 at 23:46