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("lookup 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

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" <[email protected]> wrote in message
news:[email protected]...
> 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("lookup 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

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("lookup 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