Relative vs absolute Addressing - Sheet Names

[MLang]

I am often confused by the behavior of my Excel marocs when it comes to the degree to which I need to specify sheet names.

It seems that at times I can Select or Activate a sheet, and then everything I want to do happens on that sheet without error. At other times, it seems that I am getting errors that I can make "go away" by re-specifying a sheet name. Sometimes one line works without explicilty specifying the sheet, but the next line fails. (I am sure that it has something to do with what I am doing in the line of code, but if I knew what that was, I wouldn't have the question. I have no immediate example... I am about to write a new procedure and I jsut want to use good programming conventions to get started.)

My question is, can someone give me a short primer on the basic convention for referencing sheets in macros? Is it good convention to repeatedly call out Sheets(mysht), or something else?

OPTION 1: Start everything with:
Sheets(shtname).Range(...

OPTION 2: Activate a sheet, and then refer to Active Sheet.
Sheets(shtname).Activate
Activesheet.Range(...

OPTION 3 (which seems to get me into trouble...) Select a sheet, and then hope that Excel stays focused on that sheet.
Sheets(shtname).Select
Range(...

Thanks!
Mike

 

[VoG]

Option 1 is best. Can you give a concrete example.

 

[MLang]

OK, I will use that method. As for a concrete example, here is a variant...

This works:
Sheets(SHEET_Temp_Space).Select
ActiveSheet.Range("C" & PasteRow).Select

This fails:
Sheets(SHEET_Temp_Space).Range("c", PasteRow).Select

This also fails:
Sheets(SHEET_Temp_Space).Select
Range("c", PasteRow).Select

 

[shg]

You can only select something on the active sheet, but there is almost never a need to select something to operate on it or with it.

 

[MLang]

You can only select something on the active sheet, but there is almost never a need to select something to operate on it or with it.

Eureka! I have read so much regarding activate and select, but either you stated it clearer than most, or I finally really listened...

"You can only select something on the active sheet."

Thank you!
Mike

 

[shg]

Well ... you're welcome, but the broader point is to stop selecting stuff at all.

 

[MLang]

Yes, I understand that also. (Good point.)

But now I know why, too!

 

[shg]

But now I know why, too!

That fact that you can't select something other than on the active sheet is not the reason you shouldn't; it's that it is much slower, and the screen flashes dizzyingly, then you have to add more code to stop that, then ...

Where it's helpful to select worksheets and cells is when you're debugging, so you see where the code is operating. I do that in separate lines of code, and then delete them when things are stable.

 

[RoryA]

This also fails:
Sheets(SHEET_Temp_Space).Select
Range("c", PasteRow).Select

This should only fail if the code is in the code module of a different sheet, in which case an unqualified reference to a Range refers to the sheet containing the code; in a normal module, an unqualified reference to a Range refers to the active sheet.

 

[shg]

Range("c" [COLOR=red]&[/COLOR] PasteRow).Select

or

Cells(PasteRow, "c").Select