How to Create Your Own Expense Manager via Excel VBA

This article will help you to create simple and easy to use Expense manager. Now you can log and track all your incomes, expenses and keep a track of your balances.

Download Now

If you want to start to use the software as soon as possible, then you can:

Download the Software Now

Otherwise, if you want to DIY, you can read the contents below.

Let’s prepare the GUI

Open a new workbook and save it as macro enabled excel file. We just need two sheets in the workbook. So delete extra sheets. Rename the Sheet1 as “Entry” and rename the Sheet2 as “Database”. Sheet “Entry” will be the front-end of our tool. Sheet “Database” will act as the back-end i.e., database of our tool.

On “Entry” sheet, create separate fields for the entry of “Income” and entry of “Expense”. Also create a “Summary” section to show total income, total expense and the balance amount.

We need three buttons. One is for adding Income, second is for adding expense and one for clearing the database.

Add formula

Under the “Summary” section, add this formula to the “Total income” field

=SUMIF(Database!B2:B18,"Income",Database!C2:C18)

Add the below formula to the “Total Expenses” field

=SUMIF(Database!B2:B18,"Expense",Database!C2:C18)

And finally add this formula to “Balance” section to display the Balance amount

=N9-N13

As our database is empty, all fields will be Empty. Soon as you add an expense or an income, you can see all fields showing their respective values.

Let’s prepare the database

The database for this tool will have 4 columns. First column is the “Date”, second is the “Type”, third is the “Amount” and last column is the “Notes”.

Let’s make it functional

Copy the script into a new module. Add the Sub “Clear_Database” to the “Clear Database” button on the sheet “Entry”. Add the Sub “Add_Income” to the “Add Income” button and the Sub “Add_Expense” should be attached to the “Add Expense” button.

Let’s test it

Enter an income and click “Add Income” button. Now the Summary section will display the “Total Income”. Try adding an Expense too.

Soon as you click the “Add Income” or “Add Expense” button you will notice that values you entered on “Entry” sheet will get saved as a new row on the “Database” sheet. Fields on the “Entry” sheet will be cleared automatically for new entry.

How it works?

Every time when you add an income or expense, the script will search for the last empty row in the sheet “Database” and adds the entry to it. The formula we entered will read all data in real time and displays the balance.

Modify it

You can easily modify the macro to read specific keywords in the “Notes” field and assign it to specific Tags or Categories like Food or Travel.

Script

Sub Clear_Database()
    Sheets("Database").Range("A2:D100").ClearContents
End Sub

Sub Add_Income()
    Dim v_lr As Long
    v_lr = Sheets("Database").Range("A" & Rows.Count).End(xlUp).Row + 1
    Sheets("Database").Range("A" & v_lr).Value = Sheets("Entry").Range("B13").Value
    Sheets("Database").Range("B" & v_lr).Value = "Income"
    Sheets("Database").Range("C" & v_lr).Value = Sheets("Entry").Range("B9").Value
    Sheets("Database").Range("D" & v_lr).Value = Sheets("Entry").Range("B17").Value
    Sheets("Entry").Range("B9").Value = ""
    Sheets("Entry").Range("B13").Value = ""
    Sheets("Entry").Range("B17").Value = ""
End Sub

Sub Add_Expense()
    Dim v_lr As Long
    v_lr = Sheets("Database").Range("A" & Rows.Count).End(xlUp).Row + 1
    Sheets("Database").Range("A" & v_lr).Value = Sheets("Entry").Range("H13").Value
    Sheets("Database").Range("B" & v_lr).Value = "Expense"
    Sheets("Database").Range("C" & v_lr).Value = Sheets("Entry").Range("H9").Value
    Sheets("Database").Range("D" & v_lr).Value = Sheets("Entry").Range("H17").Value
    Sheets("Entry").Range("H9").Value = ""
    Sheets("Entry").Range("H13").Value = ""
    Sheets("Entry").Range("H17").Value = ""
End Sub

Sometimes you may encounter corrupted Excel files when using this script. In such a case, you can use some third-party tools to repair corrupted Excel files. There are various recovery tools. Using DataNumen Excel Repair makes sure you get back all your lost data.

Author Introduction:

Nick Vipond is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair doc error and outlook recovery software products. For more information visit www.datanumen.com