Hi Excellers and welcome to another #macromonday. Today let’s look at a common question or query I get from website visitors and Newsletter subscribers. How do I use the Excel VBA code or Macro that I find on the Internet?.
Is There Useable Excel VBA Code On The Internet?
Yes!. There are tonnes of people and forums, newsgroups, websites out there where people post their code. I post here on this website for you all to copy and use. But, some of the code does not always come with instructions on how to use it. So let’s look at how to use that previous bit of Excel VBA code you find on the internet. So, let’s say you have found that golden piece of code that will save you hours and hours of work, yes Macros and VBA can be that beneficial to your Excel life.
Is It Safe To Use The VBA Code I found On the Internet?
Ok, let’s take a quick look at using code from the internet. One of the first things that probably comes to your mind is it safe?. Well in my experience I am always cautious about it. Let’s face it if Excel gives us many warnings and security settings around opening files with Macros then there is a possibility that they could contain viruses. So, always make back up copies of your Excel workbook you want to run the macro on. I always make two copies and one as the test workbook. In fact here are a couple of steps I always take if I see a cool piece of code I think useful.
- Copy your Excel workbook. Make a backup. Or take an extract of the data you want to test the code you have found and save it in another Excel workbook.
- Read the VBA Code. Does the code look like it is going to do what it says?. Take a good close look at the steps of the code and the annotations to see if it looks like it going to do what you expect it to do. With experience, this step gets easier and you can get a good feel for what the code will do.
The final step is to copy the code into your workbook. But just where do you put that VBA code you have copied?.
Where Do I Past The Code Or Macro That I Have Found On The Internet?
Some coders will give great instruction on where and how to use their code. For example, you may find that they state the following.
- Paste the code into a General, Regular, Normal or Standard module. This means that you need to insert a Module before you can paste the code. Normal macros are stored in these modules as well as User Defined Functions (UDF’s) and global variables.
Insert a module by opening Visual Basic by hitting ALT+F11, or select the Developer Tab and in the Code Group Select Visual Basic. You can now hit Insert Module. You can now double-click on the module and paste the code into it.
After you paste the code you can use the Excel shortcut ALT+Q to return back to the normal Excel screen You can then use ALT +F8 to select the Macro and run it.
- Paste the code into ThisWorkbook module. You can double click or right click on ThisWorkbook option in the Project Explorer Window.
As in the example above, after you paste the code you can use the Excel shortcut ALT+Q to return back to the normal Excel screen You can then use ALT +F8 to select the Macro and run it.
- Paste the code into a Sheet module. You can double click or right click on the sheet that you want to paste the code into.
Once you have pasted your VBA code you can use the Excel shortcut ALT+Q to return back to the normal Excel screen You can then use ALT +F8 to select the Macro and run it.
What Next?.
So that is how to make the most of the code that you copy from the internet. Why don’t you go grab some code for a repetitive task that you carry out on a regular basis like formatting cells or deleting specific columns? I have some really useful Excel Macros for you to try. You can check out my full list of Macro Mondays blog posts, and the code is always free for you to copy at the end of the blog post.
If you want more Excel and VBA tips then sign up for my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.