Solved

When do you use a With Structure

Posted on 2014-03-09
12
163 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 33

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 150 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 29

Assisted Solution

by:gowflow
gowflow earned 150 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
 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 50 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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 29

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 29

Expert Comment

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

Assisted Solution

by:Norie
Norie earned 150 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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

707 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now