Best practices with variables?

[davidam]

Hello All,
After seeing my program get lost a few times when it was instructed to depart a sheet and then come back to that sheet and return to the cell that was active when it left...I decided that variables were necessary. They really seem to clear up this problem. I would like to ask a few questions if I may.
1. It seems clear that variables are sub or function specific...you cannot reference them anywhere except in the procedure that birthed them. Is this always correct?
2. Would it be bad practice to use the same variable names in another procedure, or is this asking for trouble?
3. Is it always necessary to dimension a variable before using it?
That is all for now.
Thanks,
David

 

[VoG]

1. If you declare the variables at the top of a module before any Subs they will be available to all subs in the module. If you do similar but declare them as Public they will be available to the whole project.

2. If they refer to similar things (like LR for Last Row) I can't see any harm.

3. If you don't specify a data type it will be dimensioned as Variant.

 

[Norie]

David

It's not always necessary to dim variables, but it's a good idea.

It's also a good idea, but not necessary, to add Option Explicit at the top of each module.

That'll make sure you do declare (dim) all your variables and can also be helpful weeding out typos.

 

[JackDanIce]

Hi,

Some thoughts to below:

1. Depends if you're declaring a local or global variable. Local variables are local to that procedure only, global can be referenced from multiple procedures. You can pass arguments too from one procedure to another as well.

2. Not necessarily bad practise, but really depends on what the procedure is doing and how well you understand your code. For example in most of my FOR NEXT loops, I use 'i' as my counter variable and then in other procedures, I use 'i' to store the value of the last used row in a column

3. It's better to dimenionsalise the variable and to declare what data type it is. It means when evaluating the code, memory allocation can be better allocated dependent on the variable type, otherwise it will default as type VARIANT, which may not be ideal, depending on the resources of the computer your macro is running on

 

[davidam]

One thing that I would like to be able to do is this:
Starting in workbook A; find a range (dynamic) inside workbook B and setr this as a variable; and then be able to reference it later on in Workbook A.
I gather I would Dim a global variable at the top of the module (where? ), set it while inside workbook B, and then be able to reference it when I am back in Workbook A?

 

[Norie]

When, where, and how are you going to use this variable?

 

[davidam]

Another issue is that I call procedures from other modules...can I create dynamic variables that are available to all modules and procedures?

 

[davidam]

To Norie's question, here is an example

Worksheets("CChart").Activate
Range("A38").Select
Workbooks("Results.xls").Activate
ActiveSheet.Unprotect Password:=PW
Range(ActiveCell.Offset(0, 0).Address & ":" & ActiveCell.Offset(87, 77).Address).Select

I am working from Choose.xls: as you can see I have activated CChart.xls and selected Range A38 ( and hope that it is still active when I return to this workbook, I then activate Results.xls (where I recently set the active cell) and copy a range based on that active cell. I think I need to do a better job of tracking these ranges.

 

[Norie]

David

If this is all going to be in code then it probably would be a good idea to introduce some variables.

You could use them for the workbooks/worksheets/ranges etc.

Then you wouldn't need to activate/select anything.

What ranges do you want to copy from/to?

 

[davidam]

In this particular example I copy the range in Results.xls, then go back to CCharts and paste in on to the active cell...all directed from a different workbook. What I am understanding from you all is that I could do something like this:
Declare actv1 and actv2 as public variables and they would retain workbook, worksheet and cell location

Worksheets("CChart").Activate
Range("A38").Select
Set actv1 = ActiveCell
Workbooks("Results.xls").Activate
ActiveSheet.Unprotect Password:=PW
Range(actv2.Offset(0, 0).Address & ":" & ActiveCell.Offset(87, 77).Address).Select
Selection.Copy
Workbooks("CCharts.xls").Activate
actv1.Select
ActiveSheet.Paste

Not exactly sure where/how to declare these variables, and if they are/should be available to whole project or just module, however.