Best Place for Subs

[gimli]

Wheres the best place to put subroutines?
In Module, worksheet or workbook?

Im thinking I should put them in the module. Is there any rule of thumb or logic for the best place to put them?

thanks

[Brandtrock]

I haven't been around for a while now, but thought I'd take a look at what was happening here again. Not that that matters much to you, but my initial thought when seeing your title was to give you a good ribbing about Subway vs. Jimmie John's vs. Quizno's vs. Blimpie but then decided against it since my unique sense of humor has been absent for so long.

Your instinct is generally the right answer. Modules are a great place for your code, generally. With some care, you can develop routines that are versatile and able to be used over and over in other sheets. Johnske even has a nice little code library in the kb so you can keep track of the useful routines you have made (or find - yoinked is the term that was used by many to describe code that has been "found" and adapted to fit your needs - remember to credit those whose code you yoink please).

There are reasons to include code in something other than a standard module though. Class modules afford you more flexibility/creativity in writing your code as you literally create objects to be used by your code. If these interest you, there are a number of good threads on here about them if you search for them.

Sheet level code offers you less flexibility in terms of exporting your code for future use, but you might want your code in the sheet simply to keep prying eyes from going to the VBE to discover how you did something by looking in the module. Granted, only the very inexperienced VBA user will not know to look at the sheet for code if they don't see a module, but it might fool the janitor.

Then you also have the controls that can hold code. This works nicely as the control, once programmed, can be copied and the code goes with it. Perhaps not an earth shattering revelation, but hey, I've been up a long time and I should probably shut up and go to sleep.

In any case, I hope this helps your thought process or at least invite some more replies for you to read.

Regards,

[jolivanes]

Brandtrock beat me to it but I found this also

http://www.cpearson.com/excel/codemods.htm

HTH
John

[gimli]

Hey thanks for the good input. Being newbie its always helpful to pick up on things from the experienced crowd.

Although im not a newbie when it comes to sub's :-) Being from Phily I prefer a good Philly cheese steak. Tony Luke's is one of the best.

[Bob Phillips]

Yeah, Philadelphia sounds a nice place http://www.usatoday.com/communities/gameon/post/2010/04/phillies-fan-charged-with-intentionally-vomiting-on-cops-kid/1, maybe a result of those cheese steaks.

[gimli]

Ha ! Yes...or throwing snowballs at Santa Clause during and Eagles game. Never said its a nice town..just good sub's, steak hogies. :beerchug:

[Cyberdude]

Don't forget to use Personal.xls (or .xlsm) as a convenient place to store macros that a lot of your workbooks will be referencing. If it's in the Personal workbook then you have only one copy to update.
Just a thought.
Sid

[GTO]

You will notice that the lad in the booking photo is no longer having fun.