• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 642
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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