An Excel macro is a set of instructions that Microsoft Excel executes to perform an action, for example, a calculation. This set of instructions or macro is written in a computer programming language called Visual Basic for Applications or VBA. VBA or macro coding is easy to learn. The VBA editor is provided with Microsoft Excel. Macros help us to automate many repetitive tasks. We have written and executed two macros in the Excel training video. The first Excel macro shows a ‘welcome’ message. The second Excel macro calculates the total price of an item by adding ‘VAT’. When you run the first macro the cursor must be in its region and for running the second calculation macro you must place the cursor in its region. Later on we’ll learn how to run multiple macros automatically. The second ‘do while’ macro has a starting point: it checks whether there is data in row 2 and column 1. If true it calculates the total by adding the values of the two defined cells. Then it goes to the next row and again checks whether the cell in row 3 and column 1 has data. If it is true it continues its calculations. It keeps on ‘looping’ till it reaches the row x and column 1 which have no data. Here it stops. The program ‘knows’ its job is finished. Even if you find the ‘syntax’ or ‘grammar’ of the macros confusing now, don’t worry. It’s easy to learn and master with some practice. How does one write a macro practically?
- Click on Tools in the menu bar, select Macro and then Visual Basic
- The Microsoft Visual Basic editor opens up
- Click on Insert in the menu bar of the editor and select Module
- Then in the editor write ‘sub’ without the quotes, leave a space, followed by a name and parenthesis. Eg. sub welcome() or just press ‘Enter’ after the name
- The editor automatically inserts ‘End sub’.
- The name of the macro becomes ‘welcome’.
- Now all other words or code is written between these two lines as shown in the video
- The code writing follows exact rules like the English grammar and can be learnt easily
Further reading: Record and use Excel macros FAQ – Excel Macros, Excel VBA