How to write a macro in Excel? Here are a few quick tips:
Excel Macro – How to Record
Excel Macro – How to Edit
Excel Macro – Improving speed of code
Excel Macro – Examples

Recording a macro in Excel

How to record a macro in Excel 2007 and Excel 2010

In Excel 2007 (and Excel 2010), the “Record Marco” menu option is under the “View” ribbon. Click on the “Macros” button towards at the right end of the ribbon and then click “Record Macro”. A pop-up box will ask you to enter the details of the macro. If you are just starting to record your own macro, you can simply click on the “ok” button. After this you can carry out the steps that you want like entering numbers, formulas, formatting cells, inserting charts etc etc. Once you are done you can stop recording the macro by clicking the “Stop Recording” option under the “Macro” button.

Alternatively, you can begin and stop recording a macro in Excel 2010 (and 2007) by clicking on the icon located at the bottom-left of the application window, right next to where it is labelled ‘Ready’.

You can now view the macro that you just recorded by clicking the “View Macros” option under the “Macros” button. Alternatively you can press Alt+F8 as the shortcut key. You can give a descriptive name to the macro or assign a shortcut key so that by pressing that key combination, the macro will begin execution.

How to record a macro in Excel 2003 (& earlier)

In Excel 2003 and earlier versions, you can record a macro by clicking on “Tools” option in the menubar and then clicking on “Macro” and then choosing the “Record New Macro” option. Click ok on the pop-up box that appears and then carry out the steps that you wish the record in the macro. Once you are done, you can click the “Stop Recording” option under “Macro” from the menu.

You can view the macro you just recorded by clicking on “Tools” option in the menubar and then clicking on “Macro” and then choosing the “Macros” option. Alternatively you can press Alt+F8 as the shortcut key. You can record a complete set of macros and then run them one after another to get a the steps related to a task accomplished at one go.

How to edit a macro

Congratulations! You have now written your first macro. The logical next step is to check out the code that was generated by the macro. The code generated by the application is in a language called VBA (Visual Basic for Applications). To view the code, we will need to open the VBA Editor (also referred to as the VBA IDE or VBA Integrated Development Environment). You can use the shortcut key Alt+F11 to open up the IDE or simply use the following menu options.

Editing a macro in Excel 2010 and 2007

You can turn on the “Developer” tab by clicking on the Office button located at the top left corner of the Excel workbook. Click on the “Excel Options” button and then turn on “Show developer tab in the Ribbon” checkbox the under the “Popular” menu option.

Having done that, you can now use the “Developer” tab to access the VBA Editor by pressing the “Visual Basic” button.

Editing a macro in Excel 2003 and earlier versions
The visual basic editor can be accessed using he shortcut key Alt+F11, by using the visual basic editor button in the visual basic toolbar or by choosing the visual basic editor option from the menu as shown below.

You may want to keep just one workbook open to avoid confusion while opening the VBA editor.

Once you’ve opened up the VBA IDE, here’s how it would look like.

At this point, it is important to familiarize oneself with the various windows and menu options. A few minutes spent in understanding these can potentially save you a ton of effort later.

To view to code of the macro that you just recorded, click on the “Module” under the project explorer window and then double click on “Module1″. It would open up a window like the one shown below with a few lines of code.

This is the point now where you can begin to edit the code that the macro generated while you were recording it in Excel. For example, you have to automate a task where a certain column is populated with numbers starting from 1 to 10. You recorded the first three steps so that you now have the code that can enter the numbers 1, 2 and 3 in the first three cells of that column. You would now like to write the next seven steps.

If you looked at the code given above, you would have guessed that there is a structure to the code. The application first moves the cursor to the cell by using an instruction like Range(“A1″).Select and then editing its contents by something like ActiveCell.FormulaR1C1 = “1″. So for the remaining steps, we can simply repeat these steps by providing the address of the new cell and then in the next step providing the value that we would like to enter. For example if you wanted to enter 4 in cell A4, you would write:

and then repeat the step for the all the other values that you would like to enter.

Once you are done editing, save the workbook. You can play back this marco either by pressing F5 while still within the main body of the macro or going back to the excel workbook and using the “Run” button after selecting the required macro from the “Macro” list window shown above.

Please take a few minutes to carefully read through the code that the macro has generated. If you are a beginner, a few minutes invested in reading through the code and yield tremendous results and will go a long way on familiarizing you with commonly used objects in VBA. (Please bear in mind that this example is only for illustration. There are better and faster ways of achieving the same results, some of which we will cover in the sections below.)

Improving speed to execution of an Excel Macro

So far so good. Let’s move on to learning some tricks that can help you speed up the performance of your macro’s VBA code. Let’s take the example of the code snippet shown above. On a high-end computer the above code will probably run faster than you can click an eyelid but suppose we we had to run the same code over and over again for 50,000 cells (or iterations). That would take some time. If the macro that you wrote runs into hundreds of lines of code, you can improve the speed of execution by cutting down on all the other processes that are not required when the code is executing.

Using Application.ScreenUpdating command

The first trick is to stop Excel from updating the screen as the code executes. That helps Excel save all the processing power executing the code and update the screen with fresh value only after the code has ended executing. To achieve this, we can write a statement at the beginning of the code to turn off screen updation and turn it right back on at the end of the code.

The Application.ScreenUpdating command tells the application to forget about updating the screen with real-time results from the code and to refresh it only after the code has been fully executed.

Using Application.Calculation command

The second trick is to turn off automatic calculations. Let me explain. Whenever a user or a process updates a cell, excel tends to recalculate all the cells that depend upon that cell. So if say, 10,000 cells depend on the cell that your code is going to update, excel will recalculate all of them before the code completes executing. Now if there are a number of such cells, the recalculation can severely slow down code execution. To prevent this from happening, you can set the Application.Calculation parameter to manual at the beginning of the code and then right back on towards the end of the code.

Be careful to turn on this option back on at the end otherwise it will remain turned off even when the code has fully executed. If you let it turned off withing the code, you can change it to automatic by using the “Tools” -> “Options” -> “Calculation” tab from the menu as shown below:

Avoid Cell and Range Selection in VBA

When you record a macro, you will typically see lines of code like Range(“A1″).Select. In our example above, we have used such statements multiple times to select a particular cell and then changing its value. You can avoid doing this by simply assigning a value to the cell without selecting it. (The macro recorded your cursors movement from one cell to another and hence inserted these steps, however they are not necessary when working with VBA code). So in this case, a far more efficient code would be:

What we’ve done here is to simply reference a cell and then provide a value to it without selecting it at all. This is way faster than the previous one.

How to write loops in VBA

You can refer to some of the previous articles dealing with this. You can learn more about VBA For Loop here. For the Do While Loop please refer to this article.

How to fix runtime errors in VBA

Once you have learnt how to record and edit a macro and acquire the skills to write basic VBA code, it is important that you learn about error handling. Error handling prevents the application from crashing when an unexpected event occurs. Please read more about fixing runtime errors in VBA here.

Excel macro – Examples

Here are a few examples of VBA code that can help you automate some frequently repeated tasks.

Excel Macro to insert a row

Excel Macro to insert a column

Excel Macro to insert a column

Excel Macro to traverse a range

Here’s a macro to remove error values from a range in Excel. You can try playing around with the code.

Being able to write a macro in Excel is one of the most productive skills that you can pick up. It lets you automate routine tasks and allows you to complete them much faster.