ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

Writing excel macros- the simple way

Updated on August 4, 2012

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

Comments

    0 of 8192 characters used
    Post Comment

    • IndikaT profile imageAUTHOR

      IndikaT 

      7 years ago from Kandy,Sri Lanka

      Thanks for commenting, glad that you found it useful!

    • ImChemist profile image

      ImChemist 

      7 years ago

      Thanks and stop at my hubs

    • ImChemist profile image

      ImChemist 

      7 years ago

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

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://hubpages.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)