+ Reply to Thread
Results 1 to 3 of 3

VBA + LINEST Function

  1. #1
    Registered User
    Join Date
    10-04-2013
    Location
    Porto,Portugal
    MS-Off Ver
    Excel 2003
    Posts
    8

    VBA + LINEST Function

    Hi guys,

    I developed a code to extract the two parameters of my linear trendline and work it! But i cant solve for a polynomial level 2 ... I Know that function in this is case is LINEST(yknows,xknows^{1,2},,) but i cant reproduce this in my VBA code.

    Can help me ?

    Please see the attached.
    Regards
    Attached Images Attached Images

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,851

    Re: VBA + LINEST Function

    Assuming I understand what you are trying to do, the reason you are having trouble is because VBA cannot understand Excel's array constant syntax (the ^{1,2} part of the desired LINEST() formula). I see two basic approaches to a solution to this kind of problem:

    1) Since VBA cannot handle that syntax, shift the regression so it is performed in the Excel rather than VBA. In the context of your code, this might be accomplished by assigning the LINEST() formula to a string variable, then assigning that variable to the formulaarray property of the output range. This would be the same as entering the formula in the spreadsheet, so that the regression is performed in the spreadsheet.

    2) With a fuller understanding of what the range^{1,2} syntax does in Excel, find some way to recreate the effect in VBA. In Excel, range^{1,2} creates a two dimensional matrix where the first column contains range^1 and the second column contains range^2.
    2a) One way would be to use spreadsheet formulas and create this 2D matrix in the spreadsheet. The formulas should be simple (=J3,=J3^2) copied down. The use this range for the src1 object that is being used by the LINEST() function.
    2b) Another possibility, use a loop in VBA to create a 2D array that will contain these values. Then assign this array to src1 (src1 will need to be dimensioned as a variant or will need to be dimensioned as a 2D array).

    Which approach you choose will depend on other constraints and considerations.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    10-04-2013
    Location
    Porto,Portugal
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: VBA + LINEST Function

    Mr Shorty sorry for my late reply.

    Your explanation was very important to I understand the problem.

    Now my VBA code works

+ 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. [SOLVED] Linest Function - Unable to get LinEst property of the WorksheetFunction class
    By fbs13 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 07-25-2013, 09:55 AM
  2. [SOLVED] The LINEST function
    By BNCOXUK in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-05-2013, 11:38 AM
  3. LINEST function
    By a_shyam41 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-22-2011, 08:01 AM
  4. [SOLVED] linest function
    By Mitch in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-05-2005, 06:50 PM
  5. LINEST Function
    By sydodman in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 02-01-2005, 03:38 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