Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
I'm new to VBA - why more than one Module?
Hi, I'm trying to learn how to be better organized with code. I can't
find anywhere why someone would want more than one module for general code. Yet I see Module 1, Module 2, etc. Is it because some code gets really long? Is it to better organize code, even if not long? How many modules do some of you pros end up with? Any tips on how to organize code into various modules, renaming them to be more meaningful, and organized. Thanks, Harold |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
I'm new to VBA - why more than one Module?
Your last paragraph says it all regarding why more than one module. It is
done to organize things in your head. It is amazing how clear the code is when you are writing it and how opaque it is a few weeks or months or years later. It is also better (again, for your head) to break up your code into smaller macros rather than have one long macro. HTH Otto "Harold Good" wrote in message ... Hi, I'm trying to learn how to be better organized with code. I can't find anywhere why someone would want more than one module for general code. Yet I see Module 1, Module 2, etc. Is it because some code gets really long? Is it to better organize code, even if not long? How many modules do some of you pros end up with? Any tips on how to organize code into various modules, renaming them to be more meaningful, and organized. Thanks, Harold |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
I'm new to VBA - why more than one Module?
I have an add-in where I store most of my macros......frequently used and
some just for practice and those I glean off these news groups. I have a dozen or so modules which I have given descriptive names so's I can keep track of which types of routines are stored in each module. Toolbars_Menus Event_Codes Protection Range_Selections User_Defined_Functions You get the idea. When the add-in gets a little large I simply export very unused modules as *,bas files and keep them in a folder so's I can retrieve when needed. To rename Module1 to something more descriptive, select and ViewProperties Window. Delete the Module1 name and rename. Gord Dibben MS Excel MVP On Tue, 22 Dec 2009 16:20:44 -0600, Harold Good wrote: Hi, I'm trying to learn how to be better organized with code. I can't find anywhere why someone would want more than one module for general code. Yet I see Module 1, Module 2, etc. Is it because some code gets really long? Is it to better organize code, even if not long? How many modules do some of you pros end up with? Any tips on how to organize code into various modules, renaming them to be more meaningful, and organized. Thanks, Harold |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
I'm new to VBA - why more than one Module?
Per Harold Good:
Hi, I'm trying to learn how to be better organized with code. I can't find anywhere why someone would want more than one module for general code. Yet I see Module 1, Module 2, etc. Is it because some code gets really long? Is it to better organize code, even if not long? Both. Also, there's portability. For instance, one might have a module that is dedicated to parsing and displaying errors. If that module contains all the needed code - and only the needed code - the functionality can be easily copied to other applications. Excel is a concrete example for me. I've a module I call "basExcel" and it has routines to open a new spreadsheet, open an existing spreadsheet, make sure a tab name is legal, and so-on and so-forth. Whenever I write a new app that has to do stuff with Excel spreadsheets, I just copy that module into it. How many modules do some of you pros end up with? Depends on the app. Right now I'm looking at a bond trading application that has 37 modules. 6 of them are unique to the application, but always present in every app: basAutoExec basCalc basGlobals basReport basTree basValidate 21 of them are the same or very similar code in every application: basAppKill basAutoExec basBizDay basBugAlert basColor basCommonFileDialog basConnect basEmail basErrorCheck basErrorCodes basExcel basFollowHyperLink basGuid basLogFile basParseToArray basScreenModeSet basStickTo basSubDataSheets basTextWidth basUtility basWorkTables Any tips on how to organize code into various modules, renaming them to be more meaningful, and organized. Portability is my primary concern. I prefix module names with "bas", just because I like to be able to tell what kind of object something is by looking at the name. "tbl..", "qry...", "frm...", "rpt..." and so-on and so-forth. -- PeteCresswell |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
I'm new to VBA - why more than one Module?
Not to confuse VBA modules with modular programming, VBA modules are more
like a filing system for code within a workbook. There is no set rule as to how the public modules should be used. Those are the ones numbered 1, 2, 3...etc. But there are also code modules behind the ThisWorkbook, behind each Sheet and behind each UserForm. These latter three types of modules are generally used for event code related directly to those particular objects. The public modules hold the control code that executes programs and functions. If you try to put a public procedure in a Sheet or UserForm module, it probably will throw an error when you try to run it, and vice versa with a sheet or form event procedure being put in a public module. You can write modular programs by breaking a long procedure into several small procedures where each program module performs a specific action and all of the small procedures are called and controlled from one master procedure. But these could all be in a public code module and in their respective sheet or form code modules as applicable. The modular programming simply means that you do not have one lengthy program which requires consecutive events that might have the same code written repeatedly over and over. With modular programming, you can use the same procedure more than once simply by calling it to execute when you need it. Again, don't confuse VBA code modules with modular progamming, even though there might be coincidental similarity. "Harold Good" wrote in message ... Hi, I'm trying to learn how to be better organized with code. I can't find anywhere why someone would want more than one module for general code. Yet I see Module 1, Module 2, etc. Is it because some code gets really long? Is it to better organize code, even if not long? How many modules do some of you pros end up with? Any tips on how to organize code into various modules, renaming them to be more meaningful, and organized. Thanks, Harold |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
I'm new to VBA - why more than one Module?
Per (PeteCresswell):
Depends on the app. Right now I'm looking at a bond trading application that has 37 modules. Oops!... RCI strikes again. I predicated my response on this being the MS Access NG. The portability thing is probably a red herring in Excel, since one can point many workbooks at a single code repository. -- PeteCresswell |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
I'm new to VBA - why more than one Module?
Wow, what a helpful lot of responses. Thank you very much to each of you
who shared from your years of experience. It has been an eye opener to me as I now think of some of the amateur code I've patched together, and how understanding how to organize it better will be a giant step forward. Some of the thick books I have on Excel VBA haven't explained multiple modules very well. Again, thanks for your help. Merry Christmas to all, Harold Harold Good wrote: Hi, I'm trying to learn how to be better organized with code. I can't find anywhere why someone would want more than one module for general code. Yet I see Module 1, Module 2, etc. Is it because some code gets really long? Is it to better organize code, even if not long? How many modules do some of you pros end up with? Any tips on how to organize code into various modules, renaming them to be more meaningful, and organized. Thanks, Harold |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
I'm new to VBA - why more than one Module?
There may actually be a physical limitation on the size of any module - 64K I
think but regardless of this the preceding responses are more important for good code design. Why are all of us being so helpful? Simply that it's really self-help as at some stage most of us have had to tidy up after individuals who have written lengthy linear code all in one module. -- Ken "Using Dbase dialects since 82" "Started with Visicalc in the same year" "Harold Good" wrote: Wow, what a helpful lot of responses. Thank you very much to each of you who shared from your years of experience. It has been an eye opener to me as I now think of some of the amateur code I've patched together, and how understanding how to organize it better will be a giant step forward. Some of the thick books I have on Excel VBA haven't explained multiple modules very well. Again, thanks for your help. Merry Christmas to all, Harold Harold Good wrote: Hi, I'm trying to learn how to be better organized with code. I can't find anywhere why someone would want more than one module for general code. Yet I see Module 1, Module 2, etc. Is it because some code gets really long? Is it to better organize code, even if not long? How many modules do some of you pros end up with? Any tips on how to organize code into various modules, renaming them to be more meaningful, and organized. Thanks, Harold . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
I'm new to VBA - why more than one Module?
Hello,
One small addition: If you need to clean up somebody else's code and if that guy had not used OPTION EXPLICIT (happens, unfortunately), you might want to create a new module starting with OPTION EXPLICIT. Then you clean up function by function and sub by sub: delete it from the old module and create it in the new one. Regards, Bernd |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
I'm new to VBA - why more than one Module?
These comments are all showing me I'd better learn how to do a better
job of it, lest others have to clean up my code down the road. Thanks again for all your help, Harold Bernd P wrote: Hello, One small addition: If you need to clean up somebody else's code and if that guy had not used OPTION EXPLICIT (happens, unfortunately), you might want to create a new module starting with OPTION EXPLICIT. Then you clean up function by function and sub by sub: delete it from the old module and create it in the new one. Regards, Bernd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compile Error in Hidden Module: Module 1 | Excel Programming | |||
VBA code in one Module referencing Public Variables Declared inanother Module failing | Excel Programming | |||
code in module A to not execute a Worksheet_SelectionChange sub of another module | Excel Discussion (Misc queries) | |||
Run worksheet module code from workbook module? | Excel Programming | |||
Variable from a sheet module in a class module in XL XP | Excel Programming |