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
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
Last edited by gimli; 04-26-2010 at 05:49 PM.
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,
Brandtrock
Brandtrock beat me to it but I found this also
http://www.cpearson.com/excel/codemods.htm
HTH
John
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.
Yeah, Philadelphia sounds a nice place http://www.usatoday.com/communities/...-on-cops-kid/1, maybe a result of those cheese steaks.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Ha ! Yes...or throwing snowballs at Santa Clause during and Eagles game. Never said its a nice town..just good sub's, steak hogies.
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
You will notice that the lad in the booking photo is no longer having fun.