+ Reply to Thread
Results 1 to 7 of 7

VBA Linear Regression function?

  1. #1
    Registered User
    Join Date
    06-08-2012
    Location
    Copenhagen, Denmark
    MS-Off Ver
    Excel 2010
    Posts
    35

    VBA Linear Regression function?

    Hi everyone,

    So, I want to code a VBA function that can make a linear regression and estimate the Beta-coefficients on two variables.
    I know how to do the whole thing using matrix algebra (and of course the Data Analysis Toolpack), but I would love to have a VBA function for it.

    So, I have a Y-vector and then a matrix consisting of a X1 and X2 data (as attached) and I need to estimate the:
    1) Intercept
    2) Beta1
    3) Beta2

    For the linear regression function: Y = alpha + B1X + B2X


    Does anybody have any ideas?
    Attached Files Attached Files
    Last edited by Daniel86; 03-03-2015 at 12:22 AM.

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: VBA Linear Regression function?

    Check whether this helps you

    http://www.freevbcode.com/ShowCode.asp?ID=9070


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

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

    Re: VBA Linear Regression function?

    What role do you need the VBA code to play? Multiple linear regression is quite straightforward using the LINEST() worksheet function: https://support.office.com/en-us/art...rs=en-US&ad=US

    You can simply hand enter the function into the spreadsheet, and bypass any use of VBA.

    VBA can enter the LINEST() function into an array of cells (you will need to become familiar with the .formulaarray property of the desired range: https://msdn.microsoft.com/EN-US/lib.../ff837104.aspx

    The LINEST() function is also available to be used in VBA, if you want to return the regression coefficients to a VBA array: https://msdn.microsoft.com/en-us/lib...ice.11%29.aspx
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Registered User
    Join Date
    06-08-2012
    Location
    Copenhagen, Denmark
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: VBA Linear Regression function?

    Oh, yeah. Well, I would like to do the calculations as matrix calculations in VBA.
    However, I have no idea on where to start. For example, it would be awesome to make a function that takes input as ranges -> i.e. Regression(Yrange, range2, range3) and then converts them to vectors/matrices and does calculation from there.

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

    Re: VBA Linear Regression function?

    Help us understand your requirements. Are you suggesting that you do not want or cannot use the built in LINEST() function for these regressions? Are you allowed to use other Excel functions (such as the built in matrix functions such as MINVERSE())? Ore are you required to write your own regression/matrix inversion algorithm from the bottom up? What you describe is essentially the LINEST() function, so it seems important to me to understand why you do not want or cannot use it.

  6. #6
    Registered User
    Join Date
    06-08-2012
    Location
    Copenhagen, Denmark
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: VBA Linear Regression function?

    Ooh, nevermind :-)
    I solved it.

    This is the code:

    Please Login or Register  to view this content.

  7. #7
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: VBA Linear Regression function?

    Or ...

    Please Login or Register  to view this content.
    Entia non sunt multiplicanda sine necessitate

+ 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. Excel 2007 : Non linear regression
    By tiz in forum Excel General
    Replies: 3
    Last Post: 10-19-2011, 09:51 AM
  2. 3-D Linear Regression
    By komonoway in forum Excel General
    Replies: 1
    Last Post: 03-06-2010, 06:37 PM
  3. Linear regression
    By nns91 in forum Excel General
    Replies: 21
    Last Post: 03-11-2009, 08:38 PM
  4. [SOLVED] Custom function for max residual from linear regression
    By Bob Nelson in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-28-2006, 10:20 AM
  5. Linear Regression using the TREND function
    By scarlett1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-26-2006, 10:15 AM

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