How does ByRef to ByVal by parentheses work, when

Question

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"))

Answer 1

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.

2. 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.

Answer 2

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.