Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
use of module level variables
I am looking for a bit of clarification on the proper use of module level
variables. Lets say a variable named myfirstrange is defined as a range at the beginning of a module before any subroutines. Now let's say that the first subroutine is called (from a different module) and stores the following to this variable: myfirstrange=worksheets("sheet1").range("a:a") Next, a second subroutine is called (within the same module as the variables, but again from a different calling module), is the value of myfirstrange able to be used in the second subroutine as it was defined in the first? i.e. in the second subroutine, the only use of myfirstrange is as follows: findrow=application.worksheetfunction.match("looku p value",myfirstrange,0) Will the second subroutine recognize the actual value of the variable. Additionally, is there any reason not to do it this way, i.e. it reduces performance of the code, etc. And finally, is it ok to call the two subroutines from a separate module? The reason I am asking this question is that I would like to avoid having to "send" a variable to a second subroutine by defining it at the module level, as I would be sending about 100 variables, and this gets confusing at times. Would appreciate any insight. Thanks, Jason Falls |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
use of module level variables
Jason,
A module level variable is available to all procedures and functions in that module. Changing it in any of the procedures or functions in that module will be recognized by any of the other procedures or functions in that module. -- HTH RP (remove nothere from the email address if mailing direct) "TxRaistlin" wrote in message ... I am looking for a bit of clarification on the proper use of module level variables. Lets say a variable named myfirstrange is defined as a range at the beginning of a module before any subroutines. Now let's say that the first subroutine is called (from a different module) and stores the following to this variable: myfirstrange=worksheets("sheet1").range("a:a") Next, a second subroutine is called (within the same module as the variables, but again from a different calling module), is the value of myfirstrange able to be used in the second subroutine as it was defined in the first? i.e. in the second subroutine, the only use of myfirstrange is as follows: findrow=application.worksheetfunction.match("looku p value",myfirstrange,0) Will the second subroutine recognize the actual value of the variable. Additionally, is there any reason not to do it this way, i.e. it reduces performance of the code, etc. And finally, is it ok to call the two subroutines from a separate module? The reason I am asking this question is that I would like to avoid having to "send" a variable to a second subroutine by defining it at the module level, as I would be sending about 100 variables, and this gets confusing at times. Would appreciate any insight. Thanks, Jason Falls |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
use of module level variables
A lot to answer here so maybe just a quick discussion. Module level (or
global) variables should be avoided as much as is possible. Sometimes passing variables is difficult and I feel your pain. That having been said the problem with publicly declared variables is that it can be very difficult to know their value at any given time. If a bunch of different procedures can change the variable then depending who had it last the value may or may not be what you think it is. This is a beast to debug because once you determine that your variable is incorrect, you now have the task of trying to figure out who modified it last. There is a time and place for public variables but every time you create one you really need to ask if it is absolutely necessary. Here are some of the rules that I live by: Keep eveything declared as privately as is possible. Pass by value unless absolutely necessary (the default is byRef which is dangerous) Avoid side effects as much as is possible. This includes ending procedures on sheets and or in cells other than where they started. HTH "TxRaistlin" wrote: I am looking for a bit of clarification on the proper use of module level variables. Lets say a variable named myfirstrange is defined as a range at the beginning of a module before any subroutines. Now let's say that the first subroutine is called (from a different module) and stores the following to this variable: myfirstrange=worksheets("sheet1").range("a:a") Next, a second subroutine is called (within the same module as the variables, but again from a different calling module), is the value of myfirstrange able to be used in the second subroutine as it was defined in the first? i.e. in the second subroutine, the only use of myfirstrange is as follows: findrow=application.worksheetfunction.match("looku p value",myfirstrange,0) Will the second subroutine recognize the actual value of the variable. Additionally, is there any reason not to do it this way, i.e. it reduces performance of the code, etc. And finally, is it ok to call the two subroutines from a separate module? The reason I am asking this question is that I would like to avoid having to "send" a variable to a second subroutine by defining it at the module level, as I would be sending about 100 variables, and this gets confusing at times. Would appreciate any insight. Thanks, Jason Falls |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Declaring variables in Module vs. Public | Excel Discussion (Misc queries) | |||
Declaring array constants at module level | Excel Programming | |||
referencing at module level | Excel Programming | |||
Passing variables from module to userform | Excel Programming | |||
module-level variable lifetime | Excel Programming |