Solved

Excel 2010 VBA - Placing Code in ThisWorkbook versus Module1

Posted on 2013-12-08
3
581 Views
Last Modified: 2013-12-11
I am just tossing this out as a general question about usage of the various code windows:

What's the difference between placing code in Module1 versus ThisWorkbook?
0
Comment
Question by:brothertruffle880
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 18

Accepted Solution

by:
Steven Harris earned 167 total points
ID: 39705158
A great explanation from Hans Vogelaar MVP (MCC, MVP):

The sheet modules are intended for event procedures that react to events at the level of the individual worksheets. For example, a Worksheet_Change event procedure in the sheet module of Sheet2 will be executed when the user changes the value of one or more cells in Sheet2.

The ThisWorkbook module is intended for event procedures that react to events at the workbook level. For example, a Workbook_Open event procedure in ThisWorkbook will be executed each time the workbook is opened.

Procedures (Subs) in the above modules will generally be Private, i.e. they are available only within those modules. It is possible to create Public code in these modules but that is unusual.

Modules under the heading of Modules are "standard" modules. They are used for macros, user-defined functions and "general" code. Procedures and functions in standard modules can be Private, i.e. only visible within their module, or Public, i.e. visible to all code in the same workbook.
0
 
LVL 81

Assisted Solution

by:byundt
byundt earned 166 total points
ID: 39705174
Workbook event subs (such as Workbook_Open or Workbook_BeforeSave) must go in ThisWorkbook code pane. They won't work at all if you place them anywhere else.

Worksheet event subs (such as click event macros for ActiveX controls, Worksheet_Change or Worksheet_SelectionChange) must go in the code pane for the respective worksheet. They won't work at all if you place them anywhere else.

It is considered good practice to put all other macros plus user-defined functions in regular module sheets (Module1, Module2, etc.). Although it is possible to put those macros and functions in ThisWorkbook or worksheet code panes, you will find it harder to use them (you must refer to them in the Macro Selector as ThisWorkbook.MyMacro or Sheet2.AnotherMacro).
0
 
LVL 40

Assisted Solution

by:Jacques Bourgeois (James Burger)
Jacques Bourgeois (James Burger) earned 167 total points
ID: 39707986
Code in a module is intended to be used from anywhere in the Workbook. This is usually code that needs to be used by many different sheets.

When code needs to be used only internally in a Worksheet, you put it in the Worksheet . That way, if you have a bug, you need to look only there, and not everywhere in the Workbook. And if you ever delete the Worksheet, the code will go with it instead of taking useless space in the module.

Code in this workbook is code that need to be used only to react to events in the Workbook, such as when it opens or before it closes.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question