asked on

When do you use a With Structure

I have:

Set wb = ThisWorkbook
Set ws = wb.Worksheets("Produce")

With ws
strProduce = .Range("A2:A" & intY)
Range ("A2").Select
........
.........
End With

Why will .Range ("A2").Select not work I guess I little messed up on the property structure for With and Range...What is best practice for using the With..

Norie

It won't work because there is no dot qualifier, . , in front of Range.

It also won't work unless the 'Produce' worksheet is the active sheet.

ASKER CERTIFIED SOLUTION

zorvek (Kevin Jones)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Jacques Geday

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

aikimark

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

upobDaPlaya

ASKER

thanks all for the comments. I took a look at goflows spreadsheet and I see that he activates the worksheet, thus my final question is what is the difference between setting the worksheet and activating the worksheet ? thx !

zorvek (Kevin Jones)

Activating any Excel objects should only be done when you want the user to experience the end result of that activation. Usually you do not want this to happen. You want to do all your work without affecting the user's experience.

Also, activating the worksheet or workbook and then assuming it is active is not dependable. As I stated above, always fully qualifying everything is good programming practice. I'll add that you should never activate anything unless absolutely necessary.

Kevin

Jacques Geday

Well I added .Activate only because you needed to select Range("A2").

I agree with Kevin in the sense that you should not interfere with the user's experience however sometimes you need to select items or do these kind of things reason why I tend to always use

Application.ScreenUpdating = False
in the beginning of a routine
do manipulations ...

Application.ScreenUpdating = True

So that it prevent updating and disturbance to the user.
gowflow

zorvek (Kevin Jones)

gowflow,

That will affect the user's experience. Once you enable screen updating the user will see whatever your code last activated. I don't consider this good practice. All you are preventing the user from seeing is the flashing of UI components as various components are activated/deactivated. Unless you go to the trouble of restoring the UI environment EXACTLY the way it was when the user invoked your code, your are still changing the UI and thus affecting the user's experience.

If at all possible, do not use the Activate method unless you absolutely have to. One of the few examples is when you have to reference one of the ActiveWindow properties such as when freezing panes.

Kevin

Jacques Geday

Agree and to add also some instances with Pastespecial also need activation.
gowflow

SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

zorvek (Kevin Jones)

I've never needed to use the Activate method when using PasteSpecial.

As far as I know, the only time an Activate method is required is when accessing properties in the ActiveWindow object, and recovering the selection/active cell on an inactive worksheet.

There may be others but I can't think of them now.

Kevin

upobDaPlaya

ASKER

Thanks for the full depth review and counterpoints.