Doing stuff to cells without selecting them

[PeterOC]

I've read a number of post stating that in vba selecting cells/ranges before performing actions on them is bad programming practice. But what's the alternative. I'm trying to do what i think is a very simple macro - here's a snippet:

Sheets("Performance2").Select
Range("B6:E10").Select
Selection.ClearContents

How can I delete the contents of the cells without selecting them?

Thanks
P

 

[chergh]

Sheets("Performance2").Range("B6:E10").ClearContents

 

[PeterOC]

Thanks for that. I'll give it a go.

P

 

[MGumbrell]

Out of interest.

Why is it considered bad practice to select cells before doing anything with them in VBA. Is it a pure selection issue as Chergh's response does not state select or does it?

From experience I know the answer is going to be very obvious but if I don't ask, I'll never know.

Thanks, Matt

 

[namliam]

It is a performance issue, in particular if you update the screen (which can be turned off) selecting is considerably slower.

Also if you change the actual selection in code that may be deselecting something the user is doing. Ending up totaly somewhere else than the user used to be.

It all depends on your needs. but ... yes it is better, does it matter in a lot of cases?? No...

 

[chergh]

There are several reasons:

1. It makes the code harder to read and bloats the code, see the example by the OP three lines of code which can be done with one line.

2. It makes the code run slower. Selecting and activating takes time excel also has to figure out what you are selecting, is it a range, graph something else.

3. It adds increased error opportunity. If you activate sheet1 and then try and select a range on sheet2 then it will fail, you can only select a range if it is on the active sheet.

4. Select and activate are unreliable, you'll soon enough come across problems typically when you start calling functions etc.

 

[namliam]

1. But can be more easy to follow for novice developers (or amatures doing things at home)

2. Undeniable but... if you turn off "ScreenUpdating" that takes away about 90% of the delay... A lot of the "select"-time is taken (appearently) on updating the screen.

3. No it wont, because your doing Range not Sheet.Range Still it is prown to errors due to beeing on the wrong sheet.

4. I dont know... I did a lot of stuff with selecting actual things, some users want to see things going on instead of having their computer/excel just locked for 2 minutes. Busy doing something they cannot see. Now it takes 3 minutes but atleast they know it is busy. I guess (see 1) you have to be a bit more care in your code... but other than that I have never seen a problem that was not explaned by some logical error.

 

[chergh]

1. Thats just going to teach new developers and amateurs bad habits. The real reason it is easier for them to understand is because it will look familiar due to them being exposed to the macro recorder. In reality selects make the code more complex and you have to keep track of what is being selected and what the selection object is. Avoiding selects is actually simpler just unfamiliar at first.

I would have thought using selects and activates would make life harder for someone coming from another VBA or VB environment.

2. Well you should always turn of screenupdating if your code is going to take any time at all to run and even slightly quicker is better.

3. I've seen people come up with the following sort of thing and wonder why it doesn't work:

Worksheets("sheet1").Activate
Worksheets("Sheet2").Range("A2:A4").Select
activesheet.range("A5") = worksheetfunction.sum(selection)

4. Users want a lot of things but putting in select statements etc. just so they can see their computers doing something ??? I suppose it's different when your doing something as a contractor as opposed to working for the company but I have no difficulty telling my colleagues where to go when they ask for silly things like that. If they absolutley MUST know their computer is doing something then I might put a progress metre in for them.

There usually is a logical reason for the error but if you have selects and activates in your main subs when you call another sub or function that also uses select and activate then you can have a lot of problems, all easily avoided by not using select and activate.

 

[namliam]

1.
Not everyone has VB(A) experience I have seen hardcore -non VB(A) developers- write code in Excel... this very way... Yes probably learned from the recorder... It works and is not the signature of a true Vet at office automation....
This is the difference between experienced personel and Novice/amateurs who have "dropped up the tree" and think they are all that.

2.
I agree,

3.
Hey faulty programming will fail anytime.
i.e. open a group by query in access and try and change its contents thru code.
or. If 1 = 2 then dosomething
Then ask why isnt something beeing executed??
As our most famous football player Johan Cruijf would say...
Or something your average Vulcan would be quoted as saying... That is only logical.

4.
Contracting yes, sadly users/customer sometimes require you differ from best practices... They like to see the screen do their work.
Or as one customer 'demanded' from me that I would store JPGs in the Access Database, which we all know get recompiled to BMP. Not something I would normaly do but it was a requirement and could not get the customer to change his mind even after giving him proof of the problematic results.
2 months later all his pictures made the database 800 mb and he came back asking why 'my' database was broken. I 'fixed' the problem by taking out the pictures with the version I had allready got ready waiting for him to -finaly- come to his sences....
Customers internal or external have their strange requirements and will even go against everything that is considered a rule of nature to us developers.

Beeing employed gives you a chance to earn the respect and understanding of employers. Once you have their trust/respect/understanding etc yes you can tell them what to do with their "great" ideas. Untill such time... Either you do it, or they find someone else who will... untill it breaks offcourse.

I dont advocate the use of select and activate, but ... i have had customers require it, because then they could have someone internal maintain it who was their 'expert'. What am I to do? If such is a requirement.... then that is the requirement...

"stupid" simple but well structured (usually a secondary problem of using selects etc) code will function for years on end without fail.

 

[PeterOC]

I've being dabbling in vba programming for years now and I've always used selects and activates and it's been fine. I liked the idea of stepping through code and seeing things happening on the screen. But as I've got a little bit more confident with my programming I find I don't need to do this anymore. If I can do something with 1 line of code instead of 3 then that's cool. Likewise I want my code to be easier to read, less error prone and more 'elegant' if that's the right word. If it's something simple that I've based on the macro recorder then I'll not take the selects out but otherwise...

Thanks for your replies.

P

 

[Brianwarnock]

I think that I am correct in saying that all of the examples in help use Select, therefore self taught amateurs or the lone "expert" in a small shop will obviously take that path. It is experienced people like Chergh and Namlian that can help us amateurs write better code.

Brian

 

[chergh]

I'm actually self taught as well, but I learnt access VBA stuff first and then moved to excel. Once I finally 'got' the whole object, method, properties, collections etc. thing vba becomes very easy.

 

[Brianwarnock]

But some people only write small amounts of code occasionally and therefore do not develop, but adopt an " if it works that's fine " approach , for which they cannot be blamed given the help from the help files. It is obvious from the quality of your responses that that is not the case for you.
I didn't want to start an argument but just point to a reason why many bit part players will code the way they do.

Brian

 

[namliam]

I'm actually self taught as well,

Who isnt?

given the "help" from the help files.

Quoting by me...
I agree the MS help and/or samples dont help in instilling good practice things for self thought people... I myself had to break some real bad habits... But once you do... there is no going back.

 

[wiklendt]

I'm actually self taught as well, but I learnt access VBA stuff first and then moved to excel. Once I finally 'got' the whole object, method, properties, collections etc. thing vba becomes very easy.

forget that! how do i get VBA to even just fire in excel ??????

(http://www.access-programmers.co.uk/forums/showthread.php?t=175123)

 

[HaHoBe]

Hi, wiklendt,

use the proper event in the right place with correct parameter?

Ciao,
Holger