Contact Me via Email

Inserting VBA Code (Macro) for General Purpose Use

Site Home Page

Excel Tutorials

  This page provides guidance on setting up to insert 'general purpose' Visual Basic for Applications (VBA) code into a workbook.  You may either type in code to be run or paste code from another source.

VBA code is the language of Excel Macros.  Virtually anything that can be done with a formula may be accomplished with VBA.  But in some cases, a single well-written cell function can replace many lines of VBA code.

Feel free to browse around.  - j.latham (MS MVP [2006], MOS Master)
                                                                           Member IEEE
                                                                           Member Carnegie-Mellon Software Engineering Institute
                                                                           Member ASTD

Copyright 2006 by J. L. Latham, All Rights Reserved.

Begin the process to add a code module to be associated with an Excel worksheet by opening the Visual Basic Editor.  From the Excel menu bar, choose
Macro | Visual Basic Editor (or simply press [Alt]+[F11])

Other 'start code' examples: Associate Code with a Worksheet Event  Associate Code with Workbook Events

Begin the process to add a code module by opening the Visual Basic Editor.  From the Excel menu bar, choose
Macro | Visual Basic Editor (or simply press [Alt]+[F11])

 

The Excel Visual Basic Editor will open.  If this is the first time that code has been added to it in this workbook, the main portion of the screen will be empty.

If you cannot see the "Project - VBAProject" window, choose
View | Project Explorer
from the menu bar, or press [Ctrl]+[R] to bring it into view.

 

To create code for general purpose use from anywhere within the workbook, choose Insert | Module from the VBA Editor menu bar.

 

You are now ready to begin declaring procedures (Sub and Function) within the code module.  Notice that the module has been added as part of the workbook's Project.  The code will remain with this book when it is closed and be available for use when it is opened.

Your screen may not show the words Option Explicit at the top of the module.  This statement indicates that all user defined constants and/or variables must be declared with a Dim statement before they are used.  This is a good habit to get into as it will help prevent errors that are often difficult to see, such as typographic errors in a name.  To have Excel automatically require declaration of constants/variables when a module is created, choose

Tools | Options
from the menu bar and check the box next to the Require Variable Declaration entry on the [Editor] tab.