Bottom line: In this post we compare the different places to store macros, functions, & VBA code in Excel.  We specifically look at the Code Modules, Sheet Module, and ThisWorkbook Module to learn the differences between how each works.  We also learn how to run macros based on events or actions by the user.

Skill level: Intermediate

5 Modules to Store VBA Macro Code in a Project Workbook2

Video: VBA Code Modules Explained

 

Watch video on Youtube & give it a thumbs up. 🙂

Checkout my FREE webinar on “The 7 Steps to Getting Started with Macros & VBA”.

Macros and VBA Webinar Banner - Join Me - 550x200

Click here to register for the webinar, it's FREE!

Download the File

Download the Excel file to follow along.

VBA Code Modules.xlsm (25.3 KB)

5 Places to Store VBA Code in a Workbook

There are actually 5 different modules where we can store VBA code in a workbook.  We can see each of these in the Project Explorer window (Ctrl+R) in the VB Editor.  Here is a quick overview of each object type.

  1. Code Modules – The code modules are the most common place we store macros.  The modules are located in the Modules folder within the workbook.
  2. Sheet Modules – Each sheet in the workbook has a sheet object in the Microsoft Excel Objects folder.  Double-clicking the sheet object opens its code module where we can add event procedures (macros).  These macros run when the user takes a specific action in the sheet.
  3. ThisWorkbook Module – Each workbook contains one ThisWorkbook object at the bottom of the Microsoft Excel Objects folder.  We can event based macros that run when the user takes actions in/on the workbook.
  4. Userforms – Userforms are interactive forms or windows where we can add controls like drop-down menus, list boxes, check boxes, buttons, etc.  Each userform is stored in the Forms folder and has a code module where we can put macros that will run when the form is open, and/or the user interacts with the controls on the form.
  5. Class Modules – Classes are stored in the Class Modules folder and allow us to write macros to create objects, properties, and methods.  Classes can be used when we want to create custom objects or collections that don't exist in the Object Library.

When we double-click or right-click> View Code (keyboard shortcut: F7) on any of these objects in the Project Explorer Window, the code window opens on the right side of the VB Editor.  The code window looks the same for each of the objects.  It is just a big blank canvas where we can type code.

Double Click a Code Module to View Code Window in VBA Editor

For this post we are going to compare the first 3 modules listed above.

Code vs Sheet vs ThisWorkbook Module

These are probably the 3 most common places we can store macros (sub procedures) and functions (UDFs) in our VBA projects.

Each of these modules allow us to store macros that we can run by pressing a button or from the Macro window.  However, the Sheet and ThisWorkbook objects allow us to store event procedures (macros) that will run when a user takes an action in the workbook.

So let's take a look at each object in more detail.

Code Modules

Code Modules allow us to store regular macros (sub procedures) and functions (User Defined Functions, UDFs).  This is the typical place we start writing and storing our macros.  The macro recorder also creates code in a code module.

Once we have a macro in a code module, we can run it from the Macro window (Developer tab or View Tab > Macros button).  We can also run the macro by assigning it to a button or shape.  When the user clicks the button, the macro will run.  Checkout my article and video on how to create a Personal Macro Workbook and add buttons to the ribbon for more details.

Overview of Code Module for VBA Macros in VB Editor1

We can add multiple code modules to the Modules folder, and this helps keep our code organized within a project (workbook).

So, the code module allows us to store basic macros that will be run by the user pressing a button.  But what if we want our macros to automatically run when the user opens a workbook, changes worksheets, or selects a specific cell?

Sheet Modules & Event Procedure Macros

Each sheet in the workbook has a sheet object in the VB Editor.  These are listed in the Microsoft Excel Objects folder for each open workbook in the Project Window.

The sheet object displays the sheet's code name and tab name in parentheses.  The tab name is the name that is displayed in the workbook that the user can change.

Double-clicking a sheet object will open it's code module window.

Overview of Sheet Module for VBA Macros in VB Editor

We can add regular macros in the sheet object.

We can also add Event Procedures that will run when a user takes an action on the worksheet.  Here's how to add an event procedure.

  1. Select Worksheet from the Objects drop-down menu.  The Selection Change event will automatically be added to the code window.  This is the default event, but we do NOT have to use this event.  The SelectionChange event runs every time the user selects a cell in the sheet.
  2. Click the Procedure drop-down to see a list of the other event procedures.
  3. Select one of the events from the list and it's procedure (macro) code will be added the sheet's code module.  We can now add code inside the procedure that will run when the user takes an action.

