Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Array argument with .WorksheetFunction (Excel 2000, SR1)

    Hi Everybody,

    I have a formula in a cell, which contains an array as one of the arguments. It is:

    <pre>=INDEX(LINEST(KnownY,KnownX^{1,2,3,4},TRUE,TR UE),1,5)</pre>


    (It calculates the constant term of a fourth order polynomial fit, without having to bother with array entry - thanks to Hans Pottel for another tip I found edtremely useful!).

    I would like to perform the same calculaton in VBA (see attempt at bottom). However, even though this is a single-cell formula that does not require array entry, one of the arguments is an array, and VBA chokes on the curly braces (it won't even get past the auto syntax check). Is there a way to perform this calculation in VBA? Thanks in advance!

    <pre>With Application.WorksheetFunction
    .Index(.LinEst(KnownY, KnownX^{1,2,3,4},TRUE,TRUE),1,5)
    End With</pre>


    ...where KnownY and KnownX are both ranges of equivalent dimensions, fully populated with data (no empties), properly declared and Set to the same ranges as the original worksheet function example.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Array argument with .WorksheetFunction (Excel 2000, SR1)

    Jim

    I think the Array function might be what you are looking for,

    Array(1,2,3,4) ,

    or Dim vArray as Variant
    vArray = (1,2,3,4)

    Andrew C

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array argument with .WorksheetFunction (Excel 2000, SR1)

    Try using:

    <pre> Array(1,2,3,4)
    </pre>


    instead of the curly brackets.
    Legare Coleman

  4. #4
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array argument with .WorksheetFunction (Excel 2000, SR1)

    Gents,

    Thank you for your quick replies. I did try both suggestions. In both cases, I get a type mismatch error in the line with the LinEst function it it. Other things I tried:
    1) putting Array(1,2,3,4) directly after the ^
    2) Declaring all as variants (KnownX, KnownY, Powers)
    <pre> ' Declare
    Dim KnownX(8) As Double, KnownY(8) As Double, N As Integer, Powers As Variant

    ' Establish powers
    Powers = Array(1, 2, 3, 4)

    ' Fill arrays KnownX and KnownY
    For N = 0 To 7
    KnownX(N) = Range("A2:A9").Cells(N + 1).Value
    KnownY(N) = Range("B2:B7").Cells(N + 1).Value
    Next N

    ' Try to calculate
    With Application.WorksheetFunction
    MsgBox .Index(.LinEst(KnownY, KnownX ^ Powers, True, True), 1, 5)
    End With
    </pre>


    The code shown here bombs in the MsgBox line, with type mismatch. Any other ideas? Thanks!

    JIM

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array argument with .WorksheetFunction (Excel 2000, SR1)

    I see several possible problems here:

    1- I don't think that you can do array arithmetic in VBA. Therefore, I don't think the KnownX ^ Powers will work. I think you would need to do that calculation in the loop where you get the KnownX values from the sheet.

    2- I don't think the Index worksheet function will work in VBA, even using Application.Worksheet. So, I think you would have to assign the results of the Linest function to a VBA variable.

    3- Linest returns an array of values. I don't know how to get those assigned to an array. It must be possible since the Linest function is available, I just don't know how to do it. You could probably assign the result to a variant, and then reference the elements from there, but again I don't know how to do that.
    Legare Coleman

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Array argument with .WorksheetFunction (Excel 2000, SR1)

    Jim,

    I wonder if .Index is confusing excel, as it is a property of a number of excel objects like worksheets, e.g. Sheets("Sheet1").Index might return 1 in the normal course of events.

    If you try enclosing that particular function in square brackets, e.g [Index(.LinEst(KnownY, KnownX ^ Powers, True, True), 1, 5)], it may work. However as I have absolutely no idea of you are doing I cannot test it.

    You can use something like MsgBox [sum(10,20)], and it will return the correct answer (30), so try experimenting with that approach.

    Andrew C

  7. #7
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array argument with .WorksheetFunction (Excel 2000, SR1)

    JIM,

    Legare is right in all the points he mentioned. I don't think there is a way to use braces {} in a VBA statement. I thought that it might be possible to use multidimensional arrays as an argument for the LINEST function in VBA, but I can't get that to work. The alternative is what I propose hereunder. Therefore, you have to calculate the X^2, X^3 and X^4 in your spreadsheet and put them next to the X. As such, the range A29 contains 4 columns with all the powers of X. In that case, the following code surely works:

    <pre>Sub test()
    Dim Arrs As Variant
    Dim RX As Range
    Dim RY As Range
    Set RX = Range("A29")
    Set RY = Range("E2:E9")
    With Application.WorksheetFunction
    Arrs = .LinEst(RY, RX, True, True)
    MsgBox "Intercept = " & Arrs(1, 5)
    End With
    End Sub
    </pre>


    As Legare already mentioned, the LINEST returned output can be assigned to a Variant type variable.

  8. #8
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array argument with .WorksheetFunction (Excel 2000, SR1)

    How about this array formula. It will allow you insert the basevalues and the interpolated values in the approximation series.

    <pre>Function csePolynomialCoeff(rngYval As range, rngXval As range) As Variant
    'Array formula to return the coefficients for a polynomial regression in a column range
    'Attention: Using Base 0 arrays because of Linest

    Dim nXpts As Integer, nYpts As Integer, nOrder As Integer, i As Integer, j As Integer
    Dim allY As Variant, allX As Variant, allXj As Variant, allXeq As Variant

    'Determine 'scan range'
    nXpts = rngXval.Cells.Count
    'Determine number of base datapoints
    nYpts = WorksheetFunction.Count(rngYval)
    'Determine order
    nOrder = Application.Caller.Rows.Count - 1
    'Provide storage space
    ReDim allY(nYpts - 1)
    ReDim allX(nYpts - 1)
    ReDim allXj(nYpts - 1)
    ReDim allXeq(nOrder - 1)
    j = 0
    For i = 1 To nXpts
    If IsEmpty(rngYval(i)) = False Then
    allX(j) = rngXval(i).Value
    allY(j) = rngYval(i).Value
    'MsgBox rngYval(i).Value
    j = j + 1
    End If
    Next i
    'Fill the 0 based subarrays for each exponent
    For j = 1 To nOrder
    For i = 0 To nYpts - 1
    allXj(i) = allX(i) ^ j
    Next i
    'Assign to the 0 based 'array of arrays'
    allXeq(j - 1) = allXj
    Next j
    'Now just do it
    With WorksheetFunction
    csePolynomialCoeff = .Transpose(.LinEst(allY, allXeq))
    End With
    End Function
    </pre>


    If you do not want to make your workbooks dependant of your add-in, you can automate the writing the formula.

  9. #9
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array argument with .WorksheetFunction (Excel 2000, SR1)

    This is really nice, cri, I am definitely going to use that.
    Maybe, one suggestion: would it not be interesting to have the 'order' of the polynomial as an argument for the function and replace the application.caller.rows.count-1 by that order?

  10. #10
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array argument with .WorksheetFunction (Excel 2000, SR1)

    Hans Pottel, changing is easily done, but I made it this way because:

    a) I have a macro which takes care of the insertion of the formula and, optionally, of the calculation of the approximation and its deviation.

    [img]/forums/images/smilies/cool.gif[/img] If you use it directly, this way it is ensured that you have _all_ the coefficients, there is no way (known to me) how to make a array formula expend its boundary _itself_ (i.e. w/o the help of an independant Sub)

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array argument with .WorksheetFunction (Excel 2000, SR1)

    Thanks to Hans' message, I realized that Linest is returning a two dimensional array. A little more testing got this to work for your code:

    <pre>' Declare
    Dim KnownX(8) As Double, KnownY(8) As Double, N As Integer, Powers As Variant
    Dim Results As Variant
    ' Establish powers
    Powers = Array(1, 2, 3, 4)
    ' Fill arrays KnownX and KnownY
    For N = 0 To 7
    KnownX(N) = Range("A2:A9").Cells(N + 1).Value ^ Powers(N Mod 4)
    KnownY(N) = Range("B2:B7").Cells(N + 1).Value
    Next N
    ' Try to calculate
    With Application.WorksheetFunction
    Results = .LinEst(KnownY, KnownX, True, True)
    MsgBox Results(5, 1)
    End With
    </pre>

    Legare Coleman

Posting Permissions

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