Excel Zoom

...because it's more than just a calculator


Install and Use Macros

by 1 Comment

So you’ve found the perfect macro code to speed up that tedious and repetitive task – now what? In this section, we’ll walk you through the several ways to install and use your macro code. We’ll show you how to get comfortable with macros in excel before moving on to move on to perform more complex tasks. Once you know how to use macros in Excel you will have the ability to easily increase speed and output by automating manual tasks quickly with VBA code.

First, before we install a macro in excel, please make a back up copy of your file. There are some macros that can’t easily be undone once they are run. It is always important to have a back-up copy of your work incase something goes unexpectedly wrong. Now thats out of the way, we can look at how to set up and install macros in excel.

How to Record a Macro in Excel:

The first, and easiest way is to let Excel do the work for you. In earlier versions of Excel (pre Excel 2007), click ‘Tools’ -> ‘Macro’ -> ‘Record New Macro’. To do macros in Excel 2007, click the ‘Developer’ menu then ‘Record New Macro’. Enter in the appropriate information in the Record Macro dialog box (macro name, shortcut key (if desired), location to store the macro, and description (if desired)), then click ‘OK’. Excel will record every command and keystroke you make until you click the ‘Stop Recording’ button. There’s no need to enter any additional information, Excel has everything it needs to run your macro!

If you found the perfect macro to use online, you’ll need to “install” it before using it. There are several ways to do this and which one you choose depends on how you are planning to use the macro. Check out our available Excel Downloads where you can download various macros for excel. These are all sure to make your life easier and increase productivity in the office.

How to Set Up Macros in Excel

Single Workbook Use:

If you only want the macro to run in one particular workbook, and don’t want it available every-time you open Excel, this is where you want to be.

  • Open up the workbook you wish to use the macro in. This can be a new or existing workbook, it doesn’t matter. For illustrative purposes these instructions will assume you’re using a new workbook, called “Book1”
  • In pre-Excel 2007, click ‘Tools’ -> ‘Macro’ -> ‘Visual Basic Editor’ or simply press ‘Alt+F11’. In Excel 2007, click the ‘Developer’ menu then ‘Visual Basic’.
  • A new window should have popped up that looks nothing like Excel. Don’t be alarmed this is what we want. In the left hand pane you should see a tree that says something along the lines of “VBAProject (YourExcelFileName)”, obviously replacing YourExcelFileName with, well, your Excel file’s name. Mine says “VBAProject (Book1)”. Under that there should be a folder, called “Microsoft Excel Objects” that lists all the worksheets in your file.
  • Right click on ‘VBAProject (YourExcelFileName)’ -> ‘Insert’ -> ‘Module’. This should create a second folder under “VBAProject (YourExcelFileName)”, called “Modules”. You’ll also notice a blank screen over in the right hand pane. This is where we’ll be pasting in your macro code. Note: If you already have a second folder called “Modules”, you can skip this step, and just double click on “Module1” in the folder.
  • Now, go back to the wonderful time saving macro you found on ExcelZoom.com, click in the text area, which automatically selects all the code for you (cool isn’t it), and copy it (Ctrl+C, or right click and Copy).
  • Go back to your Visual Basic Editor (that thing you opened that doesn’t look like Excel). Right click in the blank right hand pane and paste in your code. It’s ok if you already have macros there and this pane isn’t blank, simply scroll down to the bottom and paste the code after your last macro.
  • Now, close the Visual Basic window, you’re done there. Go back to your workbook and click ‘Tools’ -> ‘Macro’ -> ‘Macros’ in pre-Excel 2007, or simply press ‘Alt+F8’. In Excel 2007, go to the ‘Developer’ menu and click ‘Macros’.
  • A list of all available macros should appear, including your recently installed macro. Select the macro in the menu you wish to run and click the ‘Run’ button. Then sit back, relax, go get a cup of coffee, take a nap, whatever you think you’ve earned with all that extra time you have!
  • It is also important to note that there are other ways to run your macros, which are covered below.

Make a Macro Available to All Workbooks Using PERSONAL.XLS:

