Excel Bookkeeping Application, Create Ledgers from Chart of Accounts and Journal

Question

I am writing an Excel bookkeeping application that would be suitable for a small business or contractor. The application is based on a book on double entry bookkeeping: https://www.amazon.co.uk/gp/product/B077KT965N/ref=ppx_yo_dt_b_d_asin_title_351_o03?ie=UTF8&psc=1

My first step in writing this is to automate the creation of Ledgers from a Journal. For this I use VBA to lookup accounts to create in a Chart of Accounts table and and then select the row items for each account entered in the Journal (used Excel FILTER function).

I am interested in any feedback and suggestions. I know this application is not finished, but rather than ask for a huge review, wanted to submit just the ledger creation part. I haven't yet added error handling.

The Excel file is here

I also created a github repository for anyone interested here. But it is early days so don't expect too much!

Code below.

Option Explicit

Sub CreateLedgers_Click()
  CreateLedgers
End Sub

Sub FormatHeader(rng As Range)
 rng.Font.Name = "Calibri"
 rng.Font.Size = 20
 rng.Font.Bold = True
End Sub

Sub AddHeadings(LedgerSheet As Worksheet, RowIndex As Integer, startColumnOffset As Integer)
' Add Date    Ref no. Account Debit   Credit  Notes headings
LedgerSheet.Cells(RowIndex, startColumnOffset) = "Date"
LedgerSheet.Cells(RowIndex, startColumnOffset + 1) = "Ref no."
LedgerSheet.Cells(RowIndex, startColumnOffset + 2) = "Account"
LedgerSheet.Cells(RowIndex, startColumnOffset + 3) = "Debit"
LedgerSheet.Cells(RowIndex, startColumnOffset + 4) = "Credit"
LedgerSheet.Cells(RowIndex, startColumnOffset + 5) = "Notes"
End Sub

