Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best Practices Question
What do you all consider to be best practices in VBA coding
1) Creating a called procedure with multiple arguments that can be reused 2) Creating multiple called procedures without arguments that are specific to each time it's called? I know what I think, but want to get your take on it. Thanks, Barb Reinhardt |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best Practices Question
Barb, it somewhat varies upon the application. In most cases it is best to
have re-usable code. The more time you can save yourself further down the road the better. One instance I can think of where this might not be the right option is in a function that is runs for a long period of time, you then want to write all the code to be as fast as possible. -- Charles Chickering "A good example is twice the value of good advice." "Barb Reinhardt" wrote: What do you all consider to be best practices in VBA coding 1) Creating a called procedure with multiple arguments that can be reused 2) Creating multiple called procedures without arguments that are specific to each time it's called? I know what I think, but want to get your take on it. Thanks, Barb Reinhardt |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best Practices Question
All else being equal (a rather broad assumption), I would write a single
procedure that takes either optional arguments or a variable number of arguments using a ParamArray. The proc would take action based on which arguments are included and what their values are. I think this makes the proc much better suited to reuse in a variety of ways. The only caveat that I would offer is that the function and its parameters must be very well documented to allow for real reuse across multiple projects. I have a rather large library of code that I include in projects either as modules or as single procedures. I have reused the same code in many projects over the years. Code reuse is a good thing. Of course, the code must be well designed and documented, but it is worth the effort to get a library of plug and play procedures. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Barb Reinhardt" wrote in message ... What do you all consider to be best practices in VBA coding 1) Creating a called procedure with multiple arguments that can be reused 2) Creating multiple called procedures without arguments that are specific to each time it's called? I know what I think, but want to get your take on it. Thanks, Barb Reinhardt |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best Practices Question
What about variable declaration? Would you make them all public or declare
them in the procedures as necessary. My preference is to only declare public variables if they are needed. Thanks, Barb Reinhardt "Chip Pearson" wrote: All else being equal (a rather broad assumption), I would write a single procedure that takes either optional arguments or a variable number of arguments using a ParamArray. The proc would take action based on which arguments are included and what their values are. I think this makes the proc much better suited to reuse in a variety of ways. The only caveat that I would offer is that the function and its parameters must be very well documented to allow for real reuse across multiple projects. I have a rather large library of code that I include in projects either as modules or as single procedures. I have reused the same code in many projects over the years. Code reuse is a good thing. Of course, the code must be well designed and documented, but it is worth the effort to get a library of plug and play procedures. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Barb Reinhardt" wrote in message ... What do you all consider to be best practices in VBA coding 1) Creating a called procedure with multiple arguments that can be reused 2) Creating multiple called procedures without arguments that are specific to each time it's called? I know what I think, but want to get your take on it. Thanks, Barb Reinhardt |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best Practices Question
Per Barb Reinhardt:
What about variable declaration? Would you make them all public or declare them in the procedures as necessary. My preference is to only declare public variables if they are needed. I keep variables as local as I can. Only uses I can think of off the top of my head for globals a 1) General purpose constants like runtime error codes that I can't find the proper enumerated variable names 2) Semaphores used by modal dialogs to tell the calling routine that the dialog ended normally and/or to communicate values back from the dialog. 3 Definitions of Lookup table values that are used across more than one module. On the passed parms thing, I see it as a tradeoff between simplicity and maintainability. In general, I try to keep my routines short and dedicated to a single function. I've got plenty of them that exceed a 1900 x 1200 screen in size, but one does, I start looking at it to see if I'm mixing too much functionality into one routine. Having a single routine perform a single function might involve passing quite a few parms - like yield calculations, which are performed differently using different types of data for different security types. In that case, I'll pass a YieldTypeID and whatever fields are needed to cover all calcs and then case out on the ID - where somebody else might have a different routine for each Case. e.g. "Yield_Calculate() instead of a dozen different flavors depending on YieldTypeID. The second approach is simpler in that each function has less code in it. But I find one-stop-shopping easier to understand/maintain and would implement the first. Others may see it differently. -- PeteCresswell |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best Practices Question
What about variable declaration? Would you make them all public or declare them in the procedures as necessary. My preference is to only declare public variables if they are needed. I don't use Public variables unless absolutely required and there is no other way to accomplish the task at hand. In code I design for my libraries, I might use a module-level Private variable, but only in cases when the code is designed to be imported as a complete, stand-alone module. If a proc is designed to be imported/copied in as only a single procedure, not a full module, that code never relies on the existence of anything at all. Anything it needs (e.g., a database connection, an FSO, or whatever) is passed in as a parameter. In general, and there are exceptions, nothing should rely on anything in its parent. Thus, a procedure should not rely on the existence of anything in its module, and a module should not rely on anything at the project level (such as another module). There are exceptions, of course, but my general rule is to make everything as self-contained as possible. If something is required by the proc, it should passed into the proc, not declared as a Public variable. One exception that comes to mind are constants that are used throughout a project, things like C_APP_NAME, C_APP_VERSION, C_MSGBOX_TITLE and so on, things that you want to keep constant throughout a project. In much of my work, I import complete module files into the project to provide support functions for the primary code. For example, I have a bas file named modArraryUtilityFunctions that contains around 50 array-related utility functions. I import the entire module to the project. Since the procedures call one another, it is not practical to import only individual procedures from the file. All this is not to say that Public variables should never be used. There are cases in which it makes good sense to use a Public variable. For example, if you are designing an app centered around a database connection, say to SQL Server, it would make sense to have one Public variable, Public TheDataBase As ADO.Connection, and have all the code reference that public variable, rather than having to pass around a reference to the database from one proc to another. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Barb Reinhardt" wrote in message ... What about variable declaration? Would you make them all public or declare them in the procedures as necessary. My preference is to only declare public variables if they are needed. Thanks, Barb Reinhardt "Chip Pearson" wrote: All else being equal (a rather broad assumption), I would write a single procedure that takes either optional arguments or a variable number of arguments using a ParamArray. The proc would take action based on which arguments are included and what their values are. I think this makes the proc much better suited to reuse in a variety of ways. The only caveat that I would offer is that the function and its parameters must be very well documented to allow for real reuse across multiple projects. I have a rather large library of code that I include in projects either as modules or as single procedures. I have reused the same code in many projects over the years. Code reuse is a good thing. Of course, the code must be well designed and documented, but it is worth the effort to get a library of plug and play procedures. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Barb Reinhardt" wrote in message ... What do you all consider to be best practices in VBA coding 1) Creating a called procedure with multiple arguments that can be reused 2) Creating multiple called procedures without arguments that are specific to each time it's called? I know what I think, but want to get your take on it. Thanks, Barb Reinhardt |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best Practices Question
Just to add, I think that the term that is most applicable here is called
"coupling", which is how closely related a function/procedure is to another. The more tightly coupled, or more dependent a function is on another, the less reusable and flexible it is. My opinion is loosely-coupled is better (as I gather you already agree). -- Tim Zych SF, CA "Barb Reinhardt" wrote in message ... What do you all consider to be best practices in VBA coding 1) Creating a called procedure with multiple arguments that can be reused 2) Creating multiple called procedures without arguments that are specific to each time it's called? I know what I think, but want to get your take on it. Thanks, Barb Reinhardt |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Best Practices (Counter) | Excel Discussion (Misc queries) | |||
Comparing Spreadsheets - best practices | Excel Discussion (Misc queries) | |||
Task pane best practices | Excel Programming | |||
best practices question | Excel Programming | |||
Best practices pivot using SQL-sourced table? | Excel Programming |