I would like to run regression analysis in VBA. I have a set of data that I want to fit into a LogNormal distribution. I called the parameters of the Lognormal distribution "sigma" and "mju".

To accomplish this, I am trying to create a function in VBA, called "regreslognormalsigma". This function requires the following ranges as input:

- probabilitetet --> this range contains the cumulative density of the argument;

- abshisat --> this range contains the arguments, in my case these are displacements of a structure in a seismic event.

I can not figure out why the output is always the initial value that I set for "sigma", i.e. the output is always 0.0001.

Can anyone help me out? I would like the function to give me the value of sigma for which the data fit best to a lognormal distribution with a standard deviation sigma. In the meantime, I think the algorithm below can be used to find the mean value also (with little modification, which I plan to do once I solve the issue).

The code is below:

Function regreslognormalsigma(probabilitetet As Range, abshisat As Range) As Double

Dim nrqeliza As Double 'this I use to count the range

Dim Yi As Double

Dim xi As Double

Dim Li As Double

Dim i As Double

Dim j As Double

Dim sigma, mju As Double

Dim sigmafinal, mjufinal As Double

Dim shgk, shgkmin, shgkvar As Double ' these are respectively: the sum of errors squared, the minimum sum of errors squared, and an auxiliary value

Dim result As Double

nrqeliza = probabilitetet.Count

sigma = 0.0001 'initial value

mju = 0.0001 'initial value

shgkmin = 1E+300 'I set a large value for the minimum

shgk = 0 'initial value

result = 0

'Li = WorksheetFunction.LogNorm_Dist(xi, sigma, mju, True) --> this is how I take values of the Log Normal distribution

While sigma < 10

While mju < 10

For i = 2 To nrqeliza

Li = WorksheetFunction.LogNorm_Dist(abshisat(i), sigma, mju, True)

shgk = (((probabilitetet(i) - Li)) ^ 2) + shgk

Next i

If shgk < shgkmin Then

shgkvar = shgk

result = sigma

shgkmin = shgkvar

Else: shgkmin = shgkmin

End If

mju = mju + 0.0001

Wend

sigma = sigma + 0.0001

Wend

regreslognormalsigma = result

End Function