7
\$\begingroup\$

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

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.

\$\endgroup\$
  • \$\begingroup\$ I didn't know I could just add properties to non-class code modules. That's awesome! \$\endgroup\$ – Kaz Dec 21 '15 at 16:04
  • \$\begingroup\$ Well ThisWorkbook and all Worksheet objects are class modules; and user forms, too! :) \$\endgroup\$ – Mathieu Guindon Dec 21 '15 at 16:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.