+ Reply to Thread
Results 1 to 7 of 7

Logit Regression VBA

  1. #1
    Registered User
    Join Date
    07-26-2016
    Location
    Singapore
    MS-Off Ver
    2013
    Posts
    18

    Logit Regression VBA

    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
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP, 2007, 2024
    Posts
    16,517

    Re: Logit Regression VBA

    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.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Valued Forum Contributor kasan's Avatar
    Join Date
    07-22-2009
    Location
    Riga, Latvia
    MS-Off Ver
    Excel 2010
    Posts
    680

    Re: Logit Regression VBA

    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.

  4. #4
    Registered User
    Join Date
    07-26-2016
    Location
    Singapore
    MS-Off Ver
    2013
    Posts
    18

    Re: Logit Regression VBA

    Quote Originally Posted by kasan View Post
    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.

  5. #5
    Registered User
    Join Date
    07-26-2016
    Location
    Singapore
    MS-Off Ver
    2013
    Posts
    18

    Re: Logit Regression VBA

    Quote Originally Posted by MrShorty View Post
    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!

  6. #6
    Valued Forum Contributor kasan's Avatar
    Join Date
    07-22-2009
    Location
    Riga, Latvia
    MS-Off Ver
    Excel 2010
    Posts
    680

    Re: Logit Regression VBA

    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.

  7. #7
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP, 2007, 2024
    Posts
    16,517

    Re: Logit Regression VBA

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Regression with VBA
    By ellizar in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-20-2015, 09:57 AM
  2. how to run regression with vba ?
    By eternity_forget in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-14-2009, 12:55 PM
  3. Regression
    By bosc in forum Excel General
    Replies: 6
    Last Post: 05-22-2009, 11:01 AM
  4. Replies: 1
    Last Post: 02-12-2006, 12:15 PM
  5. Regression in VBA
    By Noah in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-27-2005, 08:05 AM
  6. Regression
    By Antoine in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-11-2005, 10:05 AM
  7. [SOLVED] Regression
    By Ubirajara Augusto in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-01-2005, 06:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1