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

 

[Norie]

Without knowing more about the whole thing it's hard to say if you need global/public variables.

I don't think they would be needed for the code you've posted though.

Anyway, I'm afraid it's still not clear, to me anyway, where you are copying from/to.

You select a cell on the worksheet 'CChart', then you activate the other workbook.

Then you seem to select something based on a variable actv2, ActiveCell and a other things.

I think I know what direction the copy is going - from the active sheet of the workbook 'Results.xls' to A38 on worksheet 'CChart'.

The following code is how you can copy from one workbook to another without activating/selecting.

There are only 2 variables in it, but you could have variables for the workbooks and worksheets.

Dim rngDst As Range
Dim rngSrc As Range
 
    ' set reference to destination range
    Set rngDst = ThisWorkbook.Worksheets("CChart").Range("A38")
 
    ' set refernce to source range
 
    Set rngSrc = Workbooks("Results.xls").ActiveSheet.Range("A1:F100")
 
 
    rngSrc.Copy rngDst

 

[davidam]

It must be frustrating to educate newbies like me...but I am making progress here and I do appreciate the help tremendously!

Yes, I can see that it would be more efficient eliminate the select and copy. I used to do a lot of select/copy/paste and I avoid it now...just was not clear how to do it here but I can see where the use of variables makes it easier...thanks for this.

Set rngSrc = Workbooks("Results.xls").ActiveSheet.Range("A1:F100")
This would not work because the range that I am copying changes all of the time...it is dependant upon a search for a particular value.
That is why I need the ActiveCell offset ( my eg had a typo; the range should have specified actv2.offset twice)...
but is there any reason I could not Set rngSrc = actv2.offset(0,0).Address & ":" & actv2.Offset(87,77).Address ?

I am just trying to get clear on the use of public variables. If I call a procedure that resides in a different module...then I should use a public variable if I need to reference it inside that procedure. This project has about 15 modules and I would need to do some work to figure out if that ever happens. In the cases where I am moving data from one workbook to another, I do not see any downside to just making these variables public...just need to be very careful in keeping track of them.

 

[Norie]

David

I know a fixed range isn't what you want, but I couldn't work out what range you did want.

If the source range is based on actv2 and that's a range then you might be able to set the source like this.

Set rngSrc = actv2.Resize(88, 78)

As for public variables it's quite hard to tell without knowing what you are doing, what all these modules do etc.

It might be an idea to avoid them if you can, keeping track of multiple variables over multiple modules/subs isn't that easy.

 

[davidam]

Yes, after digging in on this, it is clear that the public variables are not necessary in my case. I have created numerous variables; a few are global and most are local. I have also used variables to eliminate a bunch of instances of select/copy/paste...much more efficient.
The program worked well before but every now and then it would just get lost; I am now pretty confident that the variables will keep it on track now.
Thanks all for your help!

 

[davidam]

PS. Pretty slick just resizing the range instead of all the address stuff!

 

[Norie]

David

Are you sure you need Global variables?

 

[davidam]

I think so. I set the variable in one sub and then use that variable in a different sub in the same module.

 

[davidam]

Perhaps I just have my terminology wrong. When I say global, I mean I am using Dim at the top of the module...making it available to all of the subs within that module. Is this the correct term?