Why Learn Excel Macros?

By Author: Ed Bolton
Total Articles: 17
Comment this article

If you are reading this, you are probably one of the 400 million users of Microsoft's flagship spreadsheet program, Microsoft Excel. You may well have developed your own files to track the movement of cash or inventory around your own organization. Some of these sheets may be quite extensive but, if you've never come across macros, then you've only scratched the surface of Excel's capabilities.



At an intermediate level, a spreadsheet tends to contain a lot of raw data that you can analyze using various logical and arithmetic functions. It can get very complicated. At an expert level, a lot of these formulas start to disappear from the sheet. Some get rationalized, but many get replaced by background code.



Background code written in Excel gets called a macro. Macros are pieces of Visual Basic (VBA) programming that can interact with the spreadsheet as you see it. This means the code can read numbers from your spreadsheet, do some math, and put an output elsewhere in your spreadsheet. Ultimately, they can perform any procedures that you can with a keyboard and mouse.

...
... br />
Macros get used for two reasons. Firstly, to automate time consuming processes so they no longer require as much manual effort. Secondly, Excel consultants can use Visual Basic to build an additional interface on top of that you would normally see. This means they can force users to enter data into forms. By controlling the inputs to a spreadsheet, it can become much easier to analyze the outputs. Visual Basic forms can improve the consistency of your data.



Fortunately, macros aren't just tools for Excel experts. Microsoft let you record your own macros at the click of a button. Your keystrokes and mouse clicks will all be encoded in Visual Basic. So if you re-format a cell in bold, italic type, Excel will give you the code to repeat the procedure. You can assign each procedure a shortcut key so, next time you need to re-format a cell, you can simply select the cell and hit the relevant short cut. You can use macros without ever having to write any code yourself!



Of course, the fact Excel will give you the code for any particular action can help you develop your own macros. As with the rest of the Office suite, macros are designed not to require any formal training, but instead they allow you to learn new skills on-the-job. Most workplaces will have their own Excel guru who you can approach should you ever run into difficulty. There are also plenty of training videos available on YouTube.



Twenty years ago, computer programming was a job for specialists and math graduates because there was no obvious way to learn the skills yourself. Traditionally, you would have to write code simply to get graphics on the screen. These days, we have moved to a point at which we can rely on e.g. Windows and Internet Explorer to quickly interpret any code we write.



That gives us the opportunity to make our own websites using html and php. It also gives us the opportunity to make our own Excel using Visual Basic Editor and macros. If you don't grasp that opportunity, you may end up doing a lot of unnecessary work. Even worse, it will probably be extremely boring and repetitive work.

About the author: Ed Bolton is the founder of Excel4Business, a service to help you find Excel consultants.