0

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

Ron
  • 660
  • 1
  • 5
  • 10
  • 1
    `Range(..) as Range` is not a thing. VBA IDE has very simple intellisense. It "suggested `Range`" because you typed in `As `. You can do that anywhere. Start with a new line and type `As `. – GSerg Sep 26 '21 at 17:15

1 Answers1

1

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)
norie
  • 9,609
  • 2
  • 11
  • 18
  • Thank you. Removing the () fixed it. Not sure why neither worked for you, as (box) for sure works on my machine. – Ron Sep 26 '21 at 17:18
  • 2
    @Ron It only works if the variable is not declared. Put `Option Explicit` on top at all times. – GSerg Sep 26 '21 at 17:22