Search Contextures Sites

Contextures
Excel news
by email

We respect your email privacy

 

30 Excel Functions in 30 Days

 

 

Learn how to create Excel dashboards.

 

 

 

Time-saving
Pivot Table add-in

 

Adding Code to an Excel Workbook

NOTE: For the Excel 2003 version of this tutorial, please visit: Copy Code to Excel 2003 Workbook

Video: Copy Excel VBA Code to a Regular Module

To see the steps for pasting a macro into a workbook, and running the macro, please watch this short video tutorial. The written instructions are below the video.

Copy Excel VBA Code to a Regular Module

Instead of starting from scratch, if you need an Excel macro, you can often find sample code at reputable sites on the internet. To copy that code, and add it to one of your workbooks, follow these steps:

  1. Copy the sample code that you want to use
  2. Open the workbook in which you want to add the code
  3. Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
  4. Choose Insert | Module
  5. Where the cursor is flashing, choose Edit | Paste

To run the code:

  1. On the Excel Ribbon, click the View tab
  2. At the far right, click Macros
  3. Select a macro in the list, and click the Run button

Copy Excel VBA Code to a Worksheet Module

Another type of Excel code is Event code, which runs automatically when something specific occurs in the workbook. For example, if you enter a number in a cell, or select an entry in a cell's drop down list, the worksheet has been changed. This could trigger the Worksheet_Change event.

Worksheet event code is stored on a worksheet module. To add worksheet event code to your worksheet, do the following:

  1. Copy the code that you want to use
  2. Select the worksheet in which you the code to run
  3. Right click on the sheet tab and click View Code, to open the Visual Basic Editor.

  4. Where the cursor is flashing, choose Edit | Paste

Copy Excel VBA Code to a Workbook Module

Another type of code is Workbook Event code, which should be added to the workbook code module:

  1. Copy the code that you want to use
  2. Select the workbook in which you want to store the code
  3. Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
  4. In the Project Explorer, find your workbook, and open the list of Microsoft Excel Objects
  5. Right-click on the ThisWorkbook object, and choose View Code
  6. Where the cursor is flashing, choose Edit | Paste

Video: Copy Excel VBA Code From a Different Workbook

To see the steps for copying a macro from one workbook to another, in any version of Excel, please watch this short video tutorial. The written instructions are below the video.

Copy Excel VBA Code From a Different Workbook

You may find code in a sample workbook online, and decide to add it to one of your workbooks. You can copy all the code in a module by doing the following:

  1. Open both workbooks
  2. Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
  3. In the Project Explorer, find your workbook, and the workbook with the code that you want to copy. The screenshot at the right, the code is in VBACodeCopy.xls and will be copied to MyForm.xlsm
  4. In the workbook with the code, click the + sign to view the list of Modules
  5. Click on the module that you want to copy, and drag it over the project where you'd like the copy placed.
  6. Release the mouse button, and a copy of the module will appear in the workbook.

To run the code:

  1. On the Excel Ribbon, click the View tab
  2. At the far right, click Macros
  3. Select a macro in the list, and click the Run button

Modify Copied Excel VBA Code

If you copy VBA code into your Excel file, you might need to modify some of the references, to match your workbook.

  • In the code, look for references to "Worksheets" to "Sheets", and change those to the sheet names in your workbook.
  • Also look for "Range" references, such as Range("A1:G100"), and adjust those to match the location of your data.

These references might be at the top of the procedure, in a Set statement:

Set ws = Worksheets("SalesData")

or elsewhere in the code.

If you run the code without modifying the reference, you might see an error message:

"Run-time error '9'": Subscript out of range

run-time error 9

To see where the problem is, click the Debug button, and a line of code will be highlighted in yellow.

highlighted code

To stop the code, click the Run menu, then click Reset.

vba run reset

Change the sheet name in the line that was highlighted, save the changes, and try the code again.

Related Excel VBA Tutorials

 

Privacy Policy

 

Contextures Inc., Copyright ©2013
All rights reserved.

 
Last updated: May 23, 2013 9:02 AM