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?

>Solution :

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 circumenvents this behavior.

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