How To Use Macros You Find Online In 6 Easy Steps

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 […]

Chat with SearchBot

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.

VBE interface

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
        End If
    Next
End Sub

 

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.

Add a macro to a workbook

Click for a larger image

 

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!

Side Notes

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.

creating personal.xlsb file in Excel

 

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.

assign a macro to an image in Excel

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 this article are those of the guest author and not necessarily Search Engine Land. Staff authors are listed here.


About the author

Annie Cushing
Contributor
I'm a Senior SEO at SEER Interactive, an Internet marketing agency in Philadelphia. My areas of focus are SEO, analytics, and data collection and analysis. My claim to fame is I make data sexy — and teach other marketers how to do the same.

Get the must-read newsletter for search marketers.