Suppose you have a macro that you want to be able to use in any Excel file you open. No problem! Follow the steps below, and you’ll have access to your macros in any Excel file you open.

  • First, you’ll need to make sure that you have a Personal Macro workbook. It should be stored in the folder that Excel uses to open all files at startup in. To find which folder this is, click ‘Tools’ -> ‘Options’ -> ‘General’ tab, and find the file location labeled “At startup, open all files in:”. Mine is set to “C:Program FilesMicrosoft OfficeOffice10xlstart”, yours should be something similar, unless you’ve changed this. In any case, click ‘Cancel’ and go to the folder specified in that box.
  • If there is a file in that folder called “PERSONAL.XLS”, open it, otherwise, right click and select ‘New’ -> ‘Microsoft Excel Worksheet’. Call it PERSONAL.XLS and open it.
  • From here, the steps are the same as if you were planning on using the macro in a single workbook (above).
  • In pre-Excel 2007, click ‘Tools’ -> ‘Macro’ -> ‘Visual Basic Editor’ or simply press ‘Alt+F11’. In Excel 2007, click the ‘Developer’ menu then ‘Visual Basic’.
  • A new window should have popped up that looks nothing like Excel. Don’t be alarmed this is what we want. In the left hand pane you should see a tree that says something along the lines of “VBAProject (YourExcelFileName)”, obviously replacing YourExcelFileName with, well, your Excel file’s name. Mine says “VBAProject (PERSONAL.XLS)”. Under that there should be a folder, called “Microsoft Excel Objects” that lists all the worksheets in your file.
  • Right click on ‘VBAProject (YourExcelFileName)’ -> ‘Insert’ -> ‘Module’. This should create a second folder under “VBAProject (YourExcelFileName)”, called “Modules”. You’ll also notice a blank screen over in the right hand pane. This is where we’ll be pasting in your macro code. Note: If you already have a second folder called “Modules”, you can skip this step, and just double click on “Module1” in the folder.
  • Now, go back to the wonderful time saving macro you found on ExcelZoom.com, click in the text area, which automatically selects all the code for you (cool isn’t it), and copy it (Ctrl+C, or right click and Copy).
  • Go back to your Visual Basic Editor (that thing you opened that doesn’t look like Excel). Right click in the blank right hand pane and paste in your code. It’s ok if you already have macros there and this pane isn’t blank, simply scroll down to the bottom and paste the code after your last macro.
  • Now, close the Visual Basic window, you’re done there. Go back to the file called “PERSONAL.XLS”. This is where it gets a little different from the steps for a single file use above.
  • Click ‘Window’ -> ‘Hide’. Considering that this file will open everytime Excel opens, you want it to open in the background, where you won’t see it. If you ever wish to add/edit/delete a macro in PERSONAL.XLS, just click ‘Window’ -> ‘Unhide’ and select PERSONAL.XLS from the list.
  • Close Excel. If it prompts you to save PERSONAL.XLS, click yes to save it.
  • Open a new or existing Excel file. You should only see that file open up, not PERSONAL.XLS as it was set to open in the background.
  • Click ‘Tools’ -> ‘Macro’ -> ‘Macros’, or press ‘Alt-F8’. You should see a list of macros that were entered into PERSONAL.XLS. They will be “prefixed” by “PERSONAL.XLS!” then the macro name. They’re ready to use and be run in any workbook you open.
  • See below for ways to run your macros.

How to RUN a Macro in Excel

There are three ways to run your macros. 1) Using the ‘Tools’ menu; 2) Creating a toolbar button; 3) Assigning a keyboard shortcut to your macro.
The following assumes that you already have macros available to use. Details about Running your Macros using each method below.

1) Using the ‘Tools’ Menu:

  • Select the cell (or cells) you wish your macro to modify, if necessary.
  • Click ‘Tools’ -> ‘Macro’ -> ‘Macros’ in pre-Excel 2007, or simply press ‘Alt+F8’. In Excel 2007, go to the ‘Developer’ menu and click ‘Macros’.
  • Select the macro you wish to run.
  • Click ‘Run’.

2) Creating a Toolbar Button:

  • Click ‘Tools’ -> ‘Customize’ -> ‘Commands’ tab.
  • In the ‘Categories’ menu, select ‘Macros’.
  • Select ‘Custom Button’ and drag it to the toolbar position where you want it.
  • In the ‘Commands’ tab, select ‘Modify Selection’ -> ‘Assign Macro’.
  • Select the macro you wish to assign to that button and click ‘OK’.
  • You can modify the button, by selecting ‘Modify Selection’ again, click ‘Change Button Image’ and select a different image.
  • If you wish to add text to the button, select ‘Modify Selection’, change the description in the ‘Name’ box, and select either ‘Image and Text’ or ‘Text Only’ depending on which you prefer.
  • Play around with some of the other options to get the custom button exactly the way you want it.

3) Assigning a Keyboard Shortcut to Your Macro:

  • Click ‘Tools’ -> ‘Macro’ -> ‘Macros’ in pre-Excel 2007, or simply press ‘Alt+F8’. In Excel 2007, go to the ‘Developer’ menu and click ‘Macros’.
  • Select the macro you wish to assign a shortcut to and then click ‘Options’.
  • Type the key you wish to use with CTRL in order to run your macro.
  • IMPORTANT: Do not use a key that is already associated with other Microsoft functions (i.e. CTRL+A already is a shortcut to select all).

How to Open the VBA Editor

Once you have played around with creating and running macros in excel you will want to be accessing the VBA editor more and more often!. The keyboard shortcut to open the VBA editor in Excel is ALT + F11. If you are still only just getting started, check out our guide to opening Excel’s VBA editor.

If you are ready to move on, then be sure to check out our Introduction to VBA for MS Excel . This will help get you started on automating those tedious or repetitive Excel tasks that always bug you having to do manually.