Natural log VB function

[Zain]

Hi there. I'm having a problem using the natural log function in VB. The function is Ln.

I get the error "Sub or function not defined".

I'm using the Option Explicit ...

Here is my code (I the problem is on the line with the asterisks).
<code>
Sub MLEParameers()

'Uses data from 'Analysis' sheet to calculate severity distribution parameters for the MLE method

Dim k, n As Integer
Dim mu, sigmasq, sumlogs, sumsq As Single

n = 2150 'Total number of losses
sumlogs = 0 'sum of log losses
sumsq = 0 'sum of squared log losses

For k = 2 To n + 1
sumlogs = sumlogs + Ln(Sheets("Analysis").Cells(k, 7).Value) '<--***the problem is on this line
sumsq = sumsq + (Ln(Sheets("Analysis").Cells(k, 7).Value)) ^ 2
Next k

mu = sumlogs / n
sigmasq = sumsq - 2 * mu * sumlogs + mu ^ 2

Sheets("MLE").Range("B11").Value = mu.Value
Sheets("MLE").Range("B12").Value = sigmasq.Value

End Sub
</code>

Can you please help?

Thanks

 

[MrKowz]

LN isn't a part of VBA, but you can still call it using the Application.WorksheetFunction property:

Sub MLEParameers()
'Uses data from 'Analysis' sheet to calculate severity distribution parameters for the MLE method
Dim k, n As Integer
Dim mu, sigmasq, sumlogs, sumsq As Single
n = 2150 'Total number of losses
sumlogs = 0 'sum of log losses
sumsq = 0 'sum of squared log losses
Application.WorksheetFunction
For k = 2 To n + 1
sumlogs = sumlogs + Application.WorksheetFunction.Ln(Sheets("Analysis").Cells(k, 7).Value)
sumsq = sumsq + (Application.WorksheetFunction.Ln(Sheets("Analysis").Cells(k, 7).Value)) ^ 2
Next k
mu = sumlogs / n
sigmasq = sumsq - 2 * mu * sumlogs + mu ^ 2
Sheets("MLE").Range("B11").Value = mu.Value
Sheets("MLE").Range("B12").Value = sigmasq.Value
End Sub

 

[JackDanIce]

I think it's because the code doesn't recognise what "ln" is. Try replacing every instance of ln with:

Application.WorksheetFunction.ln

Use a search and replace feature to do this.

 

[mikerickson]

The VBA function Log returns the natural logarithm of a number.

LN is a worksheet function for natural log.
LOG is the worksheet function for base 10 log.
Log is the VBA function for natural log.

 

[Zain]

Thanks very much guys!