Logit Regression VBA

[Anthony Norton]

Hi guys,

Am trying to figure out a logit VBA but I don't understand why when testing my function out, the result is #VALUE! I have attached an excel for your reference, you can see the cells where it is tested. The code is below:

Function LOGIT(y As Range, xraw As Range, _
Optional constant As Byte, Optional stats As Byte)

If IsMissing(constant) Then constant = 1
If IsMissing(stats) Then stats = 0

'Count variables
Dim i As Long, j As Long, jj As Long, jjj As Long
Dim V() As Variant

'Read data dimensions
Dim K As Long, N As Long
ReDim V(1 To 7, 1 To K)
N = y.Rows.Count
K = xraw.Columns.Count + constant

'Adding a vector of 1's to the matrix if constant = 1
'name xraw = x from now on
Dim x() As Double
ReDim x(1 To N, 1 To K)
For i = 1 To N
x(i, 1) = 1
For j = 1 + constant To K
x(i, j) = xraw(i, j - constant)
Next j
Next i

'Initializing the coefficient vector (b) and the score (bx)
Dim b() As Double, bx() As Double, ybar As Double, iter As Long, change As Double
Dim lambda() As Double, dlnL() As Double, hesse() As Double, lnL() As Double
ReDim b(1 To K): ReDim bx(1 To N)
ReDim lambda(1 To N)
ReDim dlnL(1 To K)
ReDim hesse(1 To K, 1 To K)

ybar = Application.WorksheetFunction.Average(y)
If constant = 1 Then b(1) = Log(ybar / (1 - ybar))
For i = 1 To N
bx(i) = b(1)
Next i

iter = 1
sens = 10^ - 11
change = Application.WorksheetFunction.Ln(iter) - Application.WorksheetFunction.Ln(iter - 1)
Do While Abs(change) <= 10^ - 11
change = Application.WorksheetFunction.Ln(iter) - Application.WorksheetFunction.Ln(iter - 1)
iter = iter + 1

'Compute prediction Lambda, gradient dlnL,
'Hessian hesse, and log likelihood lnl
For i = 1 To N
lambda(i) = 1 / (1 + Exp(-bx(i)))
For j = 1 To K
dlnL(j) = dlnL(j) + (y(i) - lambda(i)) * x(i, j)
For jj = 1 To K
hesse(jj, j) = hesse(jj, j) - lambda(i) * (1 - lambda(i)) _
* x(i, jj) * x(i, j)
Next jj
Next j
lnL(iter) = lnL(iter) + y(i) * Log(1 / (1 + Exp(-bx(i)))) + (1 - y(i)) _
* Log(1 - 1 / (1 + Exp(-bx(i))))
Next i

Dim SE() As Double, t() As Double, hinv() As Double, hinvg() As Double
Dim Pval() As Double
ReDim t(1 To K)
ReDim hinv(1 To K, 1 To K)
ReDim hinvg(1 To K)
ReDim SE(b(1) To b(K))

'Compute inverse Hessian (=hinv) and multiply hinv with gradient dlnl
hinv = Application.WorksheetFunction.MInverse(hesse)
hinvg = Application.WorksheetFunction.MMult(dlnL, hinv)
If Abs(change) > sens Then Exit Do

'Apply Newton’s scheme for updating coefficients b
For j = 1 To K
b(j) = b(j) - hinvg(j)
SE(j) = Sqr(-hinv(j, j))
t(j) = b(j) / SE(j)
Pval(j) = 2 * (1 - Application.WorksheetFunction.NormSDist(Abs(t(j))))
V(1, j) = b(j)
Next j
Loop
'ln Likelihood of model with just a constant (lnL0)
Dim lnL0 As Double, PR As Double, LR As Double
lnL0 = N * (ybar * Log(ybar) + (1 - ybar) * Log(1 - ybar))
PR = 1 - (lnL(iter) / lnL0)
LR = 2 * (lnL(iter) - lnL0)
If stats = 1 Then
For j = 1 To K
V(2, j) = SE(j)
V(3, j) = t(j)
V(4, j) = Pval(j)
Next j
V(5, 1) = PR
V(5, 2) = iter
V(6, 1) = LR
V(6, 2) = Pval(1)
V(7, 1) = lnL
V(7, 2) = lnL0
End If

LOGIT = V
End Function

[MrShorty]

This forum can be strict about rules like putting code within code tags. You want to comply with these rules to avoid getting your topic locked.

What debugging have you done? Does it actually enter the function? What statement causes the error? If you have not done so already, probably the first thing I would do would be to add a Stop statement https://msdn.microsoft.com/en-us/lib.../gg264373.aspx to the beginning of the function and any other useful places to get into debug mode. From debug mode you should be able to identify the errors causing the value error. After a quick scan through, I see what looks like an attempt to take the ln(0), which is invalid, though I do not know if that is the only error.

[kasan]

Some things to correct:
1) You try to ReDim 1 to K, but what is the value of K? Ou, you calculate it later Put K= ... before ReDim

Please Login or Register  to view this content.

2) You try to get Ln(0)? Really?

Please Login or Register  to view this content.

[Anthony Norton]

Some things to correct:
1) You try to ReDim 1 to K, but what is the value of K? Ou, you calculate it later Put K= ... before ReDim

Please Login or Register  to view this content.

2) You try to get Ln(0)? Really?

Please Login or Register  to view this content.

Thanks for pointing out the blatant errors, I have since rectified them. However, the function still doesn't work. Able to point out any more errors in coding?

Please Login or Register  to view this content.

[Anthony Norton]

This forum can be strict about rules like putting code within code tags. You want to comply with these rules to avoid getting your topic locked.

What debugging have you done? Does it actually enter the function? What statement causes the error? If you have not done so already, probably the first thing I would do would be to add a Stop statement https://msdn.microsoft.com/en-us/lib.../gg264373.aspx to the beginning of the function and any other useful places to get into debug mode. From debug mode you should be able to identify the errors causing the value error. After a quick scan through, I see what looks like an attempt to take the ln(0), which is invalid, though I do not know if that is the only error.

Sorry about not putting code tags. The function actually runs through without any compilation error. Except that when used as an array in my excel it shows #VALUE!

[kasan]

It would be better if you could walk throw your function step-by-step yourself.
Open VBA editor, open code of function, then place cursor on some line at the beginning of the code and press F9. Line of code will be marked in dark red.
After this try to "refresh" your formula on a sheet (double-click on it and Ctrl+Shitf+Enter).
This will run your function and process will stopped at the line you marked earlier. Now you can Add Watches to see values of any variable and go throw your code step-by-step with F8.

[MrShorty]

The function actually runs through without any compilation error. Except that when used as an array in my excel it shows #VALUE!

I'm not entirely certain what you mean here. You are correct that there are no compiler errors, but the amended code in post #4 does not execute all the way to the end. There are run time errors to debug. Execution stops at the lnL(iter)= statement in the "Compute prediction Lambda, gradient dlnL, Hessian hesse, and log likelihood lnl" section of the code. Something is wrong with that statement that I have not looked at in detail, yet. This seems like a good opportunity to exercise some debugging muscles. Use either a Stop statement or a breakpoint as kasan suggested, get into debug mode, and step through the function. Analyze the statements and variables and such when the errors occur.