Results 1 to 11 of 11

20010717, 19:18 #1
 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 singlecell 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.

20010717, 19:28 #2
 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

20010717, 19:35 #3
 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

20010717, 20:56 #4
 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

20010717, 21:47 #5
 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

20010717, 21:50 #6
 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

20010718, 07:43 #7
 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.

20010718, 08:19 #8
 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 addin, you can automate the writing the formula.

20010718, 09:16 #9
 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.count1 by that order?

20010718, 10:38 #10
 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)

20010718, 11:41 #11
 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