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.