• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • SALE 20% Off All Courses
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Logout
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

How To Use VBA Code From The Internet

You are here: Home / Excel VBA / How To Use VBA Code From The Internet
How To Use VBA Code From The Internet
November 27, 2013 by Philip Treacy

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.

VBA Editor and Project Explorer

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 

    Insert VBA Code Module

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

    Right Click Insert VBA Code 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.

VBA Code Pane

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 :

Cursor in Code

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.

VBA Run Button

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.

Macro List

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.

Developer Tab

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 :

Save as type xlsm

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.

How To Use VBA Code From The Internet

More Excel VBA Posts

Display All Matches from Search in Userform ListBox

Display All Matches from Search in Userform ListBox

Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.
animating excel charts

Animating Excel Charts

Use animation correctly to enhance the story your data is telling. Don't animate your chart just for some eye candy. Sample code and workbook to download.
dynamic data validation lists in userforms

Dynamic Data Validation Lists in Userforms

Data validation lists using the same source that are dynamically modified to prevent the same choice being made in each list.
show report filter pages for power pivot pivottables

Show Report Filter Pages for Power Pivot PivotTables

PivotTables created from Power Pivot can't use the 'Show Report Filter Pages' option. But this piece of VBA allows you to do just that.
charting real time data in excel

Charting Real Time Data in Excel

Receive data in real time and chart the data as it arrives. Can be used to chart things like stock prices or sensor readings. Sample code and workbook
select multiple items from drop down data validation list

Select Multiple Items from Drop Down (Data Validation) List

Choose multiple items from a data validation (drop down) list and store them all in the same cell. Sample workbook with working VBA.
Excel Calendar (Date Picker) to Use in Worksheets and Userforms

Multi-Language Excel Calendar (Date Picker) for Worksheets and Userforms

Easy to use, highly customizable and multi-language. This date picker is implemented as a userform that is simple to integrate into your workbook.
automating and emailing pivot table reports

Automating and Emailing Pivot Table Reports

Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
search for data with userform

Searching for Data With a User Form

Search a list of records (like a table) using a user form, and then populate the fields of the search form when the record is found.
Checking values in range objects with vba

Checking Values in Range Objects With VBA

Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.




Category: Excel VBA
Previous Post:How To Enable the Developer Tab in ExcelHow To Enable the Developer Tab in Excel
Next Post:Excel Search a Cell for a List of WordsExcel Search a Cell for a List of Words

Reader Interactions

Comments

  1. Ashraf Doma

    November 27, 2013 at 3:25 pm

    I want to rename the sheet based on the value of the cell

    Reply
    • Philip Treacy

      November 27, 2013 at 3:33 pm

      Hi Ashraf,

      You could do this :

      Sub RenameSheet()
       
      ActiveSheet.Name = ActiveCell.Value
       
      End Sub
      

      Regards

      Phil

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Course Sale

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
trustpilot excellent rating
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.