So, just to clarify, when we talk about programming Excel, we are not talking about using the standard functions and formulas. In this series of posts, the aim is to get you started with some very simple and straightforward programming using Visual Basic for Applications (VBA), which is built in to most of the Microsoft Office suite of applications.
It is most widely used in Excel and the Access database application, although you can also customise Word, PowerPoint, Outlook and so on, should you wish.
By way of a caveat, this i snot intended as a full course in programming – there are plentiful resources online to help you do that, so if you want to go into detail this is not the place. However, I hope to demonstrate that it doesn’t have to be a frightening prospect even for the non-technical and, once you get the hang of it, you will almost certainly want to dig deeper.
What are the advantages of using VBA?
Possibly the first and foremost reason for using VBA is consistency. Excel is famous (or infamous) for causing problems by returning flawed information and incorrect calculations as a result of user error – it’s pretty easy to make a mistake in a formula, and we have all done it. Coding calculations in VBA can overcome this problem – even if the calculations need to be reset each time new data is pasted into the spreadsheet.
Second, you don’t need the “office Excel expert” to create the worksheet every time – once it is built, it can be used by anyone and the same complex calculations and functions can be run. More than that, it’s possible using VBA to go above and beyond the functionality available in the standard formulas, so apart from being more reliable and faster, your spreadsheet can be more powerful as well!
Finally, speed is of the essence. A worksheet containing VBA modules can create a complex set of formulas, and apply various processes, in seconds.
What about disadvantages?
I’d be lying if I said it was all roses. The most obvious drawback is that you need to be able to write the code, but as we will hopefully find out in this series of posts, that isn’t as bad as it might be,
Coding anything is also subject to the whims of third parties. If Microsoft stop supporting VBA, or if your worksheets simply get to old, they may stop working properly.
VBA is already old, and there are various alternatives that you can also use (we’ll save those for another day) but VBA has been so widely used for so long, and has such a massive user base, that it is unlikely to go anywhere in the medium term.
So what can I do with VBA?
The answer to this is you can do a lot. One recent project I did created an Excel workbook from a database application, and exported a table of data into it. Once the data had been exported, I then did the following:
- Inserted a number of columns
- Added formulas to the newly inserted columns to supply the values
- Split the data horizontally into past and future transactions, with a different coloured background to make it easy for the user to see where they were.
- Added filters to the column headers
- Created additional worksheets containing charts and pivot tables, based on the new data
- Tidied up fonts and column widths so everything fitted nicely
- Inserted some lines, borders and boxes to aid the visual presentation
All of the above was done from an external application, so the resulting spreadsheet contained the data and the chart objects and pivot tables and so on, but none of the code used to create them. This meant that a “master copy” of the code always stayed with the owner of the data, and was never circulated, so could not be damaged or fiddled with.
It’s a lot of fun, once you get started, so hop to the next post in the series and see what you think!