How To Use VBA Code From The Internet

So you’ve found some VBA code on the ‘net that will save you hours and hours of work, but how do you get it into Excel and use it?

Often I’ve seen snippets of code posted on sites that won’t work unless you know where to put them in Excel, and how to actually get Excel to understand that it’s some VBA code you want to use.

You might see code like this posted on a site :

Worksheets("Sheet1").Name = "New Name"

Which if you put this into the VBA editor will do nothing as it should look something like this :

Sub RenameSheet

Worksheets("Sheet1").Name = "New Name"

End Sub

I've added the Sub and End Sub statements around my code, and given the Sub a name which in this case is RenameSheet.

Sub means subroutine, you'll variously hear VBA code referred to as Sub, Subroutine, macro, or VBA depending on what you are reading. VBA also has functions but we won't deal with that here.

But wait, what’s the VBA editor and how do I enter VBA code into it so that Excel can use it?

Firstly, I want to point out that we're just dealing with code that we want to run in regular modules, we won’t get into VBA events as these are dealt with differently.

Open the VBA Editor

With Excel open, press ALT+F11 to open the VBA editor.  On the left of the window is the Project Explorer pane (highlighted in red), which is where you can see the workbooks you have open.  If you can’t see the Project Explorer press CTRL+R.

Creating a Code Module

You need to create a module in order to paste your code into it.  Of course you can use an existing module if you have one, but if not, create one. To do this either :

1. Go to the Insert menu and click on Module 

   

2. Right click on the project name and then select Insert -> Module 

   

Either way you will end up with a new module, which in my example is called Module1.  Yours will have the same name if it’s the first module you’ve added to your workbook.  On the right hand side of the VBA editor window,  the code pane for that module is made active.  The code pane is highlighted in red below.

You can also make the code of any module active by double clicking that module’s name.

OK so we have  a new module.  All you need to do is copy and paste the code into that module.  Let’s use the same code I’ve already used as an example above.

Sub RenameSheet

Worksheets("Sheet1").Name = "New Name"

End Sub

What this macro will do is rename the sheet with the name Sheet1 to New Name.

If you have started with a new workbook you should have a sheet called Sheet1, if not then this macro won’t work.

You can either create/rename a sheet to Sheet1, or you can change the macro so that Sheet1 is replaced with the name of a sheet in your current workbook.

Run The Macro

F5

All we need to do now is run the macro.  As always there’s several ways to do this.  The easiest is to press F5 when you are in the VBA editor – but make sure your cursor is somewhere between the Sub and End Sub statements like this :

If the cursor is outside the macro you want to run, Excel will show you the list of macros available so you can choose which one to run.

Run Button

Click on the Run button on the menu in the VBA editor.  As with pressing F5, if your cursor isn’t in the Sub, Excel will display the list of macros so you can choose which one to run.  If your cursor is within a Sub, that’s the one that is run.

ALT+F8 : Your List of Macros

Pressing ALT+F8 when you are in Excel brings up the list of macros you can run.  Double click the one you want to run, or click on it once then click on Run.

Run the Macro from the Developer Tab in Excel

Click on the Developer tab, then click on macros.  This is the same as pressing ALT+F8 and brings up your list of macros.  Choose the one you want and run it.

If you don’t have the Developer tab activated then you can find out how to enable it here.  It takes 2 minutes to do.

Assign the Macro to a Shape or a Shortcut Key Sequence

You can insert a shape into your sheet and assign a macro to it. Click on that shape and your macro executes.

You can also assign a key sequence that will run your macro e.g. CTRL+SHIFT+X

How to do these things are covered in detail in this post

Run The Macro From The Ribbon or Quick Access Toolbar

You can customize the Ribbon and QAT to run your macros. It's easy to do, just follow the steps here to customize your Ribbon and here to customize your QAT.

We’re Done

Well, actually we’re not, yet.  If you’ve created a new workbook, by default it will be saved as a .xlsx file.  But these cannot contain macros.

You will need to save your file as a .xlsm, a macro Enabled Workbook.  When you save the file just change the Save as type as shown here :

OK, now we’re done 🙂

Try It Out

Grab some code from other VBA posts that I’ve written and try this out for yourself.