Macros (Mac)

Why use Macros?

At its simplest, a macro is a recording of all of the commands and actions that you use to perform a complex task. A macro will let you repeat these tasks with a single click. Macros are stored as simple bits of code within Excel and they allow you to create complex functions, loop over entries in your spreadsheet, or quickly change formatting in your data. If you find yourself repeating the same set of tasks over and over again while dealing with a dataset, it can often be quicker to create a macro. One simple example of a macro is given here (for formatting text in cells), but macros can be used to format figures, clean datasets, or many other complex tasks. 

 

Getting Started

Before you can create your first macro, you need to activate the "Developer" ribbon in Excel. First you need to find the icon at the right most of the ribbon, and select Ribbon Preference. In the pop-up window, scoll down the panel and check the "Developer" box at the bottom and click OK. This will allow you to select the developer ribbon from the tabs at the top of the Excel window.

 

Recording Macros

First, look at the options in the "Visual Basic" section of the Developer ribbon.

Select a cell in Excel you want to manipulate. As an example, we will create a macro that changes the font and formatting of text in a cell.

To record your macro, click the Record button. This brings up a set of macro recording options. Here you can enter a name for your macro, a description for what it does, a keyboard shortcut, and specify where you would like to store it. Usually it is sufficient to store your macro in "This Workbook". This allows it to be used for the currently open spreadsheet. However, if it is something that you will use across many different datasets, you can save it either in a new workbook, or in a personal workbook that stores all of your macros on your computer.  

Once you have entered the name for you macro, click OK.This will bring you back to the spreadsheet. Perform whatever alterations to the dataset you wish to record. For our example, we will change the font, size, orientation (refresher on this here), and spacing of the text.

Once you are satisfied, return to the Developer ribbon and click the Record button again. This will save your macro. The action required navigating 4 separate menus in Excel using multiple clicks. We can now repeat it for any cell with one click or keyboard shortcut.

Note: earlier in the tutorial we learned about using "$" before a cell entry to make an absolute cell reference versus a relative reference. By default, when you record a macro and refer to a cell value, it records that as an absolute reference. This means that if you run your macro again in a different position, it will always refer to the cell in the macro. By selecting "Relative References" you will be able to make your macros run using values relative to the cells where you are running it. 

 

Running your Macro

Clicking the Macros button will bring up a list of all of the macros you have stored.

Here you will see the full list of macros associated with this workbook. Selecting any macro and clicking Run will run the macro in the selected cell. If you assigned a keyboard shortcut when you named your macro, then you can also use the keyboard shortcut to activate your macro. 

Macros are stored in a language called Visual Basic. Selecting Edit will allow you to edit the code for a macro. This can be a quick way to correct simple errors in a macro. If you wish to delete a macro, simply click Delete.

 

<< Back to Some Useful Statistical Functions

 

Return to Table of Contents

 

Questions, comments, concerns?
Send an email to the Empirical Reasoning Lab
Or drop by during the Lab's open hours