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?
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
