Avoiding .Select and .ActiveSheet

  • Thread starter (PeteCresswell)
  • Start date
P

(PeteCresswell)

I'm in the home stretch on rooting out/replacing all my .Selects and
..ActiveSheets with direct object references.

Two last situations that I can't figure out to handle:

1) How to grab a range that includes all cells in
a specified worksheet.

Currently using
-----------------------------------------------------
3861 With mySS.Worksheets(curSheetName)
3862 .Select
3863 .Cells.Select
3864 End With
3869 myPSI.PrintArea = mySS.Selection.Address
-----------------------------------------------------

("myPSI" is a just a user-defined struct that I pass to a
routine that speeds things up a little by using the
Excel4Macro workaround to set Page Setup.)



2) How to control what is selected when a user first clicks the
tab to look at a worksheet.

I don't want them to be confronted with some humongous
selection left over from when my code was working on it.

The upper left cell is ok.

Having nothing at all selected would be better.

Current code:
-----------------------------------------------------
5700 set myDataSheet = theSS.WorkSheets(theSheetName)
5800 With myDataSheet
5801 .Select
5802 .Cells(1, 1).Select
5809 End With
 
P

(PeteCresswell)

Per (PeteCresswell):
1) How to grab a range that includes all cells in
a specified worksheet.

Currently using
-----------------------------------------------------
3861 With mySS.Worksheets(curSheetName)
3862 .Select
3863 .Cells.Select
3864 End With
3869 myPSI.PrintArea = mySS.Selection.Address
-----------------------------------------------------
Click to expand...

Please ignore this one. Dave Peterson straightened me out in another thread.
 
J

jlepack

For number two there are two options, one, don't select anything in
your code, which you are already trying to do. The other option is
when you finish your code, go back to all the sheets that you used and
select range("a1")

Cheers,
Jason Lepack
 
P

(PeteCresswell)

Per jlepack:
two options, one, don't select anything in
your code, which you are already trying to do. The other option is
when you finish your code, go back to all the sheets that you used and
select range("a1")
Click to expand...

So... if I'm really doing it right in the first place and never select anything,
nothing will be selected? I know that sounds a little dense... but
intuitively, it seems like there always has tb something selected.

If nothing selected is a possibility, that seems like both the right approach
and a good test to see if my code is following good practice in that regard.

I'd avoid the loop/.Select range("a1") because I'd want to be able to open up my
code module, do finds on ".Select" and ".ActiveSheet" and get no hits - just to
check up on myself.
 
D

Dave Peterson

If you never select anything, then the thing that was selected when you started
will still be selected when you finish. You don't unselect something by
avoiding selecting. You just don't change selections.

And not changing selections usually means that the code runs faster, but more
importantly, it really makes the code easier to read later on.

And for the most part, I think it's true that your code shouldn't have any
..selects and .activates in them. There are a few exceptions--Freezing windows
comes to mind. You have to be on the sheet and be in your cell to do that.

You wrote .activesheet. I bet you meant .activate. But if you didn't you may
find Activesheet in your code--especially if that code can be used lots of times
(deleting rows based on values in a column, for instance).

You may want to refer to ranges/objects by using:

With Activesheet

(like with the print setup)
 
P

(PeteCresswell)

Per Dave Peterson:
And not changing selections usually means that the code runs faster, but more
importantly, it really makes the code easier to read later on.
Click to expand...

I didn't want to say anything because I'm one of those people whose car
definitely runs better after it's been washed and waxed... but it seems to me
like I got a noticeable increase in speed after purging all the .Selects.
And for the most part, I think it's true that your code shouldn't have any
.selects and .activates in them. There are a few exceptions--Freezing windows
comes to mind. You have to be on the sheet and be in your cell to do that.

You wrote .activesheet. I bet you meant .activate. But if you didn't you may
find Activesheet in your code--especially if that code can be used lots of times
(deleting rows based on values in a column, for instance).
Click to expand...

No, I meant .ActiveSheet. Haven't used .Activate yet - don't even know what it
does, although my first guess would be that it's functionally similar to
..Select...

To me, the implication of .ActiveSheet is that somehow the right worksheet has
magically been selected. So I replaced all those references with explicit
"theSS.Worksheets("whatever") refs - passing the worksheet name as a parm to the
routine in question.
You may want to refer to ranges/objects by using:
With Activesheet
Click to expand...

Seems like a violation of the spirit of avoiding .Select - in that what's being
referred to in the code isn't immediately obvious. Or am I misunderstanding
..ActiveSheet?
 
D

Dave Peterson

I have lots of code that refers to the activesheet. In fact, I have lots of
code that refers to the selection, too. But I expect that the user wants to run
the code against the activesheet--or even just against the current selection
(say changing case in a just a few cells).

I don't usually have my code select anything--but I do trust the user (usually
me!) to select what should be acted upon first. It's not really magic--it's
more trust (must more dangerous than magic!).

And I usually use Activesheet without the dot in front of it. I want the
activesheet in the activewindow.

When you write .activesheet, that dot means something important. And the
activesheet will refer to the object in the previous "with" statement.

Dim wb As Workbook
For Each wb In Application.Workbooks
MsgBox wb.ActiveSheet.Range("a1").Address(external:=True)
Next wb

and .activesheet can belong to the application, a workbook or a window.

Since you didn't share what you used in the previous With statement, I wasn't
sure if you used it the way you wanted.

And with worksheets, .activate is very similar to .select. But if you have 17
worksheets selected and activate one of those 17, then that one will become the
activesheet--but the others will continue to be selected.
 
Top