Syntax advice to get rid of select/activate

[Nobody]

Hi all,

I have just started my journey from select/activate to start writing direct
code for the events. I often get errors claiming OBJECT is missing or RANGE
METHOD FAILED etc.

Everywere in "vba bibles" and "excel hadbooks" etc. that I had to get rid of
select - activate - copy paste!! But they don't write much more about it.

Are there some syntax rules you well trained people can deliver as advice? I
have searched on the net and got some small pieces, but not a good picture.I
guess I need a good presentation about how you build syntaxes when referring
to ranges, objects etc and what actions they can take.

I find code to be similar to learn a new language with right gramatic, but I
miss something similar to my old book of english gramatics. There I can find
how to build sentences with objects, predicat, verbs, etc. In VBA help files
etc. much of the information consist of code examples instead.

Any comments? Helping links or rules to remember, when you don't want to
copy paste a lot but just point out "from were to where" instead....

/Regards

 

[Frank Kabel]

Hi
best would be you post some code which is not working. This would it
make easier to explain how to correct your code

 

[Dave Peterson]

Learn the with/end with structure. It's less work for your typing fingers and I
find if more difficult to have unqualified ranges in your code.


dim myrng as range
with worksheets("sheet1")
set myrng = .range("a1",.cells(.rows.count,"A").end(xlup))
end with

The leading dots mean that those objects refer to the previous "with" object.

Bad example follows:

This code in a general module will work if sheet1 is the activesheet.

Dim myrng As Range
With Worksheets("sheet1")
Set myrng = .Range("a1", Cells(.Rows.Count, "A").End(xlUp))
End With

But will fail if sheet1 isn't the activesheet. That unqualified "cells" refers
to the activesheet--not the sheet in the "with" statement.


And put "Option explicit" at the top of every new module. It'll force you to
Dim all your variables which makes sure you get VBE's intellisense help to show
up.

You can change a setting with in the VBE:
Tools|Options|Editor Tab
make sure "Require Variable Declaration" is checked.

 

[Gord Dibben]

NoBody

Range(Range("A1"), Range("A1").End(xlDown)).Select
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
Selection.Paste

is equal to.....

Range(Range("A1"), Range("A1").End(xlDown)).Copy _
Destination:=Sheets("Sheet2").Range("A1")

Instead of

Dim actSht As Worksheet
Set actSht = ActiveSheet
Worksheets("Sheet2").Activate
Range("A1:J10").Select
Selection.Copy
actSht.Activate
Range("K43").Select
ActiveSheet.Paste

use

Worksheets("Sheet2").Range("A1:J10").Copy _
Destination:=ActiveSheet.Range("K43")

or, if you just want to copy values:

Range("K43").Resize(10, 10).Value = _
Worksheets("Sheet2").Range("A1:J10").Value

which bypasses using the Clipboard (and is analogous to Paste
Special/Values)

Gord Dibben Excel MVP