Go Premium for a chance to win a PS4. Enter to Win

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

Excel VBA code location, ThisWorkbook vs. Module

Hello,

What is the difference between VBA code inserted in ThisWorkbook and a Module?  I've seen it both ways.

Thanks

 Fig. 1
0
Steve_Brady
Asked:
Steve_Brady
3 Solutions
 
Dave BrettCommented:
Comprehensively covered by Pearson at http://www.cpearson.com/excel/codemods.htm

Cheers

Dave
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Steve,

my simple rule of thumb: the Sheet or ThisWorkbook modules will only contain Event subs that pertain to that object, i.e. the items you find in the "Procedures" drop down in the VBE for the module.

Everything else goes into Standard Modules.

Event subs can't be stored anywhere else but in their object modules, and to keep things clean and tidy, for all other subs I create several Standard Modules depending on what kind of code they store, i.e. all  functions in a Functions module, subs pertaining to particular functionality or particular sheets go into modules that have recognisable names.

just my 2 cents,

cheers, teylyn
0
 
patrickabCommented:
Steve,

Whilst agreeing with what has already been written here on this subject, in fact subs can always be referenced and so used by prefixing them by the code pane name they are in. So for example if Sub fred() is in the ThisWorkbook code module it can be positively identified and used like this: Call ThisWorkbook.fred

Personally I only place subs in the ThisWorkbook code module that refer to the Workbook and all other subs in user-defined code Modules.

Patrick
0
Industry Leaders: 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!

 
NorieCommented:
Patrick

Does that work for subs declared Private?
0
 
patrickabCommented:
imnorie,

>Does that work for subs declared Private?

By 'that' I assume you mean referencing the sub by the code pane it is in.

No, it doesn't work for 'Private' subs.

Patrick
0
 
NorieCommented:
Thought so, just wanted to check.

Sorry for not being clear.
0
 
C_sharp_beaverCommented:
The main point where to put code is security issue of code

If you want hide (protect) code from user - create separate .XLA file and keep code in it's modules (protected by password)

So user will be able to create his own macro but don't have access to yours
0
 
NorieCommented:
The main point of where to put code might be to put it somewhere where it will work.:)
0
 
Steve_BradyAuthor Commented:
multiple good answers
0
 
patrickabCommented:
Steve - Thanks for the points - Patrick
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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