Robust Small Business Account VBA system

[Rhodie72]

Thank you for taking the time to read this thread. Please help me keep this thread neat and useful to others by not posting anything that detractgs from the objectives

It is about Accounting programs and systems being created in Excel VBA. It covers file management fundamentals; Double entry bookkeeping; Security systems for VBA; networked file distribution; external referenced data and much more besides.

I am in the process of creating an accounting system in VBA. It requires a fundamental understanding of how an accounting system works and this one is based upon the ancient tried and tested double entry model. There are a few issues I'd like to discuss with people over time and try to make sure that it complies with UK GAAP. For those who don't know what that means it is:

United Kingdom Generally Accepted Accounting Principles. A mouthful that means "done in a way that everyone accepts according to established accounting systems" and the best part is that I'm doing this open sourced so that anyone may contribute to a good accounting system and use it for free.

Try keep opinions out of this thread and post anything that will contribute usefully to the overall outcome.

I will post the basic code when it's available and tested so that it can be publicly tested.

The very first part is about the planning of the system because no good system arrives without good plans. For this we need to consider the overall system of accounting and how it will achieve the end result: correct figures, balanced books, accurate records, efficient time management and accurate tax returns information for submission to government. This is the main aim of the system: to be acceptable for government approval.

 

[Rhodie72]

Separation of Functions: keeping management, purchases and sales apart

Management functions
it is important to keep the management console separate from the data functions in the system. To achieve this I am going to create a management console that uses forms. Everything in the whole system wil be governed by what this part creates and establishes. It will ensure that the system uses the correct filing and file management protocol.

1.0) The file system will determine the structure of the data storage and methods of data separation.
1.1) system will be stored in "Accounts/year/*" format. All the sub functions of the accounting system will be stored in subdirectories of the year in which they occurred.

2.0) We need to separate the management level accounting data from the user level data.
2.1) Management data would be trial balance, personal information as legislated under UK Data Protection Act 1998 or similar, Bank Accounts data and, the Capital Account data
2.2) Purchases accounts
2.3) Sales accounts
2.4) Assets accounts

3.0) Security systems must be a function of the operating system and also basic level access according to VBA code that denies users access to information they should not have access to except for indirect editing by the system. Data integrity is vital so part of the system needs to make sure that a secondary copy of all information is kept up to date in a secondary location that is also baked up.
3.1) Operating system security is beyond the remit of this dialogue but the coverage of VBA interactivity is vital. Security functions will always have a direct impact on functionality and the permissions system needs to be transparent once authorised. Achieving this means use of the registry in Windows to store the security keys to make the system transparent but useful.
3.1.1) It is possible to encrypt the security keys so that a user who is smart won;t easily bypass the security system without reprogramming the whole package. VBA security in Excel is a misnomer because it is inherently insecure. This is why we use the OS to manage data security but use the less secure VBA to key access to the different parts of the accounting system.

 

[Rhodie72]

After a lot of planning and design input, I've determined the file structure which is the foundation of the whole system. From here all the documentation is stored and created over time. Without the correct system it is nearly impossible to sort the data into a sensible arrangement that is coherent and cogent to the process of each business. There is also the issue as to which business one can refer to as there may be several entities that the system can manage simultaneously. In this line of thought it occurred to me that this could be used by students who are studying for an accounting exam and want to learn more about the digital side of things.

The foundation of the system begins with these simple lines: found the source on MS technet:

Sub EnumDrives()
Dim objFSO  As Object, colDrives As Object, objDrive
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set colDrives = objFSO.Drives
    For Each objDrive In colDrives
        Debug.Print "Drive letter: " & objDrive.DriveLetter
    Next
End Sub

This allows me to populate drives in a combobox in a UserForm and filter them to fixed disk types only. There are certain ways of writing the data to DVD drives but they are not reliable in the longer term and require special programs to manage them. Added to this fact, they are slow too. It is better performance-wise to write to a fixed or network disk (which could also be a dynamic DVD disk as a workaround)

 

[Rhodie72]

A sample design:

Folder Level

Business & System Level

Time Level

Folder level

Workbook level

Sheet level

<tbody>
</tbody>