excel/vba syntax error on passing "Range(...) As Range) to Sub? [duplicate]-CodePudding

I'm using excel to breadboard an app I'm designing. I have the following Sub declared in vba:

Sub MakeThickOutsideBorder(ByVal theBox As Range)
...draw the border...
End Sub

When I call it like so:

Set box = Range(ActiveCell, Cells(lastRow, lastCol))
MakeThickOutsideBorder (box)

all is well.

If I call it like so:

MakeThickOutsideBorder (Range(ActiveCell, Cells(lastRow, lastCol)))

at runtime, I get "object required" on the call.

If I try to edit the call:

MakeThickOutsideBorder (Range(ActiveCell, Cells(lastRow, lastCol)) As Range)

(Code completion specifically suggests this syntax) I get a compile error.

Obviously, I have a work around, so the problem isn't holding up this particular exercise, but I'd like to understand what's going on. I've surfed the Microsoft documentation, but haven't achieved any deep understanding.

Links to documentation I haven't stumbled across much appreciated.

Office 365 on macOS

Thanks

CodePudding user response：

I actually get the error with both sets of code and the reason is the use of () around the argument being passed.

That causes whatever is in the brackets to be evaluated, in this case the ranges will be evaluated to an array of values.

So the 'simplest' solution is to not use the ().

Alternatively you can use Call.

Call MakeThickOutsideBorder(box)