Sub CreateLedgers()

    ' Beware VBA strange vector sizing, 3 means 4!
    Dim ledgernames(3) As String
    ledgernames(0) = "Assets Ledger"
    ledgernames(1) = "Expenses Ledger"
    ledgernames(2) = "Income Ledger"
    ledgernames(3) = "Liabilities Ledger"

    ' Sort Chart of Accounts table, otherwise creation of tabs won't work properly
    Dim tbl As ListObject
    Dim tablename As String
    tablename = "chart_of_accounts"
    Set tbl = Sheets("Chart of Accounts").ListObjects(tablename)
    ' Type of account is 1st column
    tbl.Range.Sort tbl.ListColumns(1).Range, xlAscending, Header:=xlYes
  

    ' Delete any prior Ledger tabs
    Dim numsheets As Integer
    numsheets = Sheets.Count
    Dim i As Integer
    ' we could delete a sheet in middle of sheet numbers so we count down
    For i = numsheets To 1 Step -1
      If Sheets(i).Name = ledgernames(0) Or Sheets(i).Name = ledgernames(3) _
        Or Sheets(i).Name = ledgernames(1) Or Sheets(i).Name = ledgernames(2) Then
        Application.DisplayAlerts = False
        Sheets(i).Delete
        Application.DisplayAlerts = True
      End If
    Next i
    

    ' Create Ledger sheets - Alphabetical order
    Dim newsheet As Worksheet
    ' Offset each new entry by 7 columns - ledgers arranged across sheet
    Dim AssetsOffset As Integer
    Dim LiabilitiesOffset As Integer
    Dim ExpensesOffset As Integer
    Dim IncomeOffset As Integer
    AssetsOffset = 0
    LiabilitiesOffset = 0
    ExpensesOffset = 0
    IncomeOffset = 0

    ' create sheets
    Dim ledger As Variant
    For Each ledger In ledgernames
      Set newsheet = ActiveWorkbook.Sheets.Add(after:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count))
      newsheet.Name = ledger
      newsheet.Range("A1") = "Period From"
      newsheet.Range("A2") = "Period To"
      newsheet.Range("B1") = Date
      newsheet.Range("B2") = Date
    Next

    ' Insert ledger headings & FILTER formula
    Dim cel As Variant
    For Each cel In Range(tablename).Rows
        If cel.Cells(1, 1).Value = "Assets" Then
          Sheets(ledgernames(0)).Cells(4, AssetsOffset + 1) = cel.Cells(1, 2).Value
          FormatHeader (Sheets(ledgernames(0)).Cells(4, AssetsOffset + 1))
          Sheets(ledgernames(0)).Cells(5, AssetsOffset + 1) = "(Asset Account)"
          AddHeadings Sheets(ledgernames(0)), 6, AssetsOffset + 1
          ' =FILTER formula
          Sheets(ledgernames(0)).Cells(7, AssetsOffset + 1).Formula2R1C1 = "=FILTER(Journal,(Journal[Date]>=R1C2)*(Journal[Date]<=R2C2)*(Journal[Account]=R4C" & AssetsOffset + 1 & "))"
          AssetsOffset = AssetsOffset + 7
        End If
        If cel.Cells(1, 1).Value = "Expenses" Then
          Sheets(ledgernames(1)).Cells(4, ExpensesOffset + 1) = cel.Cells(1, 2).Value
          FormatHeader (Sheets(ledgernames(1)).Cells(4, ExpensesOffset + 1))
          Sheets(ledgernames(1)).Cells(5, ExpensesOffset + 1) = "(Expenses Account)"
          AddHeadings Sheets(ledgernames(1)), 6, ExpensesOffset + 1
          Sheets(ledgernames(1)).Cells(7, ExpensesOffset + 1).Formula2R1C1 = "=FILTER(Journal,(Journal[Date]>=R1C2)*(Journal[Date]<=R2C2)*(Journal[Account]=R4C" & ExpensesOffset + 1 & "))"
          ExpensesOffset = ExpensesOffset + 7
        End If
        If cel.Cells(1, 1).Value = "Income" Then
          Sheets(ledgernames(2)).Cells(4, IncomeOffset + 1) = cel.Cells(1, 2).Value
          FormatHeader (Sheets(ledgernames(2)).Cells(4, IncomeOffset + 1))
          Sheets(ledgernames(2)).Cells(5, IncomeOffset + 1) = "(Income Account)"
          AddHeadings Sheets(ledgernames(2)), 6, IncomeOffset + 1
          Sheets(ledgernames(2)).Cells(7, IncomeOffset + 1).Formula2R1C1 = "=FILTER(Journal,(Journal[Date]>=R1C2)*(Journal[Date]<=R2C2)*(Journal[Account]=R4C" & IncomeOffset + 1 & "))"
          IncomeOffset = IncomeOffset + 7
        End If
        If cel.Cells(1, 1).Value = "Liabilities" Then
          Sheets(ledgernames(3)).Cells(4, LiabilitiesOffset + 1) = cel.Cells(1, 2).Value
          FormatHeader (Sheets(ledgernames(3)).Cells(4, LiabilitiesOffset + 1))
          Sheets(ledgernames(3)).Cells(5, LiabilitiesOffset + 1) = "(Liabilities Account)"
          AddHeadings Sheets(ledgernames(3)), 6, LiabilitiesOffset + 1
          Sheets(ledgernames(3)).Cells(7, LiabilitiesOffset + 1).Formula2R1C1 = "=FILTER(Journal,(Journal[Date]>=R1C2)*(Journal[Date]<=R2C2)*(Journal[Account]=R4C" & LiabilitiesOffset + 1 & "))"
          LiabilitiesOffset = LiabilitiesOffset + 7
        End If
    Next

End Sub

Answer 1

A few tricks:

1. Use arrays and Split.

   Dim aLedgernames() As String
   Dim sLedgernames As String
   sLedgernames="Assets Ledger/Expenses Ledger/Income Ledger/Liabilities Ledger"
   aLedgernames = Split(sLedgernames, "/")
   

2. Use With

   With rng.Font
       .Name = "Calibri"
       .Size = 20
       .Bold = True
   End With
   

Or

   For i = numsheets To 1 Step -1
        With Sheets(i)
            If .Name = ledgernames(0) Or .Name = ledgernames(3) _
                  Or .Name = ledgernames(1) Or .Name = ledgernames(2) Then
                 .Delete
            End If
        End With
    Next i

or even better this way

   For i = numsheets To 1 Step -1
        With Sheets(i)
            If InStr(sLedgernames, .Name) Then .Delete
        End With
    Next i

3. For static strings (headers, etc.) and functions

   Sub AddHeadings(LedgerSheet As Worksheet, RowIndex As Integer, startColumnOffset As Integer)
        LedgerSheet.Cells(RowIndex, startColumnOffset).Resize(1, 6) = Array ("Date", "Ref no.", "Account", "Debit", "Credit", "Notes") 
   End Sub
   

or

   newsheet.Range("B1").Resize(1, 2) = Date

4. An advice: do not leave error handling to the end of coding. It may lead to huge rewritings. Take extra care on sheet operations (.Add,.Delete, etc) because they often fail.