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..
upobDaPlayaAsked:
Who is Participating?
 
zorvek (Kevin Jones)ConsultantCommented:
The With..End With syntax is used to reduce the number of characters you type when writing code. That's it. The compiled code is essentially the same. It's essentially only a shortcut and it has drawbacks.

In general, use it when you want to set a set of four or more properties belonging to a single object. Why four or more? Because the With and End With add two lines to the code so you are already behind if you use it with one or two statements. With three is a wash (in terms of lines of code only.) With four statements you start to see a benefit (again, in terms of lines only.)

The drawbacks are that, when debugging, you can't easily copy and paste property names into the Watch window although the mouse over variable reveal does still work. You also can't use the With..End With syntax with two or move objects at the same time. Finally, it can make code harder to read since the reader has to translate the dot into the actual object reference in the With statement.

As imnorie stated, it also can be more easily misused as you have done by neglecting to include the dot in front of the second Range object reference. Which brings up another point concerning good practice: always, always, always use fully qualified references to avoid falling into the inferred parent object problem.

And what are you trying to do with this:

strProduce = .Range("A2:A" & intY)

It implies you are copying more than one cell value into strProduce which will generate an error.

Kevin
0
 
NorieVBA ExpertCommented:
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.
0
 
gowflowCommented:
On top of all previous comments that are correct and to be more specific to you as imnorie pointed out for the select to work you need to have something like this

Sub test()
Dim strProduce As Variant
Dim inty As Long

Set wb = ThisWorkbook
Set ws = wb.Worksheets("Produce")
inty = ws.UsedRange.Rows.Count

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


End Sub

Open in new window


if strProduce is declared as Array Variant then yes
strProduce = .Range("A2:A" & intY)
is ok

and with the .Activate you get the focus on the sheet and then
.Range("A2").select will work

check the attached file.
gowflow
EndWith.xlsm
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
aikimarkCommented:
You should reference the range's value property when assigning values to a variable.
With ws
  strProduce = .Range("A2:A" & intY).Value

Open in new window

0
 
upobDaPlayaAuthor Commented:
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 !
0
 
zorvek (Kevin Jones)ConsultantCommented:
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
0
 
gowflowCommented:
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
0
 
zorvek (Kevin Jones)ConsultantCommented:
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
0
 
gowflowCommented:
Agree and to add also some instances with Pastespecial also need activation.
gowflow
0
 
NorieVBA ExpertCommented:
gowflow

Can you give an example where PasteSpecial requires activation?
0
 
zorvek (Kevin Jones)ConsultantCommented:
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
0
 
upobDaPlayaAuthor Commented:
Thanks for the full depth review and counterpoints.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.