0

Apparently, parentheses in VBA function arguments turn ByRefs to ByVals,

And Range Copy, whose only available variable is a Destination Range, needs that destination to be a ByRef?

So, how is this working? Shouldn't the Cell referenced with Worksheet Specified, being a ByRef in parentheses, be converted into a ByVal and cause the Copy to fail?

Range("A1").Copy (Worksheets("Sheet1").Range("E5"))
Malady
  • 251
  • 1
  • 12
  • Who says it needs that destination passed `ByRef`? – Rory Nov 22 '19 at 14:52
  • @Rory - That's what I was getting from this answer to my previous question. - https://stackoverflow.com/questions/58864512/failing-to-copy-one-cell-to-another-cell-using-destination-copy/58872378#58872378 – Malady Nov 22 '19 at 14:54
  • I'm not sure that's correct. It requires a `Range` object, that much is true. Check the results of: `typename((sheets(1).range("A1")))` versus `typename((range("A1")))` and you should see why one works and the other doesn't. – Rory Nov 22 '19 at 14:57
  • @Rory - Range vs. String... So my initial question's Accepted Answer is full of true things, but got the explanation of my issue wrong? – Malady Nov 22 '19 at 15:03
  • Going to mark this as duplicate: two candidates that the OP would be interested in reading: https://stackoverflow.com/q/56692769/9101981 and https://stackoverflow.com/q/45747604/9101981. Pity I can't add two duplicates in the one flag, because both posts have comprehensive answers (by Mathieu) – AJD Nov 22 '19 at 21:41
  • Does this answer your question? [VBA: Usage of parentheses for a method](https://stackoverflow.com/questions/56692769/vba-usage-of-parentheses-for-a-method) – AJD Nov 22 '19 at 21:43
  • @AJD - I dunno... All those questions have so much specific-ness I can't tell if they answer my question or not... – Malady Nov 22 '19 at 23:55

2 Answers2

4

Apparently, parentheses in VBA function arguments turn ByRefs to ByVals.

Well, no, not exactly.

The parentheses make the parameter into an expression that gets evaluated and then its value is passed on.

You will see this when you call a Sub that has two parameters:

mySub 1, 2       ' this is OK
mySub (1, 2)     ' this makes VB complain

The second example makes from (1, 2) an expression; however, the expression is invalid so VB will complain.

If you call a function without using the return value, then do not use parentheses:

myFunction 1, 2        ' this is OK
myFunction (1, 2)      ' this is an error
i = myFunction (1, 2)  ' here the parentheses are required.
Paul Ogilvie
  • 25,048
  • 4
  • 23
  • 41
4

A couple of things:

  1. Copy doesn't care how you pass the destination Range, as long as it's a Range object. So, for example:

    Range("A1").Copy ByVal Range("B1")

will work just fine.

  1. The parentheses try to evaluate what's inside them as an expression. So in your first case, what you actually end up passing is the value of the range, not the range itself. The weird part is actually that the evaluation returns the Range object properly when you add the Worksheets() call. This has something to do with late binding as best I can tell because that Worksheets call returns an Object type, not a Worksheet. If you were to use a worksheet code name (e.g. Sheet1.Range("A1")) instead, you'd have the same problem as you had originally. Really need someone like Mathieu to explain the inner workings of that.
Rory
  • 32,730
  • 5
  • 32
  • 35