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.


The task to perform - Check the validity of the roster
The task to perform - Check the validity of the roster

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

Source

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.

Good books to learn VBA for Excel

More by this Author


Comments 3 comments

ImChemist profile image

ImChemist 5 years ago

Its really very informative hub , i rated it useful, thanks for sharing it.


ImChemist profile image

ImChemist 5 years ago

Thanks and stop at my hubs


IndikaT profile image

IndikaT 5 years ago from Kandy,Sri Lanka Author

Thanks for commenting, glad that you found it useful!

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working