Solved

Excel 2010 VBA - Placing Code in ThisWorkbook versus Module1

Posted on 2013-12-08
3
556 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
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

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

770 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