Clear worksheets and set rows using Excel VBA macros

Was preparing my personal budget Excel workbook for next year. It contains a Summary worksheet and a worksheet for each month of the year. Copied this year’s workbook, renamed it and thought there must be a better way to clear the month worksheets and set recurring expenses. So I wrote 3 Excel VBA procedures (1 Function and 2 Subs). The Sub procedures can be run (after they have been saved) by going to the Developer tab, Macros and choosing the macro to run.

First off, a function to return the list of month names. This was covered in my previous post Return String Array in Excel VBA Macro.

Function GetMonths()
    Rem Difference btw Function and Sub is that the former can return values while the latter can't
    GetMonths = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
End Function

Next, a macro to clear all content in each month worksheet. The first row in each worksheet is used for the header, hence the range starts from A2. There are 5 columns, A to E, the last being a remarks column. 1000 is just an arbitrary number – I don’t usually have so many income/expense records in a month.

Sub ClearMonths()
    Rem Clear all month worksheets
    Dim Month, Months
    Months = GetMonths()

    For i = LBound(Months) To UBound(Months)
        Month = Months(i)
    Next i
End Sub

Finally, a macro to set recurring expenses. Note that the amounts have been changed to arbitrary numbers 😛 For the Savings expense, it will be entered into every month (cells A4 to D4). For the Property Tax Instalment, it will only be entered from Jan to Sep.

Sub SetMonthlyExpenses()
    Rem Set recurring monthly expenses
    Dim Month, Months, StartRow, Expenses(1), Expense
    'Expenses start from row 4 in blank worksheets
    'Row 1 is header, row 2 left blank for income and row 3 as buffer
    Months = GetMonths()
    StartRow = 4
    'Each row = Array(No. of months to enter expense starting from Jan, Day of month, Item, Category, Amount)
    Expenses(0) = Array(12, 1, "POSB MySavings", "Savings", 10)
    Expenses(1) = Array(9, 5, "Property Tax Instalment", "Property Tax", 1.23)
    For MonthIndex = LBound(Months) To UBound(Months)
        Month = Months(MonthIndex)
        ExpenseRow = StartRow
        For ExpenseIndex = LBound(Expenses) To UBound(Expenses)
            Expense = Expenses(ExpenseIndex)

            'Break if no need to repeat expense from this month onwards
            If (MonthIndex + 1) <= Expense(0) Then
                'Skip index 0 and special calculation for index 1
                Range("A" & ExpenseRow) = Expense(1) & " " & Month
                For i = 2 To UBound(Expense)
                    Range(Chr(65 + i - 1) & ExpenseRow).Value = Expense(i)
                Next i
            End If
            'Increment row number
            ExpenseRow = ExpenseRow + 1
        Next ExpenseIndex
    Next MonthIndex
End Sub

Definitely beats manually clicking on 12 worksheets, selecting the range, clearing them, pasting the recurring expenses and modifying the dates! If you have any more questions, feel free to hit the Contact page - Adhuc!