Hi! I am trying to write a UDF. The function works fine if I just write it as
a worksheet function. For several reasons I need it as a UDF though. Excel
tells me "Sub or function not defined" where Ln is. How am I supposed to
write this? It looks fine to me..Ay help appreciated!

Public Function Kupiec(p As Double, T As Double, N As Double) As Double
Dim a As Double
Dim b As Double

a = -2 * Ln((1 - p) ^ (T - N) * p ^ N)
b = 2 * Ln(1 - (N / T)) ^ (T - N) * (N / T) ^ N

Kupiec = a + b

End Function

One of those "differences" between the spreadsheet world and the VBA world... in the VBA world, the natural log function is Log, not Ln. Just make that change and your code should work fine.

Rick


"Arne Hegefors" wrote in message ...
Hi! I am trying to write a UDF. The function works fine if I just write it as
a worksheet function. For several reasons I need it as a UDF though. Excel
tells me "Sub or function not defined" where Ln is. How am I supposed to
write this? It looks fine to me..Ay help appreciated!

Public Function Kupiec(p As Double, T As Double, N As Double) As Double
Dim a As Double
Dim b As Double

a = -2 * Ln((1 - p) ^ (T - N) * p ^ N)
b = 2 * Ln(1 - (N / T)) ^ (T - N) * (N / T) ^ N

Kupiec = a + b

End Function

I'm glad Rick replied before me!

Besides his observation, the reason Ln failed is the function does not have
a direct VB/A equivalent, so you would need to use it as an Excel function
like this

a = Application.WorksheetFunction.Ln(number)

Although the above should work, go with Rick's suggestion and use VBA's Log
function..Just bear it in mind if you need to use other Excel functions in
VBA (most though not all work like that)

Regards,
Peter T


"Rick Rothstein (MVP - VB)" wrote in
message ...
One of those "differences" between the spreadsheet world and the VBA
world... in the VBA world, the natural log function is Log, not Ln. Just
make that change and your code should work fine.

Rick


"Arne Hegefors" wrote in message
...
Hi! I am trying to write a UDF. The function works fine if I just write it
as
a worksheet function. For several reasons I need it as a UDF though. Excel
tells me "Sub or function not defined" where Ln is. How am I supposed to
write this? It looks fine to me..Ay help appreciated!

Public Function Kupiec(p As Double, T As Double, N As Double) As Double
Dim a As Double
Dim b As Double

a = -2 * Ln((1 - p) ^ (T - N) * p ^ N)
b = 2 * Ln(1 - (N / T)) ^ (T - N) * (N / T) ^ N

Kupiec = a + b

End Function