Global vs Local variables

[Ken Loomis]

In the project I am working on there are a number of variables that I have
made local, but now wonder if they might not be better suited as global.

For example, the sortByName macro does just what its name implies and sorts
the worksheet by name based upon the name being in column 2. I use a local
variable to store that, i.e., nameCol = 2. But there are other macros that
need to know that the name column is column 2. In this project I always had
nameCol as a local variable for each macro that needed it.

But now the report format is changing and I need to change some of those
column numbers. Not such big deal, since I just search for "nameCol = 2" and
replace with "nameCol = 3" and that fixes that.

But I just thought it might be a little easier to change and easier to read,
if I used global variables for these types of values.

Can anybody tell me if the advantages or disadvantages are significant for
local vs. global variables in VBA? And, if so, what are those?

 

[Myrna Larson]

In this situation, I set them up as public constants in one of the modules.
One cited disadvantage of a project-level variable is that any sub in any
module can change its value. But if you make these constants, that problem is
eliminated.