Clarification need, not just by me

[JMay]

Select is a great method. In VBA it is over-used as experts continually say
"you don't need to use the select statement in accomplishing your task".
Also, using Select in VBA can cause an error when one tries a statement (with
sheet1 active) like Worksheets("Sheet2").Select. This error is obvious when
you stop and think about it as even while in the spreadsheet interface say
sheet1 -- one cannot select cell A1 on sheet2, without first activating
sheet2, then cell A1.

Worksheets("Sheet2").Activate is the same, well nearly…
In VBA - with sheet1 active I sometimes activate sheet2 before issuing a
statement against a range on sheet2. There are times when the Worksheets
("Sheet2").activate is unnecessary. I just don't know the rules underlying
it.
So even after years of excel programming these "don't" are not still obvious
to me.

If these such no-no's or AVOID DOING THESE THINGS were documented it would
be a "good-read" for all excel newcomers/want-a-be's. The Excel boioks I
have (around 12 or so) don't cover this sufficiently.

Can some of you begin assisting me in creating this list of clarification

thanks in advance...

Jim

 

[Joel]

I looks like you were refering to one of my postings. the are a number of
issues with the SELECT method

1) When debugging large programs with lots of loop trying to debug the code
it is often hard to figure out what item is actually selected

2) Adding worksheets and workbooks (also opening workbooks) automatically
switches to the new sheet/book. So relying on the select method you have to
be aware of the behavior of VBA which isn't well documented. The is why I
set objects to each sheet/workbook in my code

Set bk = workbooks.open(filename:=abc.xls")
Set sht = bk.sheets("Sheet1")
with sht

end with

3) Selecting a sheet slows down the code because excel has to refresh the
worksheet before continuing. Select cells also slows down the code becaue
excel has to update the screen. And even if you turn off refreshing excel
need to keep track of the active cell so when the program ends it will put
the active cell where the macro put the selection. Not the last place you
were before the macro ran

4) if you want the selected cell not to change when you run a macro then
don't use select. It is a nuisance is ever tim e you run a macro you have to
go back to the area of the worksheet where you were working. If you had a
worksheet change macro and you changed cell A3 do you want the macro to leave
you at cell IV2000.

 

[JMay]

"Selecting a sheet slows down the code because excel has to refresh the
worksheet before continuing." << Is this the same for Activating a sheet?

 

[Joel]

Yes

"Selecting a sheet slows down the code because excel has to refresh the
worksheet before continuing." << Is this the same for Activating a sheet?