Advice

mrmagoo_83

Board Regular
Joined
Mar 7, 2005
Messages
82
I was just wondering what the opinions where on how to code a ton of macros.

Rigth now I have a Module 1, and within it there are probably 10 Sub routines. Would it be wiser to make each of those sub routines a module, or leave it the way I am doing it?
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
I suppose that largely depends on personal preference and the function of each sub.

Generally I keep related procedures in the same module. I.E. Menus in one, Print functions in another, etc. Really long subs should probably be separated as well.

HTH,

Smitty
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,988
I agree with Smitty.

Remember that you can rename your modules to help organize your VBA as well.
 

mrmagoo_83

Board Regular
Joined
Mar 7, 2005
Messages
82
Ok cool, I will seperate out a few of them, most of them are just creating arrays from the data gathered from a webpage by another sub.

So I will have one module for getting and sorting the data, another model to create the arrays.
 

mrmagoo_83

Board Regular
Joined
Mar 7, 2005
Messages
82

ADVERTISEMENT

Wait, how do I declare global variables between modules?
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Global variables, by their nature can be used in any Sub.

Generally, I pick the most non-specific module (meaning one that has the subs called the most), and after Option Explicit declare every multi-sub variable as Public. I.E.
Code:
Option Explicit 

Public ws as Worksheet
Public c as Range
Smitty
 

mrmagoo_83

Board Regular
Joined
Mar 7, 2005
Messages
82
Thanks Smitty, thats what I thought, and is what I have so its all good. I declare those global variables in the first module that is called from the user form.

One more quick question, I have a userform, how do I put the minimize button on it? Or is that possible?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,234
Messages
5,571,042
Members
412,358
Latest member
Eng Muhammed
Top