Parentheses around an expression are causing the expression to be evaluated as a value and passed ByVal to the procedure invoked.
testFunc (ThisWorkbook.Sheets("Sheet2").Range("A1:A16")) '<--- this works fine
No, it doesn't work fine, not with aCol As Range in the signature of testFunc - for the exact same reason as the line underneath it. A Range object has a default member that points to its .Value. When you do this:
DoSomething (SomeRange)
What's really happening is this:
DoSomething (SomeRange.Value)
The parentheses are telling VBA to evaluate the expression as a value, before passing the result by value (ByVal) of that expression to the procedure being invoked, regardless of the implicit ByRef modifier in the procedure's signature.
Problem is, given a 16-cell Range, Range.Value is a 2D array - and given a single-cell Range, Range.Value is a Variant that contains that cell's value, and that can't possibly be coerced into a Range object instance, so it's impossible that the code you have here "works fine": it's an object required error, because the procedure is expecting a Range and you're giving it a Variant that contains a 2D array.
Why your "works fine" code isn't really working
The only way testFunc (ThisWorkbook.Sheets("Sheet2").Range("A1:A16")) could possibly work, is if testFunc had this signature (which, I suspect it did at one point before you ultimately asked this question):
Function testFunc(aCol)
Or the explicit equivalent:
Public Function testFunc(ByRef aCol As Variant) As Variant
And because a 2D array can be iterated with a For Each loop (albeit very inefficiently), and it's possible that the entire A1:A16 range contains values that can be coerced into valid Integer values, "works fine" indeed... except you're updating an in-memory copy of the data, not the cells themselves - so it wasn't quite "works fine", was more like "doesn't crash".
Here's a MCVE:
Private Function DoSomething(ByRef foo As Range)
End Function
Public Sub Test()
DoSomething (Range("A1").Value)
End Sub
Calling Test throws run-time error 424 "Object Required".
Public Function DoSomething(ByRef foo As Range)
End Function
Public Sub Test()
DoSomething (Range("A1:A100").Value)
End Sub
Calling Test throws the same run-time error 424 "Object Required".
Now remove the As Range declaration:
Public Function DoSomething(ByRef foo As Variant)
Debug.Print TypeName(foo)
End Function
Public Sub Test()
DoSomething (Range("A1:A100").Value)
End Sub
Invoking Test prints Variant() to the immediate pane (Ctrl+G). Now make the range contain just one single cell:
Public Function DoSomething(ByRef foo As Variant)
Debug.Print TypeName(foo)
End Function
Public Sub Test()
DoSomething (Range("A1").Value)
End Sub
Invoking Test prints whatever data type the value of cell A1 holds. If it's text, it prints String. If it's a number, it prints Double. If it's a date, it prints Date. If it's a cell error (e.g. #NA, #VALUE!, etc.), it prints Error.
Note that the results are identical for all of the above, whether you specify .Value explicitly or not, because omitting .Value boils down to implicitly specifying it.
Also note that passing an array ByVal is illegal. What's being passed to the function here is a copy of a pointer to the beginning of the array - the Variant array isn't storing the array itself, just a pointer to its actual location: that's why & how you can legally pass a variant array by value.
Why the variable is blowing up too
The 2nd line is throwing the same error, for the same reason:
testFunc (aRange) '<---- error
You're implicitly doing this:
testFunc aRange.Value
Because that's what the parentheses do: they tell VBA to evaluate the expression as a value, and pass it as such.
So you're passing a 2D variant array to a procedure that's expecting a Range.
This is different, and correct:
testFunc aRange
Note the lack of parentheses: now you're passing the Range object reference to a procedure that accepts a Range object reference, ByRef (although that modifier is implicit and a more semantically correct way to do it would be to pass the object reference ByVal).
So how do I tell when the arguments are being evaluated as a value?
When you invoke a Function procedure, you're normally interested in its return value:
result = MsgBox("Confirm?", vbYesNo)
The VBA automatically positions the opening parenthesis immediately after the function's identifier - no whitespace.
When you invoke a Function procedure without capturing its return value, you must omit the parentheses:
MsgBox "Confirm?", vbYesNo
Note that this would be a compile error:
MsgBox ("Confirm?", vbYesNo)
Because the expression ("Confirm?", vbYesNo) cannot be evaluated as a value and passed as such to the first parameter of the procedure.
That's easy though - MsgBox has multiple parameters. People get confused when there's only one parameter - because then this looks legal:
testFunc (aRange)
Notice the whitespace: it shouldn't be there. That's the VBE telling you "these parentheses are NOT part of the function call, they're part of the argument you're going pass to that function, and if whatever is in these parentheses can't be evaluated as a value at run-time, expect trouble".
If your testFunc procedure isn't returning a value, then it shouldn't be a Function in the first place - it should be a Sub procedure. Functions take input and return a value, Subs do something, which usually entails side-effects, e.g. actual cell values being modified.
