Working with SUBs

[Rick Charnes]

Kinda new to writing subroutines within VBA. I didn't realize that all
the module-level variables declared in the body of the procedure are not
available to you in the SUB. I guess I have to pass any that are needed
in the SUB, as arguments? Is there another technique?

 

[Pranav Vaidya]

Hi Rick,

One way is that you pass the specific variables among the subs. However
there are various scoping rules for the variables in VBA (Word and Excel)
Check out these links to learn more about it-

http://www.ozgrid.com/VBA/variable-scope-lifetime.htm
http://support.microsoft.com/kb/183369
http://www.ozgrid.com/VBA/variables.htm

 

[Jay Freedman]

Here's another link:

http://www.word.mvps.org/FAQs/MacrosVBA/ProcArguments.htm

One point that I didn't see mentioned in any of these articles: Although
declaring a variable at module or global scope seems to be an easy way to
get information to all the procedures that use it, that is usually not a
good choice. The problem is that such variables can be changed by any
procedure that can see them, and that's a breeding ground for bugs. It's
much better from the standpoint of maintainability to pass arguments, using
ByRef and ByVal modifiers when necessary, and to use functions (which return
a value) when that makes sense.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

 

[Rick Charnes]

And when you say 'pass' the variables among the subs, you mean as
arguments, right?

 

[Jonathan West]

Kinda new to writing subroutines within VBA. I didn't realize that all
the module-level variables declared in the body of the procedure are not
available to you in the SUB.

Before you start confusing yourself and everyone else here, let's get some
terminology straight.

Module-level variables are declared *before* the first Sub or Function in
the module. They are declared using the keyword Private rather than Dim. A
module level variable can be used by any routine within the same module.

Global variables are also declared before the first Sub or Function of a
module, but using the Public keyword. A global variable can be used by any
routine in any module of the project.

Procedure-level variables are declared after the Sub or Function statement
at the start of a procedure, and are only available within that procedure.

I guess I have to pass any that are needed
in the SUB, as arguments?

That is usually the best way.

Is there another technique?

Yes, you can use global or module level variables, but I would recommend
being very sparing with their use. If you declare a variable locally and
pass it as a parameter to another routine, you know that there is only one
point at which the two routines interact. With a module-level or global
variable, any routine can affect the operation of any other simply by
changing the value of a common variable, and as there is no limitation as to
how and where this can happen, bug-hunting can take on needle-in-a-haystack
dimensions

You might be interested in this article which describes some additional
techniques for writing maintainable code.

The art of defensive programming
http://www.word.mvps.org/FAQs/MacrosVBA/MaintainableCode.htm