Solved

Excel 2010 VBA - Placing Code in ThisWorkbook versus Module1

Posted on 2013-12-08
3
531 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
Comment Utility
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 80

Assisted Solution

by:byundt
byundt earned 166 total points
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

772 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now