Example of Excel/Visual Basic for Applications Project
Small Business Accounting Tool
Four Elms Bookkeeping's Small Business Accounting Tool is an Excel workbook whose standard
functionality has been extended by incorporating thirty different
Visual Basic for Applications (VBA) code modules and interactive forms. Each module providers an element of a dual entry
bookkeeping system which includes the key functions and interfaces you would expect from a commercial
accounts package. It was designed to avoids the need to purchase commercial software licences when preparing accounts
for customers whose accounting transactions are relatively straightforward.
The suite of VBA modules can connect to free open source database software for storing, backing up or retrieving
accounting data as often as required.
Although its basis is a Microsoft Excel workbook no numbers are entered by the user into worksheet cells. Instead
a series of buttons embedded in the worksheets activate input forms and run software modules written in the Visual Basic for Applications (VBA)
programming language. The VBA code checks data input by the user for common errors before the software processes
and posts them to the accounts. The Excel worksheets function mainly as displays for
The accounting tool can be used with any
of the following versions of Microsoft Excel: Excel 1997/2003, Excel 2007 or Excel 2010 and can be run
with the Windows 7 and Windows 10 operating systems. The Visual Basic code behind the
Excel worksheets applies a standard double entry bookkeeping system.
The VBA enhanced workbook provides the following features:
General Ledger which can contain up to 999 accounts
Sales and purchase ledgers storing customer and supplier contact details
Ability to create up to 1000 sales ledger and purchase ledger accounts
Creation of sales invoices
Automatic calculation of early payment discounts received from suppliers or due to customers
Balance Sheet and Profit & Loss Accounts recalculated after every new transaction
Bank reconciliation utility
Facility to set up recurring payments or receipts with automatic reminders when they fall due
Audit trail of each transaction posted
Choice of ten VAT tax codes to use with each transaction
Calculations of VAT due under VAT Margin Scheme included (for second hand car dealers and other traders in
second hand goods)
VAT100 form with box entries automatically populated. VAT returns can be calculated on an accruals or cash accounting basis.
Facility to enter details of stock items purchased or sold for quick entry into sales or purchase records.
Automatic calculation of number of stock items held.
Aged debtors and creditors reports
Activity reports on any General Ledger accounts
Activity reports on any Sales or Purchase ledger account
Year end (or period end) journals
Automatic checks on consistency of ledgers, control accounts, balance sheet and profit & loss account after each
transaction with instant user alert if any error is found.
Automatic user prompting where input is inconsistent or missing
User input boxes programmed for easy entry of figures with automatic conversion to accounting format
Embedded SQL and ADO code allows quick access to the Open Source MySQL database for data backup, storage, retrieval and management of backup data
Multiple copies of the software can be made if users need to do accounts for more than one business.
If your organisation could benefit from customised Excel workbooks, Visual Basic macros or other coded calculation tools, please contact Four Elms Bookkeeping to discuss your requirements.