Syntax question using :=

[Andrew]

Hello,
In using the range method "copy" I have run into some issues with
syntax. Below is some code. I have three statements for copying the
range to a specified destination. Two methods work. But when I use
parentheses around the argument, I get an error. I've encountered
this before. Why would I get an error when putting the argument in
parentheses?

Sub Copy_Ranges()

Dim source_range As Range
Dim dest_location As Range

Set source_range = Worksheets("Sheet1").Range("A1:B2")
Set dest_location = Worksheets("Sheet1").Range("A10")

source_range.Copy Destination dest_location ' this works fine
source_range.Copy Destination:=dest_location ' this works fine
source_range.Copy(dest_location) ' this one
results in an error

End Sub

 

[Andrew]

Hello,
In using the range method "copy" I have run into some issues with
syntax.  Below is some code.  I have three statements for copying the
range to a specified destination.  Two methods work.  But when I use
parentheses around the argument, I get an error.  I've encountered
this before.  Why would I get an error when putting the argument in
parentheses?

Sub Copy_Ranges()

Dim source_range As Range
Dim dest_location As Range

Set source_range = Worksheets("Sheet1").Range("A1:B2")
Set dest_location = Worksheets("Sheet1").Range("A10")

source_range.Copy Destination dest_location        ' this works fine
source_range.Copy Destination:=dest_location      ' this works fine
source_range.Copy(dest_location)                        ' this one
results in an error

End Sub

I've incorrectly typed my command statements. Here is the real code.

source_range.Copy dest_location ' this works
fine
source_range.Copy Destination:=dest_location ' this works fine
source_range.Copy(dest_location) ' this one
results in an error

 

[Andrew]

I've incorrectly typed my command statements.  Here is the real code.

source_range.Copy dest_location                         ' this works
fine
source_range.Copy Destination:=dest_location      ' this works fine
source_range.Copy(dest_location)                        ' this one
results in an error

I'd like to add that if I declare the argument "dest_location" as a
variant, not a range, then I am able to use the statement:
source_range.Copy(dest_location). If "dest_location" is declared as a
range, the method fails.

 

[GS]

Andrew wrote :

I've incorrectly typed my command statements. Here is the real code.

source_range.Copy dest_location ' this works
fine
source_range.Copy Destination:=dest_location ' this works fine
source_range.Copy(dest_location) ' this one
results in an error

Parenthesis are used when a return is expected. Think of them as a
'return envelope' used when passing args to a function. The Copy method
does not return anything and so no 'return envelope' is used. In all
cases where returns are expected, the parenthesis are used on the right
side of the equal operator.

 

[GS]

Andrew used his keyboard to write :

I'd like to add that if I declare the argument "dest_location" as a
variant, not a range, then I am able to use the statement:
source_range.Copy(dest_location). If "dest_location" is declared as a
range, the method fails.

Declaring the arg as Variant causes VBA to evaluate what's wrapped in
the parenthesis. Normally, this is poor programming practice since the
extra processing makes your code less efficient.

 

[Andrew]

Andrew wrote :












Parenthesis are used when a return is expected. Think of them as a
'return envelope' used when passing args to a function. The Copy method
does not return anything and so no 'return envelope' is used. In all
cases where returns are expected, the parenthesis are used on the right
side of the equal operator.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

I'll have to think about that. That may be over my head. Thanks for
the help.

Andy

 

[Mike S]

I'll have to think about that. That may be over my head. Thanks for
the help.

Andy

I think they discussed the same issue here:

http://www.excelforum.com/excel-programming/331841-range-copy-destination.html