How to Spread Bad Habits
If found this MSKB article via Automate Excel. It shows 22 ways to select a range. Yikes! You may know that I have an opinion on Selecting and Activating. There are so many wonderful things that you can do with a reference to a range. Microsoft should have used one of those.
Here’s one example:
Range("Database").Select
Selection.Offset(4, 3).Resize(Selection.Rows.Count + 2, _
Selection.Columns.Count + 1).Select
Selection.Offset(4, 3).Resize(Selection.Rows.Count + 2, _
Selection.Columns.Count + 1).Select
Why is it okay to use the Resize method after the Offset method, but they have use the Selection object instead of acting directly on the original range. How about:
With Range("Database")
.Offset(4,3).Resize(.Rows.Count + 2, .Columns.Count + 1).DoSomething
End With
.Offset(4,3).Resize(.Rows.Count + 2, .Columns.Count + 1).DoSomething
End With
There’s plenty of good information on that site, but it’s in the midst of such bad coding practices that it’s hard to appreciate it.
End of Rant.
I wish that I had seen this post a few months ago, when I started tinkering around in VBA with Excel. I spent days lost in confusion between what was active, what was selected, and what objects were.
You’d think that MSKB would be a good resource for beginning programmers, but in the midst of such bad coding practices, it’s hard to learn anything.
I think this all goes back to the function of the Macro recorder in Excel.
It always records the Select and Activate events. Many users starting from the VBA basics use macro recording to figure out how VBA works. They are being misled by all of the recorded .Select actions. There is, of course, very rarely any reason to use Select when refering to a Range object!
That MSKB does this is just plain sloppy. Microsoft . . .