5 Places to Store VBA Code in a Workbook

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

Video: VBA Code Modules Explained

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.

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.

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.

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.

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.

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.

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!

#evba #etipfree #kingexcel
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1
#evba #etipfree #kingexcel