How to write a MS Excel Macro

Microsoft Excel is a very handy tool many of us use for our daily calculations. It can be used in simple calculations to advanced data processing. Anybody who wants to master excel, should know how to write an Excel Macro. Basically Excel macros can be used in two different situations.

1.You can write an Excel macro to automate repetitive task

If you happen to perform same set of calculations, formatting over and over again on different sets of data you can think of writing an excel macro to automate that process.

Consider the following example

For the above worksheet the data is entered after completion of an exam. Then the total, average marks and the final grade of each student should be calculated. At the same time the grade column should be conditionally format to highlight the failed students. All the above calculations should be repeated when marks for a new exam is entered. This is the unnecessary repetition and we can avoid it by writing an Excel macro.

2. To achieve some advanced functionality which is impossible using the standard functions in Excel, we can write an Excel macro.

Consider the following situation

Here the requirement is to check whether the employees work according to their work agreement. The agreement does not permit to work in two consecutive shifts. How can you highlight the violation of the agreement? You can’t use the excel functions available to accomplish this situation. The answer is to write an Excel macro.

In this article let’s see how we can record a macro to avoid the repetitive tasks.

All macro related options are available in Developer tab of Excel 2007. To add the developer tab to the ribbon click Office Button ->Excel Options and select Show Developer tab in the Ribbon.

Now the Developer tab should appear on the Ribbon.

Record the macro

Now you can record your macro. Here what you will be doing is performing the actions that you want to perform on the data that you have entered. They may be formatting cells, conditionally formatting or calculations. You have to record them in the exact order in which they should run when the macro is run.

To record the macro click on Record Macro in the Developer tab. Then you will be asked to give a name to your macro and a shortcut key. The short cut key can be used to run the macro lator.

Recording the macro

 Now whatever you will be doing is recorded to the macro. Once you have completed recording click Stop Recording Macro to stop recording.

Run the Macro

Now everything is Ok and you can test your macro in action. Click on Macros in the Code group of Developer tab. All the macros that are available will be displayed. Select the macro you recorded just before and click Run. The macro will run and all the formatting and calculations will be done at once! That is so easy