Writing excel macros- the simple way

MS Excel is a great tool to use in day to day data analysis. The excel functions and other nice features will do a handy job for you.  But if you know a little bit of writing excel macros then you can add immense power to your excel work sheets. By writing excel macros you can build solutions for two problems. One is you can avoid repetitive tasks. That is if you have to perform the same procedure repeatedly for different sets of data you can create a macro for that.  The other is the situation where the excel functions are incapable of doing what you want. Then you have to consider writing excel macros as a solution.  Writing excel macros is done by using a tool called VBA (Visual Basic Applications).

As an example let’s now see how we can write a macro to perform the following task.

Assume that you want to compare two cells at a time and check whether they are equal. If they are equal you want to highlight them.  This has to be done for multiple cells in a single row and for multiple rows.

1. Add the Developer tab to Excel Environment

• Go to Office Button and select Excel Options and then Popular from the left hand side pane
• Click Show Developer tab in the ribbon in the right hand side pane
• Click OK
• The developer tab will appear in Excel environment

2. Open Microsoft Visual Basic Editor

Select the Developer Tab in the ribbon and click Record Macro. Give a name (I gave the name Calc) and assign a shortcut key. The shortcut key will act as a keyboard shortcut to run the macro. Now click on Visual Basic. Following editor will be displayed.

3. Write code

Now we have to write VB code . The code written inside calc() procedure will do what we need to perform, comparing two adjacent cells and highlights the cells if adjacent cells have the value 1.

This is the exact code written there

Sub calc()

    Dim c

    Worksheets("sheet1").Activate

    For i = 4 To 8

        For j = 2 To 15

                 Cells(i, j).Activate

                ActiveCell.Interior.ColorIndex = 0

        Next j

    Next i

    For i = 4 To 8

        For j = 2 To 15

            If Cells(i, j) = Cells(i, j + 1) And Cells(i, j) = 1 Then

                c = i & j

                Cells(i, j).Activate

                ActiveCell.Interior.ColorIndex = 3

                Cells(i, j + 1).Activate

                ActiveCell.Interior.ColorIndex = 3

            End If

        Next j

    Next i

End Sub

4. Run the macro

Now the macro is created and you can run it. Go to the developer tab and select Macros. Select the macro you created and run it. You will see the following output.

So what you have to know is only VBA. It is very easy to learn and if you know Visual Basic already you know it already. If you need any other reference material following guides will be very useful.