Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Organizing functions
Good afternoon everyone,
I've got more of a style question than technical one this time around. I'm currently assembling all my custom functions and subs into one project. I have been collecting some over the last couple of projects I've done, so I thought it might be a good idea to have the ones I use very often together. I am currently on 50-60 of them. The thing is that I've read so many different opinions about how to store them. Should I group them in different modules, or should I just create one big module and comment every sub I have? Is there a processing-time difference of having let's say 10 different modules or just having one big one? Second question: should I save it as an add-in or would you recommend me saving it as a normal spreadsheet? I'd appreciate any comment on this matter. Thanks guys ;o) kj |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Organizing functions
I would suggest categorising them, and any like functions in a category
(such as formatting functions, file access, etc.) and create modules per category. Name the modules, so you know the category. I strongly suggest not putting 50-60 in one module, maintenance is easier by breaking up. I know of no performance impact of any significance of either method, but too many functions can cause a problem. -- HTH RP (remove nothere from the email address if mailing direct) "Knut Dahl" wrote in message ... Good afternoon everyone, I've got more of a style question than technical one this time around. I'm currently assembling all my custom functions and subs into one project. I have been collecting some over the last couple of projects I've done, so I thought it might be a good idea to have the ones I use very often together. I am currently on 50-60 of them. The thing is that I've read so many different opinions about how to store them. Should I group them in different modules, or should I just create one big module and comment every sub I have? Is there a processing-time difference of having let's say 10 different modules or just having one big one? Second question: should I save it as an add-in or would you recommend me saving it as a normal spreadsheet? I'd appreciate any comment on this matter. Thanks guys ;o) kj |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Organizing functions
My assumption here is that these are utility type functions. Let head through
this one question at a time. How should they be organized. Grouping items togethter by functionality is a great idea. It doens't change the processing time one little bit, but from an understandability stand point it makes a lot of sense. If you have a module with a few different text manipulation functions, then group them together. If there is a problem with a text function you know immediately right where to look. Grouping into a number of smaller categories also makes a lot of sense from the standpoint of code re-use. I have a number of different modules of utitlity type functions that I import into new projects. One for Error handling, one for protecting and unprotecting, one for ADO DB recordsets... Creating a new project becomes "plug and play". In terms of commenting. Always, always, always comment. There should be some sort of a preable at the beginning of your function indicating what it is, what it does and how it is used. You should not have to read the code to understand the function. When you write code you should comment throughout. I will rarely go more than 5 lines without a comment. All of my variables are commented. My code can be read by anyone and understood. If I come back to something 6 months later to modify, it takes me no time at all to get up to speed and make the changes. Should it be in an add-in. If it is something that you will be using all of the time then probably. Addin code is a little faster because it is pre-compiled. Just my two cents... "Knut Dahl" wrote: Good afternoon everyone, I've got more of a style question than technical one this time around. I'm currently assembling all my custom functions and subs into one project. I have been collecting some over the last couple of projects I've done, so I thought it might be a good idea to have the ones I use very often together. I am currently on 50-60 of them. The thing is that I've read so many different opinions about how to store them. Should I group them in different modules, or should I just create one big module and comment every sub I have? Is there a processing-time difference of having let's say 10 different modules or just having one big one? Second question: should I save it as an add-in or would you recommend me saving it as a normal spreadsheet? I'd appreciate any comment on this matter. Thanks guys ;o) kj |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Organizing functions
Thanks guys.
Was more or less what I thought. Just wanted it confirmed by the experts ;o) kj "Knut Dahl" wrote in message ... Good afternoon everyone, I've got more of a style question than technical one this time around. I'm currently assembling all my custom functions and subs into one project. I have been collecting some over the last couple of projects I've done, so I thought it might be a good idea to have the ones I use very often together. I am currently on 50-60 of them. The thing is that I've read so many different opinions about how to store them. Should I group them in different modules, or should I just create one big module and comment every sub I have? Is there a processing-time difference of having let's say 10 different modules or just having one big one? Second question: should I save it as an add-in or would you recommend me saving it as a normal spreadsheet? I'd appreciate any comment on this matter. Thanks guys ;o) kj |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Organizing functions
Jim Thomlinson wrote: All of my variables are commented. Perhaps, then, you are not using intuitive names for your variables. I think code should be 'self documenting' where possible. I'd sooner use: Dim TotalIncomeByTaxScheduleOfLastEmployeeAddedToGrid As Currency than have to resort to something like: ' Keep a running total of income by Tax Schedule ' of last employee added to the grid Dim Total8 As Currency on the basis that it makes it harder to maintain the code if I have to remember what distinguishes Total8 from Total1, Total2, Total3 etc. Jamie. -- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Organizing functions
Jamie,
How can you 'criticise' anyone for using comments<g I am sure Jim uses meaningful names as well, just as I do (see how many times I use the variable in my code<bg), but comments can also help. To fully explain, a meaningful name could get ridiculously long, as in your example, I would rather not use TotalIncomeByTaxScheduleOfLastEmployeeAddedToGrid. a name and comments works better for me, as an example '-------------------------------------------------------------------- ' Constant: CFP_Icon ' Usage: Defines if an icon is shown in the toolbar What annoys me is shortening (I note happily that you don't), such as TotIncByTaxSchedOfLastEmpAddToGrid, they are the worst of all. I would add that I find some comments useless, such as adoConn.Close 'close the connection If comments are not used effectively, and just sprinkled about stating the obvious, another developer may well decide that the comments do not help, and so ignore them. And of course, they will then miss the one comment that is truly enlightening. -- HTH RP (remove nothere from the email address if mailing direct) "Jamie Collins" wrote in message oups.com... Jim Thomlinson wrote: All of my variables are commented. Perhaps, then, you are not using intuitive names for your variables. I think code should be 'self documenting' where possible. I'd sooner use: Dim TotalIncomeByTaxScheduleOfLastEmployeeAddedToGrid As Currency than have to resort to something like: ' Keep a running total of income by Tax Schedule ' of last employee added to the grid Dim Total8 As Currency on the basis that it makes it harder to maintain the code if I have to remember what distinguishes Total8 from Total1, Total2, Total3 etc. Jamie. -- |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Organizing functions
Bob Phillips wrote: How can you 'criticise' anyone for using comments<g Sorry to shock you, Bob <vbg. I agree everything you've said here about comments. It's just that Jim said he comments *all* of his variables, so presumably we'd see either something you (and me) would call 'useless' e.g. ' Declare an ADO Connection object Dim adoConn As ADODB.Connection or something I'd called a non-meaningful name e.g. Dim var1 As String ' Connection string value Dim var2 As ADODB.Connection ' ADO connection object Jamie. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need Help Organizing a Formula? | Excel Worksheet Functions | |||
Help organizing date | Excel Discussion (Misc queries) | |||
Help with organizing data | Excel Discussion (Misc queries) | |||
Organizing Data | Excel Worksheet Functions | |||
Organizing words, willing to pay $ 100 | Excel Programming |