So you’ve found some VBA code on the internet 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 :
Which if you put this into the VBA editor will do nothing as it should look something like this :
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 :
- Go to the Insert menu and click on Module
- 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.
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
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.
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.
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
Run The Macro From The Ribbon or Quick Access Toolbar
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 ?
If you are new to VB Programming and wish to do VB coding with ease to automate any of your Excel Reports or processes – Click here
Happy Excelling – Team Excelgoodies – www.Excelgoodies.com