Why is this UDF getting a #VALUE error?

[JenniferMurphy]

I am trying to write a simple UDF to access a range of cells in the calling sheet. Here's the code:

Public Function Test(pWts As Range) As Long

MsgBox "Hi"

Dim NumWts
NumWts = Range(pWts).rows.Count   'This statrement gets the #VALUE error

Test = 0

End Function

I've used that exact statement in other code and it works. What am I doing wrong here?

It is called from a sheet that passesd it a range of 4 cells. I'm including a screenshot. I'm not using xl2bb because the UDF is in an add-in and I didn't know what xl2bb would do with that. I can frepost using xl2bb if that will work.

 

[RoryA]

It should just be:

pWts.rows.Count

since pWts is a Range object, not the name/address of a range.

 

[JenniferMurphy]

It should just be:

pWts.rows.Count

since pWts is a Range object, not the name/address of a range.

Aha! (Where is that banging head on wall emoji?)

I checked the other code and, of course, in that I am passing the3 name of a range, not the range itself.

Thank you.

Y'now, I think if the AAs at m$ft tried a little harder, they could make the error messages even more obtuse and less helpful. Hey, I have an idea, why not replace all of the error codes with just one "#ERROR"? Or maybe, "#WKBWWT"?