Code module in excel

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

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

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

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

Download the File

Download the Excel file to follow along.

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 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! 🙂

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!

Click here to register for the FREE webinar!

Итог: в этой статье мы сравниваем различные места для хранения макросов, функций и кода VBA в Excel. Мы специально смотрим на модули кода, листовой модуль и модуль ThisWorkbook, чтобы узнать различия между тем, как работает каждый из них. Мы также узнаем, как запускать макросы на основе событий или действий пользователя.

Уровень мастерства: Средний

Видео: объяснение кодовых модулей VBA

Скачать файл

Загрузите файл Excel.

VBA Code Modules.xlsm (25.3 KB)

5 мест для хранения кода VBA в рабочей книге

На самом деле есть 5 различных модулей, в которых мы можем хранить код VBA в рабочей книге. Каждый из них виден в окне Project Explorer (Ctrl + R) в редакторе VB. Вот краткий обзор каждого типа объекта.

1. Code Modules — модули кода являются наиболее распространенным местом, где мы храним макросы. Модули находятся в папке «Модули» в рабочей книге.
2. Sheet Modules — у каждого листа в книге есть объект листа в папке «Объекты Microsoft Excel». Двойной щелчок объекта листа открывает его модуль кода, в который мы можем добавить процедуры обработки событий (макросы). Эти макросы запускаются, когда пользователь выполняет определенное действие на листе.
3. ThisWorkbook Module — каждая книга содержит один объект ThisWorkbook в нижней части папки объектов Microsoft Excel. Мы можем создавать макросы на основе событий, которые выполняются, когда пользователь выполняет действия в / над книгой.
4. Userforms — пользовательские формы — это интерактивные формы или окна, в которые мы можем добавлять элементы управления, такие как раскрывающиеся меню, списки, флажки, кнопки и т. Д. Каждая пользовательская форма хранится в папке «Формы» и имеет модуль кода, в который можно помещать макросы, которые будут запускаться. когда форма открыта и / или пользователь взаимодействует с элементами управления в форме.
5. Class Modules — классы хранятся в папке «Модули классов» и позволяют нам писать макросы для создания объектов, свойств и методов. Классы могут использоваться, когда мы хотим создать пользовательские объекты или коллекции, которые не существуют в Библиотеке объектов.

Когда мы дважды щелкаем или щелкаем правой кнопкой мыши>
Просмотр кода (сочетание клавиш: F7) на любом из этих объектов в окне Project
Explorer, окно кода открывается справа от редактора VB. Окно кода выглядит
одинаково для каждого из объектов. Это просто большой пустой холст, где мы
можем напечатать код.

Для этого поста мы собираемся сравнить первые 3 модуля, перечисленные выше.

Code vs Sheet vs ThisWorkbook Module

Это, вероятно, 3 наиболее распространенных места, где мы можем хранить макросы (подпроцедуры) и функции (UDF) в наших проектах VBA.

Каждый из этих модулей позволяет нам хранить макросы, которые мы можем запустить нажатием кнопки или из окна макроса. Однако объекты Sheet и ThisWorkbook позволяют нам хранить процедуры событий (макросы), которые будут выполняться, когда пользователь выполняет действие в рабочей книге.

Итак, давайте посмотрим на каждый объект более подробно.

Code Modules

Модули кода позволяют нам хранить обычные макросы (подпроцедуры) и функции (пользовательские функции, пользовательские функции). Это типичное место, где мы начинаем писать и хранить наши макросы. Устройство записи макросов также создает код в модуле кода.

Когда у нас есть макрос в модуле кода, мы можем запустить его из окна макроса (вкладка «Разработчик» или «Просмотр вкладки»> «Макросы»). Мы также можем запустить макрос, назначив его кнопке или фигуре. Когда пользователь нажимает кнопку, макрос запускается. Ознакомьтесь с моей статьей и видео о том, как создать Персональную книгу макросов и добавить кнопки на ленту для получения более подробной информации.

Мы можем добавить несколько модулей кода в папку «Модули», и это помогает сохранить наш код организованным в рамках проекта (рабочей книги).

Таким образом, модуль кода позволяет нам хранить основные макросы, которые будут запускаться пользователем нажатием кнопки. Но что, если мы хотим, чтобы наши макросы запускались автоматически, когда пользователь открывает рабочую книгу, меняет рабочие таблицы или выбирает конкретную ячейку?

