[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 636
  • Last Modified:

Excel 2010 VBA - Placing Code in ThisWorkbook versus Module1

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
brothertruffle880
Asked:
brothertruffle880
3 Solutions
 
Steven HarrisPresidentCommented:
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
 
byundtCommented:
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
 
Jacques Bourgeois (James Burger)PresidentCommented:
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now