Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

When do you use a With Structure

Posted on 2014-03-09
12
Medium Priority
?
175 Views
Last Modified: 2014-03-16
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..
0
Comment
Question by:upobDaPlaya
  • 4
  • 3
  • 2
  • +2
12 Comments
 
LVL 35

Expert Comment

by:Norie
ID: 39916531
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
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 600 total points
ID: 39916568
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
 
LVL 31

Assisted Solution

by:gowflow
gowflow earned 600 total points
ID: 39917204
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 46

Assisted Solution

by:aikimark
aikimark earned 200 total points
ID: 39917232
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
 

Author Comment

by:upobDaPlaya
ID: 39925538
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
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39925541
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
 
LVL 31

Expert Comment

by:gowflow
ID: 39925555
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
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39925561
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
 
LVL 31

Expert Comment

by:gowflow
ID: 39925676
Agree and to add also some instances with Pastespecial also need activation.
gowflow
0
 
LVL 35

Assisted Solution

by:Norie
Norie earned 600 total points
ID: 39926954
gowflow

Can you give an example where PasteSpecial requires activation?
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39927164
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
 

Author Closing Comment

by:upobDaPlaya
ID: 39933415
Thanks for the full depth review and counterpoints.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

879 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question