WorksheetFunction in VBA

My workbook has a sheet called "MyList", another called "HisList" , a named range called "myNamedRange" which is "MyList!A:A".

How do I get this functionality of this excel formula

Please Login or Register  to view this content.

in this vba code

Please Login or Register  to view this content.

The following code works with a named range

Please Login or Register  to view this content.

My question is how can I use "MyList!A:A" directly in the worksheet function.

Thanks

You have a few options:

Please Login or Register  to view this content.

(note you can use Application.CountIf also (ie dispose of WorksheetFunction))

or you can use Evaluate

Please Login or Register  to view this content.