Bottom Line: Learn how to insert VBA code into your worksheets using four different techniques.
Skill Level: Beginner
Download the Basic File
In Option 4 below, I'll show you how to import a basic file (.bas) into your workbook. The file I use in the example can be found here:
M_TOC.bas (2.3 KB)
Copying or Importing VBA Code
If you're relatively new to Macros and VBA, then you might want to use code from other sources. This could be from previous projects you created, code you found on the web, or a macro from your own personal macro workbook.
Getting VBA code into your workbooks can be done in four different ways. All of these are are quick and easy to implement.
Option 1 – Copy and Paste VBA Code
The first option is simply to copy the code from whatever source you are taking it from and then paste it into the VB Editor.
- Copy the source code using the Ctrl+C keyboard shortcut (or the right-click menu). In the example below, I've copied some code from this Excel Campus post.
- In Excel, access the VB editor by clicking on the Visual Basic button on the Developer tab. Or use the keyboard shortcut Alt+F11.
- In the Project Pane of the VB Editor, select the workbook you want the code to be in. Then, if there is not already a code module listed under that sheet, you can insert one by choosing the Insert option, and then Module. (The keyboard shortcut to do this is ALT+I+M.)
- Once the module is added, you can double-click on that new Module1 project to open the code pane if it isn't already open. That's where you are going to paste the code that you copied in Step 1.
Saving Your Workbook as a Macro-Enabled File
Any time you are placing a macro into an Excel file that doesn't already have them, it's important that you save the file as a macro-enabled workbook (.xlsm). When you go to save your workbook, you may receive this prompt, asking you if you want to ignore the code and save it as a macro-free workbook. You want to select No.
Then you want to change the file type to be an Excel Macro-Enabled Workbook (.xlsm). Once you have selected that option from the drop-down menu for the file type, just click Save.
If you do not save your file as a macro-enabled workbook, then the VB code you copied will not be able to run.
Option 2 – Drag and Drop a Module
Another way to insert code into a workbook is to drag and drop a code module from one book to another.
- Open both your source workbook and your destination workbook.
- From any Excel file, access the VB editor (using the Visual Basic button on the Developer tab, or Alt+F11).
- Left-click on the module you want to copy, but don't release your hold until you've dragged the module to the destination file.
- The file will not be moved, but copied to the new folder. You'll be able to see it listed in both workbooks.
This drag and drop method can be used for Userform and Class modules as well.
Removing a Code Module
Perhaps you dropped your file prematurely while you were dragging it and the module ended up in the wrong workbook. If you want to remove a module, the process is simple. Just right-click on the module you want to remove and choose the option for removing it.
Option 3 – Copy Sheet Modules That Contain Code
Unfortunately, the drag and drop process above does not work for Sheet modules. However, the process to move or copy sheets (and the code that comes with them) is also very simple.
- Open both the source workbook and destination workbook.
- Starting on the sheet that you want to make a copy of, right-click on the name tab at the bottom. Then select “Move or Copy…”.
- In the Move or Copy Window you want to (1) select the checkbox to create a copy, (2) select your destination file, (3) indicate where you want the sheet to go in the new workbook, and (4) click OK.
The sheet (and the code that comes with it) is now copied to the destination workbook.
In the example above I'm copying a sheet that has code to make it automatically create or update a table of contents whenever changes are made to the file. I explain more about this technique in my video on how to create a table of contents that automatically updates.
Option 4 – Export & Import Modules
Finally, you can export and import modules instead of just copying them. It's a good option for sharing modules with other people. It allows you to save them to a folder on your computer for future use as well.
- In the VB Editor, right-click on the module you want to export. Then choose Export File….
- You can save it to any location you choose. The module will save as a basic file (.bas extension).
- When you are ready to import it to another workbook, just right-click on the workbook you want to place it in and choose Import File….
Importing code modules or macros is as easy as that!
You can use any of these options to copy code to your personal macro workbook as well. If you haven't yet set up a personal macro workbook, I explain how you can do it and why it's a good idea in this post & video series: How to Create a Personal Macro Workbook.
Checkout this post on VBA Code Modules & How to Run Macros Based on User Events to learn more about the different types of code modules.
I hope these four options help you to be able to better integrate macros and modules into your Excel files. If you have any questions about them, please let me know by leaving a comment below.
Free Webinar on Macros & VBA
If you are interested in learning more about macros, I'm currently running my free webinar called “The 7 Steps to Getting Started with Macros & VBA”. It's running all this week, and it's absolutely free to register.
During the webinar I explain why you might want to learn VBA, and a lot of the basic coding concepts that will help you get started. I jump into Excel and the VB Editor and walk through how to write and run our first macro. Even if you have been using VBA for awhile, I'm sure you will learn some new tips.