Add a VBA Event Procedure to the Sheet Code Module in the VB Editor

In the video I use a code example that selects the entire row and column of the cell that is selected in the worksheet.  This selects/highlights the entire row and column for the user.  Checkout the video for more details on how it works.

VBA Macro to Select Entire Row and Column On Cell or Range Selection - SelectionChange Event

The sheet modules can contain multiple event procedures.  It can also contain regular procedures (macros) and functions.

There is a list of all the worksheet events and their help articles on the MSDN Worksheet Object Help Page.

ThisWorkbook Module & Event Procedure Macros

Each workbook contains one object called ThisWorkbook.  The ThisWorkbook object is stored at the bottom of the Microsoft Excel Objects folder.

The main purpose of the ThisWorkbook module is to run event procedures (macros) when the user takes an action in the workbook.  This could be when the user opens the workbook, before they close the workbook, when they select a worksheet, range, or even update a pivot table.

Overview of ThisWorkbook Module for VBA Macros in VB Editor1

The ThisWorbook module is very similar to the sheet modules.  The main difference is that the event procedures in the ThisWorkbook module can run when actions are taken throughout the entire workbook.  The sheet module's events only run when actions are taken on the specific sheet that the code is in.

The process to add event procedures to the ThisWorkbook module is the same as the sheet module.

  1. Select Workbook from the Object drop-down.
  2. The Workbook_Open Event is automatically added to the module.  This macro will run when the workbook is opened and macros are enabled.  You can delete this event's code if you do not want to use it.
  3. Select another event from the Procedure's drop-down.
  4. The code for that procedure will be added to the module.  Add code to the procedure that will run when the user takes that action in the workbook.

The Worksheet events are also made available in the ThisWorkbook list of events.  This means we can run worksheet events on any worksheet, or specific worksheets, instead of copy/pasting code between sheet modules.

There is a list of all the workbook events and their help articles on the MSDN Workbook Object Help Page.

What's the Best Place to Store My Code?

Now that you know how the different modules work, you might be wondering where the best place to store all your code is.  And the answer is, it's a matter a personal preference…

Put All the Code in Sheet Modules

Technically, you can store all your macros and functions in a Sheet module or ThisWorkbook module.  Some developers like this approach because it keeps everything tied to a specific sheet or set of sheets.  The sheets can then be moved or copied to different workbooks, and the code will travel with it.  It's important to note that UDFs must be stored in a code module.

Organize Code in Code Modules

Other developers like to have all macros organized in the code modules.  If they need to use sheet or workbook events, they add those events to the appropriate module and then call macros in the code modules from those events.  The advantage here is that it is easy to see all your code in one place.  You can add comments to macros that are called by event procedures so you know how the macros are run.

Personally, I prefer storing my code in code modules for this reason.  When I open a workbook in the VB Editor it's easy to see that the macro contains code, without having to double-click each sheet object to see if code exists in the module.  This can be time consuming if the workbook has a lot of sheets, and can make debugging a project more difficult.

Plan Your Legacy Wisely!

There are pros and cons to each method, so my suggestion is to pick the one that works for you.  If you are going to be passing on the workbook or project to someone else to maintain in the future, then try to make it easy for them to find your code.  Planning your legacy ahead of time will mean less work for you in the future…

Please leave a comment below with any questions.  Thank you! 🙂

Free Webinar on Macros & VBA

My free webinar on “The 7 Steps to Getting Started with Macros & VBA” is going on now, and it's FREE!

Macros and VBA Webinar Banner - Join Me - 550x200

Click here to register for the FREE webinar!

18 comments

