Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel VBA code location, ThisWorkbook vs. Module

Posted on 2010-11-25
10
Medium Priority
?
1,788 Views
Last Modified: 2012-05-10
Hello,

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

Thanks

 Fig. 1
0
Comment
Question by:Steve_Brady
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 50

Accepted Solution

by:
Dave Brett earned 1000 total points
ID: 34215309
Comprehensively covered by Pearson at http://www.cpearson.com/excel/codemods.htm

Cheers

Dave
0
 
LVL 50

Assisted Solution

by:Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 600 total points
ID: 34215898
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
 
LVL 45

Assisted Solution

by:patrickab
patrickab earned 400 total points
ID: 34216646
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
Independent Software Vendors: 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!

 
LVL 34

Expert Comment

by:Norie
ID: 34218376
Patrick

Does that work for subs declared Private?
0
 
LVL 45

Expert Comment

by:patrickab
ID: 34218602
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
 
LVL 34

Expert Comment

by:Norie
ID: 34218671
Thought so, just wanted to check.

Sorry for not being clear.
0
 

Expert Comment

by:C_sharp_beaver
ID: 34301283
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
 
LVL 34

Expert Comment

by:Norie
ID: 34301511
The main point of where to put code might be to put it somewhere where it will work.:)
0
 

Author Closing Comment

by:Steve_Brady
ID: 34304726
multiple good answers
0
 
LVL 45

Expert Comment

by:patrickab
ID: 34304748
Steve - Thanks for the points - Patrick
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
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…

721 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