FOUR ELMS BOOKKEEPING Mudford, Yeovil, Somerset  01935 850807

Microsoft Office Macro Development

A tool to automate business processes

Do you regularly carry out the same calculations but using different numbers each time? Perhaps, for example, you calculate profits, expenses, wages or taxes due for different companies, clients or employees, using the same calculation method each time, but with different input figures. If this sounds like you then your work could be speeded up and automated by the use of Microsoft Excel macros. Macros can also be used to produce accounts, employee timesheets or payslips, send out emails, query databases, extract information from websites or automate many other tasks. In fact whenever a method of calculation can be specified, or a set of instructions for performing a task written down, then it may be possible to write a macro which does it all for you. Tailored user interfaces can be created which enable easy user input, control over how a macro behaves or provide a user-friendly display of the calculated result. You can see some(examples of VBA UserForms here ). Macros can also transfer data to databases for secure storage, including the free MySQL open source database. Accounting and bookkeeping is one of many areas in which VBA macro software can automate and speed up computations.

What are macros?

"Macro" is a loose term which refers to modules of executable computer code or software written in a language called Visual Basic for Applications (VBA) and which extend or customize one or more Microsoft Office application. The most common use of macros is to add new functionality to Microsoft Excel spreadsheets, so that they can perform more complex operations than is possible with the inbuilt cell formulae. Within Excel macros may exist within particular spreadsheets or workbooks and operated via control buttons ("Form Controls") embedded in the spreadsheet. But macros can also be written for use with any spreadsheet you may use. General use Excel macros operate more like scripts or small applications and can be set up to be run by clicking icons in the Quick Access ribbon.

Why use VBA macros?

Microsoft Excel is an application that is already present on an estimated nine out of every ten computers in business use. Therefore no new software licenses need be purchased and most macros are easily run on different computers. Staff are often already familiar with Excel so little training is needed to operate VBA macros. Tools to run and write macros are integral with every edition of Microsoft Office. Neither extra licences nor special hardware are required: standard laptops or desktop computers are usually all that is needed.

Macros with other Microsoft Office applications

The use of macros is not confined to Excel, although more macros are written for Excel than for any other Office application. You can automate activities in other Microsoft applications with macros. For example, Microsoft Word can be programmed to produce individually addressed letters to customers or clients whose details are stored in a Word table or database. Or Microsoft Outlook could be programmed to automatically generate emails to staff or customers on a mailing list, attach copies of reports extracted from a database or even execute a tailored mailbox cleansing routine written in VBA.

The Visual Basic for Applications language in which macros are written allows different Office applications to "talk" to each other. For example, Excel could instruct Microsoft Access (or another database) to run a report and return the extracted data. Excel might then import the data into a specially formatted spreadsheet and attach copies of the spreadsheet to multiple emails. Meanwhile another process can write the text content of each email introducing or explaining the report, with the final result emailed out to all the email addresses on a mailing list together with a personalised greeting.

Within the Microsoft operating system tools is a scheduler which can even be programmed to run such processes at fixed times each day, week or month, meaning you will never forget to carry out this task. PowerShell scripts can be written to link the Task Scheduler to VBA processes and achieve automated scheduling. This way routine tasks can be carried out with minimal consumption of time and resources.

How we can help.

Four Elms Bookkeeping offers a competitively priced macro writing service. If you believe that macros tailored to your precise requirements could save you time and cost on repetitive tasks, data input or calculations then contact Four Elms Bookkeeping now to discuss your requirements.