Variables--Placement and Scope Philosophy

[Atroxell]

Hi,

I use Excel macros extensively, and have recently started migrating blocks of code into my personal.xlsb workbook and adding references to my projects. I am self-taught, so there are some practices I use that are probably not within acceptable standards, and this idea may be one of those.

In the process of editing and moving re-used code blocks to minimize reptition of code, I see that over the last few years I have been using the same or similar names for variables at the module level. For example, I work in the financial industry and use variables with names like "brName" and "brID" for "branch name" and "branch ID" almost all the time. They are commonly declared at the top of a module so that they can be passed into the various procedures.

I guess my question is less of a technical nature and more philosophical.

Would it be of any advantage for me to place the Dim statements in my personal workbook (and thereby doing it only once) as Public statements? Does anyone see or know a reason why I should not do it this way? Or is this one of those things considered bad form or practice for programming and be avoided at all costs? I know I would have to clear all variables in the beginning of any code I write in the future, but I figure that that would be a kind of way to "declare" the variables used in the module/procedure.

I'm just not sure if this is a good idea or practice and would like some other thoughts before I commit.

TIA,
Al

 

[tweedle]

As a former VB programmer; we would maintain two modules that went with almost everything (albeit the product was packaged exe and dll files and not as independently portable as Excel can be).

Anyway; Module pGlobals held all Publicly declared variables that could be used anywhere throughout the code. Module pConst held a bzillion Constants (Const) that could be used anywhere (mostly relative to Win-API calls, but we had our own set as well)

I will still do the same when I have a significant client-project in VBA.
I've gone to declaring all my array constructs in a module pArrayConst.
It's easier to know where to go when one needs to find the definition of something.
The drawback is; if it's not with the project; the code will bomb in spectacular fashion.

I have a LOT of little helpers at work now. (The common belief is that I actually spend my time compiling this stuff). Because I'm here, and I'll never port this code outside my desktop - I'm less concerned about a global portability.

Would it be of any advantage for me to place the Dim statements in my personal workbook (and thereby doing it only once) as Public statements?
Does anyone see or know a reason why I should not do it this way?
Or is this one of those things considered bad form or practice for programming and be avoided at all costs?
I know I would have to clear all variables in the beginning of any code I write in the future, but I figure that that would be a kind of way to "declare" the variables used in the module/procedure.
I'm just not sure if this is a good idea or practice and would like some other thoughts before I commit.

Advantage: Central repository of variables for re-use
Reason why not to?: Not really

I know I would have to clear all variables in the beginning of any code I write in the future

Not sure what you mean here. One can store them in a separate Module all by their lonesome selves. Copy the module to any new projects.

It's not a bad idea...nor is it a taboo practice. IMHO

 

[RoryA]

IMO, variables should have the least scope possible for them to function. Where possible, pass them directly between procedures; if not, declare them at module level for the procedures in that module; if that is not possible, use Public variables.

 

[xenou]

You could run into a confusing situation some day where you are calling code in one place that changes the public variable value (without realizing this is happening), and then when you return to the original procedure in the call stack your variable will be different and you may not know it - such code is difficult to debug. 95% or more of my code uses local (procedure level variables) - the lowest possible scope.
ξ

 

[Atroxell]

Thank you all. I knew that this thread would not result in a definitive course of action and would simply give me more to ponder.

tweedle--good points all, and similar to the methods I used in my db/web interface development days in my last job (not using VBA and over 500 pages of code). However, the content there was so specific that I built a module for variable declarations and it became second nature to use standardized variable names throughout the scripting. That's where my idea to centralize the declarations in VBA first came from. By "clearing the variables" I mean setting them to null, zero or blank at the beginning of the module/procedure where they are first used in order to ensure they only get populated with values that will be correct in that application. In my last job, I actually had a module that reset entire groups of variables (based on a parameter) to null so there would be no leftover values lurking to strike.

rorya--Also a good point and I know that declaring variables at their lowest levels is the common, accepted practice with VBA and elsewhere. That's how I am doing it now. But that is what I was really trying to pry into with my question--whether this was a good idea or not.

and xenou--you are in agreement with rorya. I completely understand this. I have crossed up variable values between procedures within the same module and I can see it easily happening when I am crossing between modules. That's part of the reason why I envision "clearing the variables" at the start of code. At least that way I don't have any legacy values floating around.

In all likelihood, I will remain with my current configuration as the one thing that really bugs me more than repetitive code and crossing up variables is debugging code that should be working. This is not something I will jump into blindly, and I will be thinking more about it before anything changes. Besides, the first rule of thumb may hold precedence here: If it works, don't fix it. So far, it all works just fine.

I have learned volumes from this site, and look forward to learning more everyday when I visit. I truly appreciate the time everyone has taken to answer my questions--whether sensible or otherwise.

Thank you all!