Sheet Modules & Event Procedure Macros

Каждый лист в книге имеет объект листа в редакторе VB. Они перечислены в папке «Объекты Microsoft Excel» для каждой открытой книги в окне «Проект».

Объект листа отображает кодовое имя листа и имя вкладки в скобках. Имя вкладки — это имя, отображаемое в книге, которое пользователь может изменить.

Двойной щелчок по объекту листа откроет окно его модуля кода.

Мы можем добавить обычные макросы в объект листа.

Мы также можем добавить процедуры обработки событий, которые будут выполняться, когда пользователь выполняет действие на листе. Вот как добавить процедуру обработки события.

1. Выберите «Рабочий лист» в раскрывающемся меню
   «Объекты». Событие выбора будет автоматически добавлено в окно кода. Это
   событие по умолчанию, но мы НЕ должны использовать это событие. Событие SelectionChange
   запускается каждый раз, когда пользователь выбирает ячейку на листе.
2. Щелкните раскрывающийся список «Процедура»,
   чтобы увидеть список других процедур обработки событий.
3. Выберите одно из событий из списка, и его код
   процедуры (макроса) будет добавлен в модуль кода листа. Теперь мы можем
   добавить код внутри процедуры, которая будет выполняться, когда пользователь
   выполняет действие.

В видео я использую пример кода, который выбирает всю строку и столбец ячейки, выбранной на листе. Он выбирает/выделяет всю строку и столбец для пользователя. Посмотрите видео, чтобы подробно изучить, как это работает.

Модули листа могут содержать несколько процедур обработки событий. Он также может содержать обычные процедуры (макросы) и функции.

На странице справки по объекту MSDN Worksheet есть список всех событий рабочего листа и их справочных статей.

Модуль ThisWorkbook и макросы процедуры события

Каждая рабочая книга содержит один объект с именем
ThisWorkbook. Объект ThisWorkbook хранится в нижней части папки объектов
Microsoft Excel.

Основное назначение модуля ThisWorkbook — запуск процедур
обработки событий (макросов), когда пользователь выполняет действие в книге.
Это может произойти, когда пользователь открывает рабочую книгу, прежде чем
закрыть рабочую книгу, когда он выбирает рабочую таблицу, диапазон или даже
обновляет сводную таблицу.

Модуль ThisWorbook очень похож на модули листа. Основное отличие состоит в том, что процедуры обработки событий в модуле ThisWorkbook могут выполняться, когда действия выполняются во всей книге. События модуля листа выполняются только тогда, когда действия выполняются на конкретном листе, в котором находится код.

Процесс добавления процедур обработки событий в модуль ThisWorkbook аналогичен модулю листа.

1. Выберите Workbook из выпадающего меню Object.
2. Событие Workbook_Open автоматически добавляется
   в модуль. Этот макрос запускается при открытии книги и включении макросов. Вы
   можете удалить код этого события, если вы не хотите его использовать.
3. Выберите другое событие из выпадающего списка
   Процедуры.
4. Код для этой процедуры будет добавлен в модуль.
   Добавьте код в процедуру, которая будет выполняться, когда пользователь
   выполнит это действие в книге.

События рабочего листа также доступны в списке событий ThisWorkbook. Это означает, что мы можем запускать события листа на любом листе или на отдельных листах вместо копирования / вставки кода между модулями листа.

На странице справки по объекту MSDN Worksheet есть список всех событий рабочего листа и их справочных статей.

Какое лучшее место для хранения моего кода?

Теперь, когда вы знаете, как работают различные модули, вам может быть интересно, где лучше всего хранить весь ваш код. И ответ таков: это вопрос личных предпочтений …

Поместите весь код в листовые модули

Технически вы можете хранить все свои макросы и функции в модуле Sheet или модуле ThisWorkbook. Некоторым разработчикам нравится этот подход, потому что он привязывает все к определенному листу или набору листов. Затем листы можно перемещать или копировать в разные рабочие книги, и код будет перемещаться вместе с ними. Важно отметить, что пользовательские функции должны храниться в модуле кода.

Организовать код в кодовых модулях

