Why is my VBA UDF to count distinct not working?

Question

I have tried to create in VBA a user defined function that performs a count of unique values in a range; so I'm using the Excel-function method as its basis.

The Excel function would look something like this:

{= SUM(1/COUNTIF(range,range))}   

And so my attempt to replicate this in VBA is as follows:

Public Function f_COUNTDISTINCT(rng_range As Range)
With Application.WorksheetFunction
    f_COUNTDISTINCT = .Sum(1 / .CountIf(rng_range, rng_range))
End With
End Function

I'm having no success! I suspect that it's because I need to tell VBA to do the equivalent of an array formula, but I'm not sure how. I've tried using square brackets in various configurations to no avail.

How do I do it?

NB: I understand that there are more elegant ways to do this using a different method, for example here https://excelchamps.com/blog/count-unique-values-excel/#6, however I'd like to learn the technique by which I could make the method I'm using work. Thank you in advance.

Answer 1

As @SJR has already mentioned, you can use the Evaluate method...

Public Function f_COUNTDISTINCT(rng_range As Range)
    With rng_range
        f_COUNTDISTINCT = Evaluate("SUM(1/COUNTIF(" & .Address(External:=True) & ", " & .Address(External:=True) & "))")
    End With
End Function

Note that in this example .Address has been set to return an external reference. This will allow you to enter the formula in a sheet other than the one containing the data. However, if your range includes empty/blank cells, the function will return a #DIV/0! error. To exclude empty/blank cells, try the following instead...

Public Function f_COUNTDISTINCT(rng_range As Range)
    With rng_range
        f_COUNTDISTINCT = Evaluate("SUMPRODUCT((" & .Address(External:=True) & "<>"""")/COUNTIF(" & .Address(External:=True) & "," & .Address(External:=True) & "&""""))")
    End With
End Function