Improve company productivity with a Business Account.Sign Up

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

Modules

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
BostonBob
Asked:
BostonBob
1 Solution
 
nutschCommented:
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
 
GaryCommented:
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
 
BostonBobAuthor Commented:
Thanks for the succinct answer!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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