Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best place to declare variables
Where is the best place to declare variables, in terms of
memory utilization, etc. if the same variable names are used in different modules, although their values do not (always) need to be passed between modules. ex: Module A is used to summarize data on Sheet1 of a Workbook into Sheet2 Module B is used to summarize data on Sheet3 of a Workbook into Sheet4 There are no common functions/subs between Module A and Module B (Module C has functions/subs called by both Module A and B - but that could be a red herring) In Module A and Module B I use the same variable names - just for consistency. Is it more efficient to declare the variables with DIM in Module A & Module B or to declare them as Public elsewhere? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best place to declare variables
Taras wrote:
Is it more efficient to declare the variables with DIM in Module A & Module B or to declare them as Public elsewhere? Module-level variables (whether Public or not) have space permanently allocated. So having 2 sets will consume twice as much space. However, I would not advocate using Public variables in case you end up changing the sequence in which things are done and get conflicting usage of the same variable between procedures in different modules. Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - reply in newsgroup |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best place to declare variables
Bill,
Thanks for your input. I think that as long as I ensure I initialize the variable in the first sub that I use it in, I should be OK. What is your opinion on declaring the variables in a sub that is executed when the spreadsheet is opened so that they would be available to any macro following. The sheet is rarely used without running some macro. Taras -----Original Message----- Taras wrote: Is it more efficient to declare the variables with DIM in Module A & Module B or to declare them as Public elsewhere? Module-level variables (whether Public or not) have space permanently allocated. So having 2 sets will consume twice as much space. However, I would not advocate using Public variables in case you end up changing the sequence in which things are done and get conflicting usage of the same variable between procedures in different modules. Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - reply in newsgroup . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best place to declare variables
Variables should be declared with the narrowest scope possible.
For example, if you have variables i and j, commonly used for indexing, in many procedures, each procedure should declared its own i and j. -- http://www.standards.com/; See Howard Kaikow's web site. "Taras" wrote in message ... Where is the best place to declare variables, in terms of memory utilization, etc. if the same variable names are used in different modules, although their values do not (always) need to be passed between modules. ex: Module A is used to summarize data on Sheet1 of a Workbook into Sheet2 Module B is used to summarize data on Sheet3 of a Workbook into Sheet4 There are no common functions/subs between Module A and Module B (Module C has functions/subs called by both Module A and B - but that could be a red herring) In Module A and Module B I use the same variable names - just for consistency. Is it more efficient to declare the variables with DIM in Module A & Module B or to declare them as Public elsewhere? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best place to declare variables
I know hardly anything about XL's memory utilization, but I'll take
liberty with your use of "etc." in your question. It's personal opinion only: Variable memory is no longer a concern for me in any but the most extreme circumstances. Nor is speed (of accessing variables, at least). What I focus on is maintenance - it's cheaper for you to add a GB of RAM than to have me spend an extra few hours trying to decipher and debug your code. I use three rules in commercial work: 1) All variables are local to their procedure. Variables must be passed between procedures as arguments. There are no exceptions to this rule. 2) In extreme circumstances, or if I can't think of any other way to do it and the deadline for delivery is within 12 hours, Rule 1 may be violated and a global variable used. In this case, procedures sharing the global must be grouped in the same module and globals must be private to that module. There must be no duplication of global variable names between modules. Procedure variable names must not override globals. There are *absolutely* no exceptions to this rule. 3) At gunpoint, or when forced by customer specifications, Rule 2 may be violated, but *all* globals must be public and declared in a separate module. Global variable names are perforce not duplicated. These rules assure me that when I go to modify a client's code six months or a year from now, I (or another developer) won't spend any more time than necessary chasing variable names and scope. In article , "Taras" wrote: Where is the best place to declare variables, in terms of memory utilization, etc. if the same variable names are used in different modules, although their values do not (always) need to be passed between modules. ex: Module A is used to summarize data on Sheet1 of a Workbook into Sheet2 Module B is used to summarize data on Sheet3 of a Workbook into Sheet4 There are no common functions/subs between Module A and Module B (Module C has functions/subs called by both Module A and B - but that could be a red herring) In Module A and Module B I use the same variable names - just for consistency. Is it more efficient to declare the variables with DIM in Module A & Module B or to declare them as Public elsewhere? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best place to declare variables
Taras wrote:
What is your opinion on declaring the variables in a sub that is executed when the spreadsheet is opened so that they would be available to any macro following. If you really mean *declaring* the variables, it won't work. Variables declared within a procedure are available only within that procedure. Because module-level variables lose their values when the project is recompiled (e.g. after you click the Reset button in the VB editor or the End button in a VB error message, or execute an End statement etc), I try to avoid relying on their values between one macro run and the next. If I need to have such module-level variables I will use a procedure to set them all, including a Boolean ValidVars: Sub CheckVars() If ValidVars Then Exit Sub ' set all module level variables ValidVars = True End Sub and I call CheckVars from the start of each procedure that can be run as the result of a user action. Hope this helps Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - reply in newsgroup |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best place to declare variables
your three rules.
Generally, I find them very honorable. I follow (1) where possible and have NEVER yet resorted to (3), however I'm left wondering about (2): how do your classes maintain their property values if you don't use private module-level variables? "J.E. McGimpsey" wrote in message ... I know hardly anything about XL's memory utilization, but I'll take liberty with your use of "etc." in your question. It's personal opinion only: Variable memory is no longer a concern for me in any but the most extreme circumstances. Nor is speed (of accessing variables, at least). What I focus on is maintenance - it's cheaper for you to add a GB of RAM than to have me spend an extra few hours trying to decipher and debug your code. I use three rules in commercial work: 1) All variables are local to their procedure. Variables must be passed between procedures as arguments. There are no exceptions to this rule. 2) In extreme circumstances, or if I can't think of any other way to do it and the deadline for delivery is within 12 hours, Rule 1 may be violated and a global variable used. In this case, procedures sharing the global must be grouped in the same module and globals must be private to that module. There must be no duplication of global variable names between modules. Procedure variable names must not override globals. There are *absolutely* no exceptions to this rule. 3) At gunpoint, or when forced by customer specifications, Rule 2 may be violated, but *all* globals must be public and declared in a separate module. Global variable names are perforce not duplicated. These rules assure me that when I go to modify a client's code six months or a year from now, I (or another developer) won't spend any more time than necessary chasing variable names and scope. In article , "Taras" wrote: Where is the best place to declare variables, in terms of memory utilization, etc. if the same variable names are used in different modules, although their values do not (always) need to be passed between modules. ex: Module A is used to summarize data on Sheet1 of a Workbook into Sheet2 Module B is used to summarize data on Sheet3 of a Workbook into Sheet4 There are no common functions/subs between Module A and Module B (Module C has functions/subs called by both Module A and B - but that could be a red herring) In Module A and Module B I use the same variable names - just for consistency. Is it more efficient to declare the variables with DIM in Module A & Module B or to declare them as Public elsewhere? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best place to declare variables
Reminds me of:
In 1942, Isaac Asimov came with three laws of Robotics. The Three Laws of Robotics a 1. A robot may not injure a human being, or, through inaction, allow a human being to come to harm. 2. A robot must obey the orders given it by human beings except where such orders would conflict with the First Law. 3. A robot must protect its own existence as long as such protection does not conflict with the First or Second Law. Subsequently a fourth law was added, the the "Zeroth Law", which is: 0. A robot may not injure humanity or, through inaction, allow humanity to come to harm. "J.E. McGimpsey" wrote: I know hardly anything about XL's memory utilization, but I'll take liberty with your use of "etc." in your question. It's personal opinion only: Variable memory is no longer a concern for me in any but the most extreme circumstances. Nor is speed (of accessing variables, at least). What I focus on is maintenance - it's cheaper for you to add a GB of RAM than to have me spend an extra few hours trying to decipher and debug your code. I use three rules in commercial work: 1) All variables are local to their procedure. Variables must be passed between procedures as arguments. There are no exceptions to this rule. 2) In extreme circumstances, or if I can't think of any other way to do it and the deadline for delivery is within 12 hours, Rule 1 may be violated and a global variable used. In this case, procedures sharing the global must be grouped in the same module and globals must be private to that module. There must be no duplication of global variable names between modules. Procedure variable names must not override globals. There are *absolutely* no exceptions to this rule. 3) At gunpoint, or when forced by customer specifications, Rule 2 may be violated, but *all* globals must be public and declared in a separate module. Global variable names are perforce not duplicated. These rules assure me that when I go to modify a client's code six months or a year from now, I (or another developer) won't spend any more time than necessary chasing variable names and scope. In article , "Taras" wrote: Where is the best place to declare variables, in terms of memory utilization, etc. if the same variable names are used in different modules, although their values do not (always) need to be passed between modules. ex: Module A is used to summarize data on Sheet1 of a Workbook into Sheet2 Module B is used to summarize data on Sheet3 of a Workbook into Sheet4 There are no common functions/subs between Module A and Module B (Module C has functions/subs called by both Module A and B - but that could be a red herring) In Module A and Module B I use the same variable names - just for consistency. Is it more efficient to declare the variables with DIM in Module A & Module B or to declare them as Public elsewhere? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Declare variables to a code? | Excel Discussion (Misc queries) | |||
Declare Variables in Array | Excel Discussion (Misc queries) | |||
Declare and Set Public variables | Excel Discussion (Misc queries) | |||
how to declare local variables for Excel.Workbook at runtime. | Excel Discussion (Misc queries) | |||
How to efficiently declare variables | Excel Discussion (Misc queries) |