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

 

[MLang]

So, is this correct?

If my procedures are stored on one of my worksheets, then whenever I refer to "Range(xxx)" without any qualifiers (such as "ActiveSheet", or "Sheets(sht1)", etc), the range will be relative to the sheet that the procedue is saved in. Correct?

I had been under the impression that merely activating a given Sheet would cause all subsequent references to a Range to be interpretted relatative to the activated sheet.


ML

 

[RoryA]

Correct. The behaviour you mention at the end is true for code in a normal module, but worksheet modules behave differently (because it's a class that has a Range property).