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?
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

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
8,053
Office Version
  1. 365
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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,551
Messages
5,838,055
Members
430,527
Latest member
MyFace2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top