Microsoft Excel, Tailor-Made For Your Business Thanks To VBA

You're probably familiar with Microsoft Excel. Whatever your line of business, whatever role you have in the organisation, whatever size of company it may be, you've probably come into contact with Excel fairly frequently. It is the standard spreadsheet, by far the most popular and widespread application for managing accounts and related data. The essentials of what Excel can do for you are almost as well known; it can add, subtract, multiply and divide, it can produce graphs, it can automatically update the results of your equations as you alter the figures.

Many will be aware of a number of other functions that Excel can carry out for you. However, there is more available that just that. Excel can be made to do almost anything you tell it to, provided you tell it in a language it understands. That language is Microsoft Visual Basic for Applications (VBA) - and the words it uses are written in plain English. You don't have to learn a thick and impenetrable programming language to get to grips with VBA, just a very simple syntax in which to fit actual words (rather than code, curious acronyms or unexpected abbreviations). You may well find - particularly if this still seems daunting or unclear - that a short training course in VBA can help you to get to grips with the language.

Excel can be customised to do your work for you by creating small automated programs called 'macros'. An Excel macro is simply a series of Excel commands brought together so that, by activating the macro, all the commands are executed automatically. For instance, you may want an entry in a particular part of your spreadsheet to automatically be reproduced elsewhere.

Excel 2007 has a macro recording tool - you simply start recording, work through your process manually, and that process is saved as a macro. Any time in future you want your new entry to be automatically reproduced, you can run the macro from the ribbon at the top of the Excel 2007 screen, and this will be done automatically. Similarly, if you need to find an average from a range of numbers, you can record the process of adding them together and then dividing by the number of entries involved, and by recording this, averages can be worked out instantly in future. And, to save further time, you can even assign a shortcut key to your macro.

However, what happens if you want to use one of these macros, but need it to change slightly from the previous process, or fine tune it a little? A recorded macro is written in the VBA language by the software - but this can be freely accessed and tweaked afterwards by the user. Perhaps you'll now need to find the average of a different set of figures; within the VBA script used to make the macro, there will be a part relating to the cells used in the calculation (it will use the word 'cells' - VBA really does use real English). A quick change there - or copy and pasting to a new macro, making the change there, allowing you to do both if needed - and the macro, and Excel as a whole, moves along with your new requirements.

This way, VBA can be used to make sure that Excel is customised to fit your particular demands, not only once, but always. Time, as we are all aware, is money, and with a little training on how the language fits together, VBA can be a hugely effective labour-saving tool. Where once you or your staff may have had to waste time working through the same procedure over and over, VBA allows you to hit one keyboard shortcut - and then move on to find something far better to do with your time.

---

Author is a freelance copywriter. For more information on vb for access trainer, please visit http://www.microsofttraining.net

---