How To Use A VBA Macro You Found On The Internet
You Finally Found It!
Yes, I’ve been there! You’ve spent days scouring the internet to find a solution to a really cool capability you had dreamt up in Excel.
First problem: Your solution involves something called VBA code (aka a macro).
Second Problem: Your not a computer nerd! You don’t know the first thing about coding and quite frankly that stuff scares the hell out of you.
Am I striking a chord? If your answer is yes, then you are in the right place. I’m going to share with you the steps to just get that code into your Excel file and running. We aren’t going to do any coding, just copy/pasting. Think you can handle that? Let’s do it!
What Are You Trying To Do?
Before we actually add the code, we first need to plan for how you are wanting this code to be triggered. There are two main triggers that are used to run macro code (click link to jump to it):
- User runs code “on demand” (ie click a button) – This is the most common
- Code runs automatically based on users action (ie selecting a tab, changing a cell value, etc…)
Once you know how you want to trigger your macro to best suite your situation, go to the respective section below to see how to implement your code.
Manually Trigger The Code
I’m going to start off with this method as 99% of your code will most likely be triggered by your or the spreadsheet user on demand.
Step 1: Re-Saving File
Microsoft has created a separate file extension to store VBA code. You will need to save your file as a .xlsm file rather than a .xlsx file.
Step 2: Open Visual Basic Editor
First, let’s open up the Visual Basic Editor by using the keyboard shortcut alt + F11 (while inside Excel). The Visual Basic Editor window is where you will actually past in your code. Make sure the Project Explorer pane is showing to the left (if not appearing use shortcut ctrl + r to make it appear)
Step 3: Create A New Code Module
Next, you will need to create a place within your Excel file to store the VBA code. This storage place is called a Module. In order to create a new one, right click on the VBAProject line associated with your Excel file (in this example, the file is called Book1) and select Insert >> Module.
You should see a new module appear in the Project Explorer (most likely named Module1).
Step 4: Paste Your Code In
Ok, now it’s time to paste in that VBA code you have found to solve all your problems. First, make sure you have Module1 selected (it’s name will appear at the top of your window in the Title Bar).
Before you paste in your code, you may need to create a macro name to place your code inside. To do this, type the word “Sub” (short for subroutine) and then give your macro a name. This name must be unique to any other macro you may have in the Excel file and cannot have any spaces. Finally type “()” after your macro name. After you have done this, you can hit your Enter key and the phrase “End Sub” should automatically appear.
That’s it, you have now technically created a macro! Now to get this macro doing some work for you.
Next, you will want to paste your code in between your “Sub” line and your “End Sub” line of code.
Now, assuming your code was perfectly written when you copied it, there should be nothing else you need to do. Make sure your code is referencing the proper cells or worksheets that you intend it to modify.
I do recommend adding a description comment in line 2 of your code to remind yourself what this code does. To tell your code you want to add a comment, simply type a single quote and every word after the quotation mark will be ignored and turned green. You can see a description comment added below:
You can also have multiple macros stored within a Module. If your automation requires multiple macros, you can simply copy/paste your code below your first macro. You can also insert a new module and add more macros into Module2 if wanted.
Once you are finished, you can save your code by hitting the Save button in the toolbar or using the keyboard shortcut ctrl + s. Let’s exit out of the Visual Basic Editor now.
Step 4: Create A Visual Trigger
Finally, we’ll want to make an easy way to trigger the macro. I always recommend creating a button out of an Excel shape. Below is a quick GIF showing some basic steps on how to create a basic button shape if you are unfamilar with the concept.
After your button is created and formatted to your liking, you will need to assign your macro code to it. To do this simply right-click on the shape and select Assign Macro. You should now see the Assign Macro dialog. I recommend filtering down to Macros in “This Workbook” and then you can click any one of the macros that you’ve added to the Excel file. After you have made your selection and clicked OK, the assigned macro will execute every time you or your users click on the button.
Congratulations, You Now Have A Working Macro!
And there you you go! You’ve now taken that code you found on the internet and added it into an Excel file for easy use. All without having to learn any coding. It wasn’t that bad, was it? Now hopefully, as you get more experience using macros, you’ll get more interested in teaching yourself how to write it from scratch. It’s actually one of the easiest languages to code in for beginners and would you believe it if I told you I was in your very shoes as a Finance guy with now computer coding education whatsoever?! My curiosity got the best of me over the years and I taught myself to code VBA and now I’m teaching others around the world….it’s a crazy world we live in!
Code Runs When An Event Occurs
If you need some automation to run every time you or your users do something inside Excel, you are going to want to paste in your VBA code in a special location within the spreadsheet. Please note, that this will only work if your user accepts to run macro code when they use your file.
Step 1: Re-Saving File
Microsoft has created a separate file extension to store VBA code. You will need to save your file as a .xlsm file rather than a .xlsx file.
Step 2: Open Visual Basic Editor
Next, let’s open up the Visual Basic Editor by using the keyboard shortcut alt + F11 (while inside Excel). The Visual Basic Editor window is where you will actually past in your code. Make sure the Project Explorer pane is showing to the left (if not appearing use shortcut ctrl + r to make it appear)
Step 3: Select A Microsoft Excel Object
Before you can paste in that VBA code you will need to find a location to store it. Any VBA code that needs to be triggered by an event must be stored in one of the Microsoft Excel Objects. There is an object automatically created for each worksheet in your file and also for the file itself (called ThisWorkbook).
In this example, we are going to have some code run when the Excel file first opens. Since this is a file-level event and not isolated to a specific worksheet, we will want to store our code inside the ThisWorkbook object. To open it, double-click the object named ThisWorkbook inside the Project Explorer pane (it’s name will appear at the top of your window in the Title Bar).
Step 4: Create Your Event Trigger
There are a number of different events available to us inside the Microsoft Excel Objects. In order to select an event trigger, you will need to make some selections with the two drop down menus above the coding area.
In the first drop down menu, select the the type of object (either Workbook or Worksheet). Next, within the second drop down menu, you will see a list of events that you can select from. Most of these events are pretty self-explanatory via the event name. For example SheetActivate runs your code once a different spreadsheet tab is selected inside the file. Or BeforePrint would run your VBA code prior to the user printing the spreadsheet. If you don’t know what a specific event does, like “Sync” (I have know idea what that does), just Google it!
For this example, let’s choose the Open trigger. Once you select a trigger name, a subroutine will pre-populate in your coding area. In this case “Private Sub Workbook_Open” and “End Sub” will be written for you.
Step 5: Paste Your Code In
Finally, copy your code and paste it in between the Private Sub and the End sub lines of code.
I do recommend adding a description comment in line 2 of your code to remind yourself what this code does. To tell your code you want to add a comment, simply type a single quote and every word after the quotation mark will be ignored and turned green. You can see a description comment added below in green:
Once you are finished, you can save your code by hitting the Save button in the toolbar or using the keyboard shortcut ctrl + s. Let’s exit out of the Visual Basic Editor now. Make sure to test your code to ensure your code is being triggered as expected. In our example’s case, I would close the workbook and reopen it, expecting a message box greeting me saying, “Hello Chris”.
Congratulations, You Now Have A Working Macro!
And there you you go! You’ve now taken that code you found on the internet and added it into an Excel file for easy use. All without having to learn any coding. It wasn’t that bad, was it? Now hopefully, as you get more experience using macros, you’ll get more interested in teaching yourself how to write it from scratch. It’s actually one of the easiest languages to code in for beginners and would you believe it if I told you I was in your very shoes as a Finance guy with now computer coding education whatsoever?! My curiosity got the best of me over the years and I taught myself to code VBA and now I’m teaching others around the world….it’s a crazy world we live in!
After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!
Keep Learning
![Learn VBA Macro Coding Basics In Excel [2023 Guide]](https://www.thespreadsheetguru.com/wp-content/uploads/2022/10/IntrotoVBAMacros.png)
![Quickly Learn Excel’s Power Query Tool [Guide 2022]](https://www.thespreadsheetguru.com/wp-content/uploads/2022/12/PowerQueryGuide.png)
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.