ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

Creating a Macro in Excel

Updated on February 28, 2014
Excel is Cool!
Excel is Cool! | Source

Microsoft Excel is used for many different applications including financial reporting, tracking of personal finances, keeping track of personal assets, grade keeping, loan calculator, and to run complicated calculations. I spend most of my day at work in Excel. As an accountant, I find Excel to be an invaluable resource that makes my life easier. One of the things that I love about it is that you can you can automate repetitive tasks by the use of macros.

What is a Macro?

A macro is a task or a series of tasks that are performed all together. This includes any key presses or mouse clicks. I use macros as much as possible. Here are a few examples of how you can use a macro:

· Hide or unhide workbook tabs

· Reset a combo box to a desired selection

· Open an Excel file

· Protect or unprotect a worksheet

· Formatting

· Insert a formula (Works best with simple formulas that do not use cell references)

Turn on the Developers Tab
Turn on the Developers Tab | Source

Enabling Macros

If you are using Microsoft Excel 2007, you will have to enable the “Developer” tab by clicking on the Microsoft Office button (Top left hand corner of the screen) and then select Excel Options at the bottom of the drop down menu. This will bring up a popup window where we need to activate the “Developer” tab. Start by clicking on “Popular” (left-hand side) and then find where it says, “Top options for working with Excel” and check the box next to “Show Developer tab in the Ribbon,” which is the third option down from the top. Finally, click on OK to close the window.

Macro Security Popup
Macro Security Popup | Source

Changing Macro Security

In the newer versions of Excel, macros are disenabled for security purposes. Do not let this alarm you. Macros can have dangerous code in them; however, if you only use spreadsheets that you create, you will not have an issue. I always keep my macros enabled and I have never had a problem. If you are worried, you can enable and disable macros as often as you wish. To enable macros, go to the “Developers” tab and click on the “Macro Security” button. This will bring up the macro security dialogue box. Change the selection to enable all macros.

Click thumbnail to view full-size
Developer TabMacro Popup box
Developer Tab
Developer Tab | Source
Macro Popup box
Macro Popup box | Source

How to Create Macros in Excel

Creating a macro in Excel is easy and does not require any advanced Excel training. Before actually starting to create a macro, it is important to spend a few minutes thinking of exactly what you want your macro to do and the order that you want it to do it. For example, the financial reporting spreadsheet that I prepare every month at work has a navigation tab along with over 100 tabs of financial data. I created a macro that causes the spreadsheet to open up on the table of contents tab and it resets the combo boxes so that they start out blank.

Once you have decided what you are going to do, follow these simple steps below:

  • Save your current workbook as a macro enabled workbook or otherwise your macros will not work.
  • On the “Develop” tab click on the “Record Macro” button, this will bring up a macro popup box.
  • Enter a name for your macro. There are no spaces allowed in the macro name. My advice is to use a descriptive name that is easy to remember when you go to run the macro. In the example of the financial reporting spreadsheet, I named my macro “autoexec”. I will get into why I choose this name under the “Auto Executing” section below.
  • Select where you want to store the macro. There are three options to choose from: Personal Macro Workbook, current workbook, or a new workbook. If you are just planning to use the macro in the current workbook, select that as your selection. If you are planning to use it in other workbooks, store the macro in your personal macro workbook. In my example of the financial reporting spreadsheet, I saved my macro in that workbook only.
  • Click OK and the macro will begin recording. Anything you do past this point will be included in your macro. Go slow and take your time with each step to make sure it is exactly how you planned to do it. Once you are done, click the macro stop button.

Creating macros in Excel is just that easy. If you make a mistake that will not work in your macro, go ahead, stop the macro, and start over. Make sure to use the same macro name that you did the first time.

Great Resource of Ready-To-Use Macros

Running a Macro

Once you have finished creating your macro it is time to run it and see how it works. The easiest way to run a macro is on the “Developer” tab. Click on the “Macro” button, which will bring up the macro dialogue box. Select the name of the macro that you want to run and then click on the “Run Macro” button. Your macro will now run.

Assigning a Command Button

Assigning a command button is a great idea if you plan to run a macro repeatedly. On the “Developer” tab in Excel, click on the “Insert” button which will bring up the Form Controls menu. Click on the top-left button and then draw your button in your workbook. As soon as you let go of the mouse, a box will popup asking you to assign a macro. Select the macro that you want and hit enter. Do not click off the button yet. Highlight the name of the button and change it to describe your macro. Also, if you want to change the text color or formatting, highlight the name and right-click on the button. Select “Format Control. . .” from the popup menu. Once you are finished, click OK and you are ready to use your button to run your macro. You can have as many buttons that you like in a workbook.

Auto Executing a Macro in Excel

Sometimes you may want a macro to run automatically when a spreadsheet opens. It works great for my financial reporting spreadsheet to make it open to the Table of Contents tab. All that you have to do to make your macro execute automatically is to name it “autoexec”. It is vital that you save this macro to the current workbook and not to your personal macro workbook. If you do the latter, the macro will run every time that you open Microsoft Excel. Also, you may only have one macro that automatically executes per workbook. If you want your macro to do more, you have two options. If you are comfortable working in the VBA editor, you can create a second macro and then copy that logic into the auto executing macro. If you are not comfortable with that, you can always redo your macro and give it the same name (“autoexec”).

Recording a macro in Excel is easy and is a great time saver whether you are an expert in Excel or a novice. It is important to take your time while creating your macro to make sure that everything runs the way you want it.

Comments

    0 of 8192 characters used
    Post Comment

    • zodaic profile image

      Pisces 

      5 years ago from Pakistan

      Nice and creative hub

    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)