Clarification needed

[jim may]

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

 

[Joe4]

Hey Jim,

I am moving this to the Excel Discussion Forum. It is probably the more appropriate forum, and since it doesn't get as nearly as much traffic, it should stay on the first page a lot longer.

In my early programming days, I can across this link which has lots of good information and tips:
http://www.ozgrid.com/VBA/SpeedingUpVBACode.htm

 

[SydneyGeek]

With respect to Select or Activate with worksheets;

1. I always activate a worksheet, never select. One reason for this is that you can activate a hidden sheet but you can't select it. So, I avoid run-time errors.
2. If you need to select a cell or range on another sheet, activate the sheet first -- but often you can operate on that sheet without selecting or activating it.
3. If you give a worksheet an object variable name (eg, Set shDest=Worksheets("Sheet2")) then you can use a With construct to manipulate the sheet.

Denis

 

[jim may]

Denis Thanks;

I'm especially interested in the area of "operate on that sheet without selecting or activating it" - I'd like to be able to think/say -- In code you can issue code statements changing ranges "across" worksheets (as long as you fully-qualify the references), except in the case of the following methods and properties:

1. Select
2. ???
3. ???
4. ???


and,
Must one assign an object variable name (to a worksheet) in order to use the With construct, as indicated in your statement?

 

[SydneyGeek]

Something like...

Sub CopyRows()
    Dim c As Range
    Dim RwLast As Long
    Dim shDest As Worksheet
    Set shDest = Sheets("Summary")
    
    For Each c In Selection
        If c.Value = "A" Then
            RwLast = shDest.Cells(Rows.Count, 1).End(xlUp).Row + 1
            c.EntireRow.Copy _
                Destination:=shDest.Cells(RwLast, 1)
        End If
    Next c
End Sub

You don't have to assign an object variable to a sheet name but I like to. It makes the code cleaner, and if you repurpose the code for another sheet all you need to do is re-set the reference

For the activation thing, something like...
I tend to activate the sheet when I'm using Paste Special to paste values.

Worksheets("Some hidden sheet").Activate
Range("A6").PasteSpecial Paste:=xlPasteValues

Denis

 

[RoryA]

A couple of important points off the top of my head:
1. You can't select or activate a range unless its sheet is active.
2. If you are selecting a range (for whatever reason), it is better to use Select rather than activate, unless you are just trying to change the activecell within a multi-cell selection.
3. If you have code in a normal module, an unqualified reference to Range or Cells will refer to the active worksheet; if your code is in a worksheet module, an unqualified reference to Range or Cells will refer to the worksheet containing the code. That last one often throws people when they use Range("A1").Select after activating a sheet and find it fails.
4. Any thing to do with the Window object (FreezePanes, gridlines etc) does require you to activate the sheet first.

And to address your last question, no you can use a With statement without a variable:

With Sheets("Sheet1")
   .Range("A1").Value = "This is"
   .Range("A2").Value = "a test"
End With

 

[jim may]

Denis, you say:
I tend to activate the sheet when I'm using Paste Special to paste values.

This is not REQUIRED, is it? Isn't the activate unnecessary if the Paste destination cell references is fully qualified? Thanks again.....

 

[jim may]

Thanks Rory...

 

[Domski]

Hi guys,

I'm not a very experienced VBA programmer but having gone from trying to manipulate macro recorder code to understanding a fair bit more about the way VBA works with a workbook/worksheet I come to realise that one of main things about writing VBA is to not try to emulate the way that you actually interact with Excel.

One thing I used to do when working with multiple files would be to open each file and then perform a lot of actions on the active workbook, often switching between activating one workbook and another to perform tasks.

Whereas now I will use:

Dim readBook As Workbook

Set readBook = Workbook.Open .....

And find it a much better method of working.

Getting away from using Select etc is a learning curve that most people have to go through and when I get a spare moment I like to revisit old projects and re-write any code that I wrote that used those techniques and to date I don't think I've come across any that I couldn't eliminate it completely (although the Window object thing has cropped up).

It's also interesting to learn how you often don't need to use methods like autofill to populate a range of data and as pointed out copy...paste special...values can be avoided (which only recently clicked).

Anyway, I'm probably repeating a lot of what's already been said but thought I would give a relative novice's point of view.

Laters,

Dom