Range works in formula but not in custom function-CodePudding

I have a range in a formula and it works:

=MAX(LN(A2:A7))

Now I want to rebuild this formula as a custom function and it does not work.

Function testy(xr As Range)
    n = WorksheetFunction.Max(WorksheetFunction.Ln(xr))
    testy = n   
End Function

It throws an error: wrong datatype. But I defined "As Range"? Why does it not work, how can I correct it?

CodePudding user response：

Use Application.Ln.

The issue is that WorksheetFunction.Ln accepts a Double as its argument. You're implicitly passing it the .Value of a multi-cell range, which is a Variant array.

Using the late-bound Application.Ln, one can pass an array (and get an array back):

Function testy(ByVal xr As Range) As Double
    testy = WorksheetFunction.Max(Application.Ln(xr))
End Function