Your email address will not be published. Required fields are marked *

  • I have no question I just wanted to say thank you for all the help you give folks. As a totally disabled vet, it is hard to spend money to learn anything to do with excel and I have no plans of ever working outside my home. Well except helping others someday on line that need help to set up small excel programs. Thank you again so much

  • Hi Jon,

    thanks for this great article. Very good.

    I am struggling with the following issue. I have a code that enables the user by Drop Down to filter horizontal. This code is in the sheet module. Every time the user changes the drop down cell, the filter works.
    The workbook that contains the filter has another macro. This one collects data from other workbooks and safe the workbook as a new file.
    Now the problem… When the workbook was saved, the filter macro doesn´t work anymore. Only when I close the whole excel application and open afterwards again the filter macro works.
    I do not understand why? And what can I do do avoid this?

    Thanks for any idea.
    Best regards Pam

  • Jon,

    I have a sheet code that gives the user a box to enter a number. is it possible to have it where they could enter multiple numbers using a separator of some sort?

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    Dim strCellName As String
    Dim strReplace As String
    strCellName = Target.Value

    If InStr(1, strCellName, “@Para1”) > 0 Then

    strReplace = InputBox(“Enter Site OR % (Wildcard)”)
    If strReplace = “” Then
    Cancel = True
    Exit Sub
    End If

    strCellName = Replace(strCellName, “@Para1”, strReplace)

    Call ExecSql(strCellName)
    Call Macro1

    Cancel = True

    End Sub

  • Hi Jon,
    You prefer storing your code in code modules, including sheet events. How do you move the event trigger code?

  • Thanks for the article, Jon!

    Is it possible to have a click_event sub on the sheet call another sub in the code module section? If so, what does that call look like -its syntax?

    I tried it with the code below but got a compile error: “sub or function isn’t defined”. My sheet macro is supposed to pass the active shape’s name (shName variable) to a sub named ButtonExpand I placed on a code module. W

    ButtonExpand (shName)

  • I don’t know if this is the right place for my question, but I’ll try anyway.
    I’ve developed an excel workbook with a risk matrix. I use ellipse shapes with a number to populate the risk matrix to indicate if a risk is low, medium, high and what the impact is. I would like to be able to move the shapes without seeing the shape handles so it is a nice visual user interface to move risks on the matrix. I would also like to have an event on clicking a shape so that I can create macro’s to show specific information of that particular risk.
    Hope that made sense, appreciate your suggestions.

    Thanks.
    Johan.

  • Hai.

    I would like to write a macro to copy the value from the last row of all the sheets in a workbook into a single sheet in the same workbook.

    Could you help me?

    WBR,
    KTSK

  • Thanks for your article, I am looking for how to create 2 vba procedure in “Thisworkbook” the two procedure are:

    1. A procedure to open an excel file on your choice worksheet

    2. The second procedure is to make an excel file expired after certain period of time and automatically delete the file.

    please is there a way to do this, kindly help me out.

  • Great examples Jon!

    Can you post some examples on how to run events from Addin?

    For example: sheet change event triggered from an addin

    Thanks!

  • Hi

    I just watched the 3 videos on Pivot tables and Dashboards. The videos were very well done and easy to understand. I can’t wait to start creating!

    I also want to learn how to add a “refresh” button right in my workbook that pulls in new data as I want to update. I have used this at a past employer, but I do not know how to do it. Is there a tutorial for this type of function?

    Thanks, I look forward to future learning!

    Diane

  • Thank you for the videos they were very well done and informative, the best I have seen.

    I am trying to determine if I am able to use VLOOKUP to weight the 1 or more possibilities in the multiple dropdown list in the cell. I have several cells that have multiple option in the cell.

    I have attached a copy of the code I used and modified.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngDV As Range
    Dim oldVal As String
    Dim newVal As String
    If Target.Count > 1 Then GoTo exitHandler

    On Error Resume Next
    Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
    On Error GoTo exitHandler

    If rngDV Is Nothing Then GoTo exitHandler

    If Intersect(Target, rngDV) Is Nothing Then
    ‘do nothing
    Else
    Application.EnableEvents = False
    newVal = Target.Value
    Application.Undo
    oldVal = Target.Value
    Target.Value = newVal
    If Target.Column = 12 _
    Or Target.Column = 13 _
    Or Target.Column = 16 _
    Or Target.Column = 18 _
    Or Target.Column = 19 _
    Or Target.Column = 21 Then
    If oldVal = “” Then
    ‘do nothing
    Else
    If newVal = “” Then
    ‘ do nothing
    Else
    Target.Value = oldVal _
    & “, ” & newVal
    End If
    End If
    End If
    End If

    exitHandler:
    Application.EnableEvents = True
    End Sub

    Any advise would be helpful,

    • Hi Sean,
      You can use the Vlookup function in VBA. Here is the code.

      WorksheetFunction.Vlookup(arg1,arg2,arg3,[arg4])

      The arguments will be the same as those in Excel. You can specify a range in a worksheet for the Table_Array (lookup range). So you might want to add a lookup table to a sheet in your workbook and use that code in VBA to weight the values.

      You can also store the values in an array in VBA. There are a lot of options for this, but the Vlookup one should be pretty easy to implement. I hope that helps get you started.

Search
Generic filters
Exact matches only
Filter by Custom Post Type

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly