Have you ever done a search to find out how to do something in Excel, just to find the search results littered with macro options? I used to avoid those results like the plague because I found macros (at least those beyond what I could create with the Macros Recorder) really intimidating. And I’m writing this post because I’m assuming that I’m not the only one who has done that. Amiright?
So I’m going to show you how to take a macro you find online or get from a trusted source and add it to your workbook and run it — on both a PC and Mac.
If you want to follow along, you can download the Excel file I’ll be using in the demo.
Step 1: Enable Developer Tab
To work with macros, you’ll need to enable the Developer tab, which is hidden from the Ribbon by default.
To enable it on a PC, go to File > Options, then choose Customize Ribbon > Select Developer under Customize the Ribbon, and press OK.
For Mac, go to Excel > Preferences, then choose Ribbon > Customize, Select Developer, and press OK.
Note: To avoid redundancy, unless I state otherwise, we will be using the Developer tab, so I won’t include Developer in every set of instructions.
Step 2: Open The Visual Basic Editor (VBE)
Simply press Alt-F11 (Mac: Option-F11). Alternatively, choose Code > Visual Basic for PC and Visual Basic > Editor for Mac.
Step 3: Find A Macro
Find a macro from a trusted source that you want to run on a sheet of data. I’m going to use this macro that converts URLs to hyperlinks:
Public Sub Convert_To_Hyperlinks()
Dim Cell As Range
For Each Cell In Intersect(Selection, ActiveSheet.UsedRange)
If Cell <> "" Then
ActiveSheet.Hyperlinks.Add Cell, Cell.Value
One important thing to note is that the title of the macro is in the first line, after Sub, and is what you’ll look for when you go to run it. In this case the title is Convert_To_Hyperlinks.
Step 4: Decide The Scope For Your Macro
We’re just going to apply this macro to our workbook, but you could also make your macro global, if you wanted to always have it at your disposal, regardless of what workbook you’re working in. (This will make it global to whatever computer you’re working on, e.g., your laptop or your desktop.)
Step 5: Insert The Macro
To apply the macro to the sample workbook, right-click on VBAProject (links.xlsx), then choose Insert > Module. Shockingly, this process is the same for both PC and Mac. Then paste the macro into the module and press Alt-F11 (Mac: Opt-F11) again to close out of the editor and get back to your workbook.
Step 6: Run The Macro
To run the macro, in this example, select the URLs you want to convert into hyperlinks, and select Macros under the Developer tab. You should see the name of the macro. Select it, then click the Run button. Bada bing, bada bang, bada bada, boom!
Making the Macro Global: If you want to have the macro available to any future workbooks you open from the machine you’re working on, right-click VBAProject (Personal Macro Workbook) instead of the links.xlsx file. Your macro will then be available for any workbook you want to create active hyperlinks in (without having to use the HYPERLINK formula). You can read more about this from the Microsoft site.
Caveat: For some strange reason, in Excel 2010 the PERSONAL.XLSB file doesn’t show up automatically like it used to in 2007. If you don’t see it when you open the VBE, the easiest way to create it is to create a macro with the Macro Recorder and choose to store it in Personal Macro Workbook . (I literally just selected a few cells for my lame sandwich macro.)
Then press Alt-F11 to reopen the VBE. You should see it appear under whatever workbooks you have open. Then you can right-click and choose Insert > Module, as shown below.
Deleting Macros: If you want to delete a macro, click on Macros under the Developer tab, select the macro, and choose Delete. Alternatively, you can delete them by opening the VBE and deleting it from the Modules folder wherever you created the macro. (Excel creates this Modules folder automatically when the macro is created).
To delete it, right-click on it and choose Remove Module. Microsoft will give you the option to export it. If I’m deleting a macro, I don’t ever export it, but a case could be made for doing that, I suppose.
Assigning Macros: If you want to use the macro again and again, one alternative to clicking on Macros every time is to assign a macro to a shape or image. This turns the image into a clickable object that will run whatever macro you assign to it. And it couldn’t be easier to assign a macro. Just right-click on the image and choose Assign Macros. I did this once with a project management spreadsheet I created. When I’d click the refresh graphic I copied into the worksheet, the macro I created would run and move completed tasks to the bottom of the sheet.
Making a Macro Persistent: In the example I’ve been working from in this post, once you run the macro, you really don’t need it anymore. It’s your classic one-and-you’re -done kind of macro. You can save the file with the active links as a .xlsx file as normal.
However, if you want to use the macro whenever the workbook is open (let’s say you attach it to a graphic), you won’t be able to save it as a .xlsx file. Instead, choose File > Save As and select Excel Macro-Enabled Workbook (.xlsm) from the Format drop-down. When you open the file later though you’ll have to explicitly enable the macro or save your workbook to a safe folder.
Hopefully these tips will empower you to start using macros you find. They can reduce laborious tasks to seconds. Just make sure you trust the source you find the macro from because people with nefarious intentions also create macros that do horrible things to your computer.
As always, if you have any questions, feel free to ask them in the comments below or reach out to me on Twitter.
Opinions expressed in the article are those of the guest author and not necessarily Search Engine Land.