1
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?

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Malady
  • 251
  • 1
  • 12

2 Answers2

2

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
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • Thanks! So, why does my last line of code, with the Worksheet noted, work, even though it's in brackets? – Malady Nov 15 '19 at 09:58
  • @Malady Actually I cannot explain why it worked (maybe accidentally). But the syntax is wrong too (it should be without the parenthesis too). Anyway it is a good practice to **always** specify a worksheet for every `Range` and `Cells` object. It makes your code much more reliable and stops Excel guessing which sheet you meant to use. – Pᴇʜ Nov 15 '19 at 10:28
  • Yeah, I guess I will, then ask about the inconsistency in another question... – Malady Nov 21 '19 at 11:06
  • Made the question, if you wanna look: https://stackoverflow.com/questions/58996540/how-does-byref-to-byval-by-parentheses-work-when-the-byref-is-a-range-with-a-wo – Malady Nov 22 '19 at 14:42
0

you have to eliminate brackets

Range("A1").Copy Range("E5")
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
patel
  • 430
  • 1
  • 4
  • 9
  • Expanding why that works would help, but also make it a dupe of the above... So, maybe delete? I dunno. – Malady Nov 15 '19 at 09:54