CODEWITHSUNDEEP
excelvbacopyrangeruntime-error
Malady
Malady

Reputation: 263

Failing to copy one cell to another cell using Destination Copy

Range("A1").Copy (Range("E5"))

That fails, but if I explicitly define the destination range as a Destination, like the following, it works.

Range("A1").Copy Destination:=Range("E5")

If I give a Worksheet reference for my destination, like the following, it also works:

Range("A1").Copy (Worksheets("Sheet1).Range("E5"))

Why is the simplest form, not working, and giving me

Runtime Error 1004: "Copy method of Range class failed"

instead?

Upvotes: 1

Views: 100

Answers (2)

patel
patel

Reputation: 440

you have to eliminate brackets

Range("A1").Copy Range("E5")

Upvotes: 0

Pᴇʜ
Pᴇʜ

Reputation: 57683

The difference between 

Range("A1").Copy (Range("E5"))

and 

Range("A1").Copy Range("E5")

is that the first one converts the ByRef argument into ByVal while the second does not. The parenthesis here stands for a conversion and not for what you expected.

Note that you must only use the parenthesis when you use a function (that returns a value) but not when using a procedure.

Example

a = MyFunction(Parameter1, Parameter2)   'correct with parenthesis
MyProcedure Parameter1, Parameter2       'correct w/o parenthesis

a = MyFunction Parameter1, Parameter2    'it will error
MyProcedure(Parameter1, Parameter2)      'it will error

now with only one parameter

a = MyFunction(Parameter1)   'correct with parenthesis
MyProcedure Parameter1       'correct w/o parenthesis

a = MyFunction Parameter1    'it will error
MyProcedure (Parameter1)     'it will NOT error but convert ByRef into ByVal !!!

The last line of the above example does something completly different than you expected. You can notice the extra space between the procedure name and the parenthesis. And it is the same like the following syntax

a = MyFunction((Parameter1), (Parameter2))   'convert ByRef into ByVal
MyProcedure (Parameter1), (Parameter2)       'convert ByRef into ByVal

So we can note down:

  • If a function returns a value that we want to write into a variable:
    You must use parenthesis: a = MyFunction(Parameter1)
  • If a procedure (or function) does not return a value then:
    You must not use parenthesis: MyProcedure Parameter1

Upvotes: 2

Related Questions