Другим разработчикам нравится организовывать все макросы в
модулях кода. Если им нужно использовать события листа или книги, они добавляют
эти события в соответствующий модуль и затем вызывают макросы в модулях кода из
этих событий. Преимущество в том, что весь ваш код легко увидеть в одном месте.
Вы можете добавлять комментарии к макросам, которые вызываются процедурами
обработки событий, чтобы вы знали, как выполняются макросы.

По этой причине я предпочитаю хранить свой код в модулях
кода. Когда я открываю книгу в редакторе VB, легко увидеть, что макрос содержит
код, без необходимости дважды щелкать каждый объект листа, чтобы увидеть,
существует ли код в модуле. Это может занять много времени, если в рабочей
книге много листов, и это может затруднить отладку проекта.

Планируйте свое наследие с умом!

У каждого метода есть свои плюсы и минусы, поэтому я предлагаю выбрать тот, который подходит именно вам. Если вы собираетесь передавать рабочую книгу или проект кому-то другому для поддержки в будущем, постарайтесь облегчить им поиск вашего кода. Планирование вашего наследия заблаговременно будет означать меньше работы для вас в будущем …

Пожалуйста, оставьте комментарий ниже с любыми вопросами. Спасибо!

VBA Macro is for developers. In Excel, macro is a piece of code written in VBA and VBA is Microsoft’s programming language, it stands for Visual Basic for Applications. The module is a file with a .bcf extension that stores the code written in the Visual Basic for Applications editor. Let’s learn, how to run a code from a module in excel VBA. 

Run a code from the Module in Excel VBA

Step 1: Go to Developer Tab, under the code section you will find Visual basic. Now click on Visual Basic. 

Step 2: Microsoft Visual Basic for Applications(VBA) dialogue box appears. In the Menu bar, go to Insert Tab, and click on Module.

Step 3: A Module named Module1 is created under the VBA project. 

Step 4: Write the code you want to run in Microsoft excel. For example, a message box will appear stating “Learning geeks for geeks”. 

Step 5: In the Tools bar. Click on the run button. You can also press F4(Fn + f4) on your keyboard to run the module code. 

Step 6: The code pauses and the Microsoft VBA editor minimizes and a message box appears in the MS Excel worksheet. Click Ok. The code resumes and the Microsoft VBA editor maximizes again. 

Scope of Module in VBA 

The scope of the module in VBA is workbook level. You can use the written modules in different worksheets. You have created a module named module1 in shee1 above. A sub-procedure named geeksforgeeks() is also created. We will use this procedure to check the scope of a module in VBA. Following are the steps: 

Step 1: Click on the plus and a new worksheet named sheet2 is created. 

Step 2: Go to the Developer tab, and click on Macros. 

Step 3: A dialogue box named Macro appears. In the macro name, you will find a procedure name geeksforgeeks(). This was the procedure that we wrote in sheet1 of this workbook. This proved that the scope of the module is workbook level. Select it, and click on Run. 

Step 4: The code written in the module1 sub procedure gets executed. A dialogue box appears. 

I would suggest separating your code based on the functionality and purpose specific to each sheet or module. In this manner, you would only put code relative to a sheet’s UI inside the sheet’s module and only put code related to modules in respective modules. Also, use separate modules to encapsulate code that is shared or reused among several different sheets.

For example, let’s say you multiple sheets that are responsible for displaying data from a database in a special way. What kinds of functionality do we have in this situation? We have functionality related to each specific sheet, tasks related to getting data from the database, and tasks related to populating a sheet with data. In this case, I might start with a module for the data access, a module for populating a sheet with data, and within each sheet I’d have code for accessing code in those modules.

It might be laid out like this.

Module: DataAccess:

Function GetData(strTableName As String, strCondition1 As String) As Recordset
    'Code Related to getting data from the database'
End Function

Module: PopulateSheet:

Sub PopulateASheet(wsSheet As Worksheet, rs As Recordset)
    'Code to populate a worksheet '
End Function

Sheet: Sheet1 Code:

Sub GetDataAndPopulate()
    'Sample Code'
     Dim rs As New Recordset
     Dim ws As Worksheet
     Dim strParam As String
     Set ws = ActiveSheet
     strParam = ws.Range("A1").Value

     Set rs = GetData("Orders",strParam)

     PopulateASheet ws, rs
End Sub

Sub Button1_Click()
    Call GetDataAndPopulate
End Sub