Advice needed! scope considerations...

A question for the experts: I have a module with several routines that need to read data from several worksheets. Is it 'better' to have the sheet objects declared with module level scope, or should I declare them locally in my main procedure and pass them about as parameters to the functions? Declared at module it seems easier to code and read, but I read somewhere that module level variables makes code less efficient...

You can name the sheets directly in the IDE. The defaults are SheetX. Select the sheet in the IDE and rename it in the properties window. You can then use these names to refer to the sheets in code:

Code:

---

Sheet1.Range("a1").Value=x

---

Regarding your question though I would say 9 times out of 10 it's probably better to do whatever is easiest to code! If you are using them alot then declaring global variables will makie things much easier to read and edit at a later time. Make sure that you destroy the variables when you have finished with them though by setting any objects to nothing after the last use.

:)

While it is always good to make code efficient, most of the time when certain things are less efficient than others, the speed difference is negligable.

My preference is to avoid using global variables and just pass the variables to each Sub as needed. So basically you just need to decide what you like best in this case.

Hi,

I agree with Jacob, the time cost of the difference in efficiency in this case is likely to be negligible (if you were doing a loop with thousands of iterations, for example, then you'd want to get the efficiency right).

That said, as you've asked for opinions, I feel it is 'better' to pass arguments to routines rather than using global/public variables. It just makes for safer, more self-contained code which will provide you with less headaches in the long run.

Thanks for the input guys, I've gone down the locally declared route, I think it does actually make more sense as it's making me naturally write more abstract code, eg for sorting routines etc, which I can reuse.