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

x
?
Solved

Modules

Posted on 2014-03-10
3
Medium Priority
?
236 Views
Last Modified: 2014-03-10
Hi All,

Today's question is just a general information question but I have never had a definitive answer.  So having said that...

Can somebody please explain to me why we should put our subs and functions in modules instead of the actual sheet itself?  Is it because we want to be able to "Call" the subs/functions from the modules and this is easier than if the sub/functions are in the sheets themselves?  Does it make the vba program run faster?

What are the benefits?  

thanks,
0
Comment
Question by:BostonBob
3 Comments
 
LVL 39

Accepted Solution

by:
nutsch earned 2000 total points
ID: 39919272
If you want to call a macro that's in sheet code, you'll need to prefix it with the vba sheet name, which you can change, but which by default, doesn't give you a lot of information about the sheet, e.g. sheet35.Macro1

This means you need to find out first which sheet has your macro before you can call it. That makes for slower coding, but I don't believe it makes for slower execution.

In terms of code maintenance, I use workbook and worksheet codes to handle workbook and worksheet events respectively and use the modules and class modules to contain everything else. When I open someone else's files, this is also where I look to find macros so deviation from that norm would slow me down.

My 2 cents, fwiw,

Thomas
0
 
LVL 58

Expert Comment

by:Gary
ID: 39919305
nutsch sums it up pretty well but if you want a more detailed explanation/logic behind which to use.
http://www.cpearson.com/excel/codemods.htm

::no points for this::
0
 

Author Closing Comment

by:BostonBob
ID: 39919446
Thanks for the succinct answer!
0

Featured Post

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!

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

916 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